Case study: DW – optimera dynamisk SQL

Case study: DW – optimera dynamisk SQL Förutsättningar:
* DW applikation
* Test av ny rapport i testmiljön
* Rapporten går mot en stor faktatabell på 40 miljoner rader.
* Fem dimensionstabeller används för detaljnivå
* En köpt 3:e partsprodukt (rapportverktyg) som genererar dynamisk SQL.
* En specifik query, fångad med profiler, som tar extremt lång tid jämfört med andra varianter.
* En timme för att komma med förslag, helst lösa problemet medan användarna är på lunch

Hur närmar man sig problemet? Studera schemat, ingående tabeller, kolumner, index och statistik. Studera statistik från frågan, exekveringsplaner om du har rättigheter i databasen (GRANT SHOWPLAN).

Här: Utvecklarna har tänkt till en del, lagt på ett antal index. Närmare bestämt 10 st + ett PK klustrat index på syntetisk nyckel. Eftersom det är en dw applikation med performance problem och de har mycket av billig disk så gör det inte så mycket, diskmässigt, att man lägger på ytterligare ett index. Bara frågan går snabbare. Fast ibland stjälper det för optimeraren, iställer för hjälper, om det blir för många index att utvärdera. Den utredningen får vänta tills det finns mer tid att analysera. Första försöket på en exempel query, som härstammar från ett vanligt val av parametersättningar i rapportverktyget, infångat med profiler, tar 7 minuter att köra, provar igen med SET STATISTICS IO och SET STATISTICS TIME igång, och det går genast snabbare (när datat redan ligger cachat): lite mer än en minut. Det ger dock följande stats: Scan count 196222, logical reads 100699779 CPU time = 64023 ms, elapsed time = 65586 ms. (10 minuter har gått av uppgiftstiden) Mycket logical reads.

Ser på exekveringsplanen att den syntetiska nyckeln används i en Key Lookup. Undersöker om det inte finns en naturlig nyckel som skulle fungera bättre. Det gör det. Skriver ny kod för byte av klustrat index från syntetiskt ID, som inte används till två kolumner: verkligt id och datum i INT format, som dessutom unikt identifierar datat, (tar också 20 minuter att köra ombyggnaden av det klustrade indexet, men under tiden hinner man studera exekveringsplan och fundera ihop ett nytt indexförslag). Inte alltid det går att påverka på den nivån. Måste egentligen utredas mer innan sådant drastiskt ingrepp görs. Men då det är testmiljön vi kör i och jag undersöker frågan för att få fram några förbättringsförslag, eller åtminstone se vad problemet består i, så är det värt ett försök.

Sådär, nu ser det genast bättre ut, om än inte riktigt nöjd, men nu tar rapporten 12 sekunder, vilket är ganska mycket bättre än > en minut, har tänkt prova senare med flushad cache, låter sig dock inte göras just nu. Måste ta hänsyn i en fler-utvecklar-miljö där alla jobbar med olika saker, samtidigt: Scan count 196239, logical reads 4979636 CPU time = 59998 ms, elapsed time = 10491 ms. 40 minuter har gått av uppgiftstiden och användarna är inte tillbaka från lunchen, vet inte om 12 sekunder är snabbt nog. Lägger även på ett include index, som jag byggde medan jag väntade på att det klustrade byggdes om. Include indexet tar med flera av kolumnerna som är med i SELECT, JOIN och WHERE delarna. Nu är vi nere på 4 sekunder vilket får anses helt ok. Tyvärr kan jag inte ta med alla kolumner då det skulle bli ett gigantisk index och inte hjälpa optimeraren lika mycket som det kostar i disk. Scan count 196222, logical reads 904207 CPU time = 4072 ms, elapsed time = 4357 ms.

Ska man gå vidare? Har jag “rätt” prestanda nu? I det här fallet hade jag 20 minuter “över”. Jag kan inte skriva om själva frågan, den genereras av rapportverktyget så det begränsar en aning vilka möjligheter som finns. Om man har behov av ännu snabbare svarstider så går det alldeles säkert att uppnå, men med köpta applikationer är det lätt att börja sub-optimera och tynga ner andra delar av appen med en förbättring på en sida. “It depends” gäller således även här, som i så många andra sammanhang. Det är upp till beställaren att avgöra. För att få stöd för när det är dags att sluta tar jag ett par försök till, det föreslås t ex ytterligare ett include index av ett antal av kolumnerna, fast en annan uppsättning en den jag just gjorde, men lägger jag på den minskar iofs antal logical reads en del och även cpu något, men det är marginellt och kostar rejält i form av utrymme. Avslutar där (10 minuter till godo. Ska ju dokumenteras också). Rätt performance är oftast målet, inte bästa möjliga performance. Och då har vi inte börjat tänka på snabbare disk-system, mer processorer och/eller mer minne än. Det är mest i high-end system som problemet ligger på den nivån. Kräver att man mäter och helst har en base-line att jämföra med. Börja hur som helst inte med att slänga på mer hårdvara, om det inte är så att du har extremt lite möjligheter att påverka själva SQL Server delen.

/Jonas Bergström