Monthly Archives: June 2018

Eén slecht query plan uit de cache van SQL Server verwijderen

Posted on by

De evolutie van een database administrator. Er komt een melding binnen: “Eén van de query’s is erg langzaam”.

  • “We herstarten de machine”
  • “We herstarten de SQL Server instance”
  • “We herindexeren alle tabellen”
  • “Laten we DBCC FREEPROCCACHE gebruiken”
  • “Ja, laten we DBCC FREEPROCCACHE met beleid gebruiken”

Wacht! De functie DBCC FREEPROCCACHE met beleid gebruiken? Hoe dan?

Elke query die in de cache is opgeslagen, krijgt een unieke ‘identifier’. Die identifier wordt met de volgende query gevonden:

SELECT
    cacheobjtype,
    text,
    plan_handle
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text NOT LIKE '%sys.dm_%'

De kolom text geeft de query terug en de kolom plan_handle geeft de unieke identifier terug. Met die identifier kan vervolgens alleen dat query plan uit de cache worden verwijderd met het commando:

DBCC FREEPROCCACHE (plan_handle)

Dus vanaf vandaag worden alleen nog maar slechte query plannen via de functie DBCC FREEPROCCACHE verwijderd.

Performance: Werkelijke data in plaats van cached plans

Posted on by

Samenvatting:
SQL Query’s worden als tekst in de cache van de server opgeslagen om later eventueel te worden hergebruikt. De kleinste afwijking in een query, levert een nieuw record van de query in de cache op; dus ook bij een andere formatting, het gebruik van hoofdletters of het gebruik van comments. Query plannen zijn erg belangrijk en het opslaan van query plannen komt de performance ten goede. Echter, query plannen dubbel in de cache opslaan omdat de tekst nét wat anders eruit ziet, gaat ten koste van het geheugen, waardoor er minder ruimte voor werkelijke data overblijft, die regelmatig wordt benaderd.

Uitwerking en voorbeeld
Om query’s zo efficiënt mogelijk te draaien, slaat SQL Server query plannen op. Deze worden opgeslagen in de tabellen sys.dm_exec_cached_plans (de plannen) en sys.dm_exe_sql_text (de tekst van de SQL queries). Het opslaan van deze query plannen gebeurt in het geheugen. In principe is hier niets mis mee. SQL Server gebruikt deze query plannen opnieuw wanneer dezelfde query wordt gedraaid en hoeft niet opnieuw een query plan op te zetten, wat performance kost.

Wat regelmatig vergeten wordt, is dat query plannen als text worden opgeslagen. Hierdoor is het mogelijk dat een query plan van dezelfde query twee keer in de cache voorkomt. Voorbeeld van een query, uitgevoerd op de StackOverflow database:

/* Test query 1 */
SELECT
*
FROM dbo.Users
WHERE Reputation = 2499
GO

/* Test query 2 */
SELECT
*
FROM dbo.Users
WHERE Reputation = 2499
GO

Wordt vervolgens de cache uitgelezen met de volgende query:

SELECT
	cacheobjtype,
	text,
	plan_handle
FROM sys.dm_exec_cached_plans P
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%Test query%' AND text NOT LIKE '%sys.dm_%'

Dan is het resultaat twee records. Wordt er een ‘Test query 3’ toegevoegd, dan zitten er daarna drie records in de cache. Onbedoeld kan het geheugen hiermee onnodig worden belast met query plannen van dezelfde query. Dit terwijl het geheugen juist zo vol mogelijk moet worden gezet met daadwerkelijke data uit de database die regelmatig wordt benaderd.

Conclusie: voorzichtig, dus! SQL Server houdt zelfs rekening met hoofdletters. Is er één teken in de query tekst anders (hoofdletter, punt, komma, enter, wat dan ook!), dan verschijnen er al twee query plannen in de cache.