Performance Tuning

Uge 16: Cardinality Estimation fra SQL Server 2014

I sidste uge skrev jeg om udfordringerne med Cardinality Estimation før SQL 2014 versionen og fremefter. I dag tager jeg hul på hvad der skete fra version 2014 og fremad – også i Azure.

Den nye Cardinality Estimator

En af de gode tiltag i SQL server 2014 og fremefter har været den nye metode og underliggende engine for Cardinality Estimation. I sidste uge skrev jeg om udfordringerne i den tidligere version og nogen af begrænsningerne, som kan give forkerte estimeringer. Dette leder igen til dårlig performance – baseret på en Cardinality Estimation som blev udviklet tilbage i SQL 7.0.

Der har været små bugfixes hen over årene fra version 7.0, men de har ikke været slået til som udgangspunkt. Disse skulle slås til manuelt ved at anvende Trace Flags i SQL serveren. Dette gjorde Microsoft for at tilsikre at der ikke blev introduceret såkaldte Plan-Quality Regressions. Så fra SQL server 2014 og frem kom den første store ændring på dette område siden SQL 7.0.

Målet med den nye Cardinality Estimator er at forbedre kvaliteten af execution planerne. Men, den er ikke helt 100%, og kan stadig give plan regressions. Derfor er det vigtigt at undersøge om den nye Cardinality Estimator giver mening for det specifikke workload der er på SQL serveren. Til det formål er der introduceret nye traceflags fra SQL server 2014, så man selv kan styre hvordan Query Optimizeren virker.

SQL Server 2014 databaser har Database Compatibility Level 120 og alle fremadrettede versioner har højere tal. For at få adgang til de nye traceflags skal Database Compatibility Level være 120 eller højere. Ligger databasen i Azure, er det som default (pr. d.d. 150). Forsøger man med en restore fra tidligere versioner skal man derfor også huske at rette Compatibility Level manuelt.

Man kan se Compatibility Level på flere måder – een er at se på sin Execution Plan og se på Properties i SQL Server Management Studio. Elementet CadinalityEstimationModelVersion skal være 120 eller højere for at have adgang til de nye features.

Cardinality Estimation

De to nye traceflags som blev introduceret med SQL server 2014 er:

Med traceflag 2312 kan man angive om man ønsker at anvende den nye Cardinality Estimator fra version 2014 – hvis for eks ens database er i en lavere compatibility level). Hvis man derimod vil have den gamle mode, kan traceflag 9481 anvendes.

Disse traceflags kan sættes på instance niveau, session niveau og query niveau.

  • Instance niveau sættes på databasen under Properties
  • Session niveau sættes som en DBCC kald i den query session man er i
  • Query niveau sættes som nedenstående eksempel
SELECT * FROM Person.Person
OPTION (QUERYTRACEON 2312)

Den nye Cardinality Estimator giver en række ændringer som kan give bedre performance gennem bedre estimeringer og dermed bedre execution planer. Microsoft har gennemarbejdet Cardinality Estimator på følgende områder:

  • Estimations for Multi-column predicates
  • Ascending key problem
  • Estimations for JOINS predicates
  • Extended Events troubleshooting

Nn dybere gennemgang af disse ændringer kan læses i dette whitepaper fra Microsoft af Joseph Sack.

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator | Microsoft Docs

Opsummering – Cardinality Estimation fra SQL Server 2014

Endnu en måned er gået og der er taget nogen dybe spadestik i statistics på SQL serveren og som du har læst over de sidste 4 uger, så er deres nøjagtighed og gentagende opdatering yderst vigtig for performance på SQL serveren.

I næste uge har vi hul på endnu en måned og jeg skriver lidt om Locking, Blocking og Deadlocking på SQL serveren.

Husk at skrive dig på maillisten nedenfor, så du ikke misser næste udgave af denne blogserie. Jeg lover dig kun at sende en mail, når der er nyt i denne serie.

Få besked om næste indlæg

Skriv dig gerne op til at modtage en mail, ved næste indlæg. Det kan du gøre nedenfor.



Marketing stuff

Our emails contain marketing stuff, so we need to give you some fine quality fine print: brianbonk will use the information you provide on this form to email you with updates and marketing. You can change your mind at any time by clicking the unsubscribe link in the footer of any email you receive from us, or by contacting us at help@brianbonk.dk. We use Mailchimp as our marketing platform. By checking the box to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing, and that we may process your information in accordance with these terms.

Følg mig på Instagram

Leave a Reply

Your email address will not be published. Required fields are marked *

en_USEnglish