Plan cache bloat (pollution) i SQL Server – hvorfor DMV-analyse feiler og hva du kan gjøre?

Plan cache bloat

SQL Server allokerer dynamisk minne til plan cache basert på tilgjengelige ressurser. Når dette minnet fylles opp, må eksisterende planer kastes ut for å gi plass til nye. Dermed kan nyttige planer bli raskt kastet ut og gjenbruk av planer reduseres.

Typiske årsaker:

• Ad hoc queries uten parameterisering
• Dynamisk generert SQL som gir mange varianter av samme spørring og lav plan reuse

Resultatet er at plan cache fylles opp av mange små og lite gjenbrukte planer.

Hvorfor er dette et problem?

Plan cache bloat fører til:

• Hyppig eviction av planer. SQL Server må stadig rydde i cache → nyttige planer forsvinner.
• Spørringer må kompileres på nytt i stedet for å gjenbruke eksisterende planer → CPU

Problemet med DMV-statistikk

Ytelsesanalyse i SQL Server baserer seg ofte på DMV-er som:

• sys.dm_exec_query_stats
• sys.dm_exec_sql_text
• sys.dm_exec_query_plan

Ved plan cache bloat blir denne statistikken mindre pålitelig.

Hva skjer i praksis?

• Planer blir kastet ut før de rekker å akkumulere statistikk
• “Top queries”-lister blir upålitelige
• Høyt belastende spørringer kan forsvinne fra analysen

Resultatet er at de reelle problemene ikke nødvendigvis kommer frem.

Typiske symptomer

• Alt er tregt, ingenting skiller seg ut
• Varierende ytelse uten klar årsak
• Høy CPU eller minnebruk uten tydelige toppspørringer

En av flere angrepsvinkler

En av flere angrepsvinkler ved plan cache bloat er å fokusere på query memory grants og tidsrommet grantene holdes. Vi kaller dette memory load.

Memory Load = query memory grant * execution time.

Denne typen belastning fanges ikke nødvendigvis godt opp gjennom DMV-statistikk alene, siden execution plans kan bli kastet ut før de rekker å akkumulere tilstrekkelig data.

En mulighet ved plan cache bloat er å sample requests over tid.

Praktisk tilnærming

1. Sample requests over tid
2. Identifisere spørringer med høy total memory load
3. Analysere execution plans
4. Se etter:
   1. feil cardinality estimates
   2. manglende indekser
   3. unødvendige sort/hash-operasjoner

Vi har pakket dette inn i  sp_LynxTopQueriesGrantedMemoryLoad

Når bør du bruke denne tilnærmingen?

Ved hyppige evictions av execution planer

Oppsummert

Plan cache bloat gjør tradisjonell analyse vanskelig fordi statistikk forsvinner før den rekker å bli nyttig.

• Plan cache bloat → dårlig observability
• DMV-er → mindre pålitelige
• Sampling + query grants → praktisk angrepsvinkel