Tijdens één van mijn leertrajecten op SQL Server onderzocht ik het gedrag van query’s op READ ONLY secondaries binnen Always-On clusters. Wat opviel was dat query’s op deze database langzamer draaiden; dus, dezelfde query draait op de primary sneller. Nu gebruik ik in het dagelijks werkleven meer secondary logshipomgevingen, waardoor mijn werk niet direct wordt geraakt, maar als SQL Server Enthousiast werd mijn interesse behoorlijk geprikkeld.
Bij mijn zoektocht op internet, kwam ik gelukkig weer bij Microsoft uit. Dit artikel verklaarde precies de exacte oorzaak van mijn testsituatie.
Hoe zie ik dat het fout gaat?
- De query voert een index scan uit op een groot gedeelte van een tabel, die een clustered row-store index hebben.
- De query maakt gebruik van de NOLOCK query hintDe dynamic management view sys.dm_db_index_physical_stats laat een significante fragmentatie van de index zien
- Loskoppelen van de secondary uit het Always-On Cluster lost de performance kwestie op
Hoe zit dit nu? Wat is de oorzaak?
Op secondary databases binnen een always-on cluster wordt snapshot isolation bij query’s afgewdongen. De optie NOLOCK wordt daarbij genegeerd. Dit zorgt ervoor dat de index scan op volgorde van de index key door SQL Server wordt uitgevoerd Als de clustered index significant gefragmenteerd is, valt SQL Server terug op het lezen van één pagina per IO request. Op de primary database (waar snapshot isolation niet wordt afgedwongen) zal SQL Server nog steeds terugvallen op het lezen van meerdere pagina’s per IO request.
De oplossing?
Om dit probleem voor te blijven, is het verstandig de clustered index van de betrokken tabellen opnieuw op te bouwen. De nieuw opgebouwde indexen zullen dan via de Always-On naar de secondaries worden overgezet, waardoor de query’s net zo snel als op de primary database worden.
De Featured Image komt van deze pagina.
