SQL Server lekérdezések optimalizálása

1. SQL Server lekérdezésekoptimalizálása Soczó Zsolt ASP.NET MVP, MCSD, MCDBAzsolt.soczo@gmail.com http://soci.huResearch EngineerQualification Developement

2. Tartalom • Azoptimalizálás lélektana • Indexhangolás • Automatizált segítségek • Állatorvosi lovak

3. Munkafolyamat • Alapoperációs rendszer adatok gyűjtése • SQL Server példány behatárolása • Az várakozási idők eloszlásának meghatározása • Adatbázisok terhelésének behatárolása • Optimalizálandó lekérdezések azonosítása • Az optimalizálás módjának meghatározása • Optimalizálás-ellenőrzés • GOTO 5

4. AlapOS adatok gyűjtése I. • Teljesítményszámlálók vizsgálata • Diszk • LogicalDisk: DiskQueueLength • Storage-oknál általában nem értékelhető egyszerűen • LogicalDisk: Avg. Disk sec/Transfer • 0-10ms jó, 10-20 közepes, 20-50 terhelt, 50- túlterhelt • Processzor • System: ProcessorQueueLength • Processzoronként tartósan > 2, túlterhelt (de nem biztos) • Processor: % Processor Time • Tartósan 80% felett terhelt

5. Alap OS adatok gyűjtése II. • Memória • Memory: Pages/sec • Nem feltétlenül vergődik, ha nagy az értéke • KB: 139609 • Memory: AvailableMbytes • Tartós 5 körüli érték azt jelzi, szűkös a RAM • Hálózati interfész • Network Interface: Bytes Total/sec

6. SQL Server példány behatárolása

7. Várakozási idők eloszlásának meghatározása • DMV: sys.dm_os_wait_stats select '%signal (cpu) waits' = 100 * sum(signal_wait_time_ms) / sum (wait_time_ms), '%resource waits'= 100 * sum(wait_time_ms - signal_wait_time_ms) / sum (wait_time_ms) from sys.dm_os_wait_stats +

8. Adatbázisok terhelésének behatárolása • DMV: sys.dm_io_virtual_file_stats

9. Adatbázis áttekintés riportokkal

10. Optimalizálandó lekérdezések azonosítása • Legfőbb fegyverünk: SQL Server Profiler • Adatbázis aktivitás monitorozására • Sokrétűen konfigurálható, szűrhető, rendezhető • Főbb oszlopok • Text: a művelet maga • Duration: a művelet teljes hossza • Read: lapolvasások száma (1 lap 8kByte) • Write: lapírások száma • CPU: felhasznált CPU idő

11. Profiler kimenet és végrehajtási terv

12. Az optimalizálás módjának meghatározása • Nagy Duration esetén

13. Optimalizálás - Hiányzó indexek azonosítása, létrehozása és tesztelése • Vizsgálandó lekérdezés futtathatóvá tétele • Profiler bekapcsolása, csak erre a Session-re • SET STATISTICS_IO ON • IncludeActualExecutionPlan bekapcsolás • Lefuttatás • Duration, Read, CPU lejegyzése Profilerből • Táblánként IO ellenőrzése STATISTICS_IO-ból • Végrehajtási terv elemzése, Cost lejegyzése • Optimalizálás, majd GOTO 5

14. Indexelés stratégiák • Nincs index (heap) • Nonclustered index • Clustered index • Nonclustered és Clustered index • Nonclusteredcover index seek • Nonclusteredcover index unorderedscan

15. Extent Bit Map … 127 1 01 01 01 01 01 01 01 Con Dunn Con Con Con Graff Con … … … … … … … 128 1 02 02 02 02 02 02 02 Bacon Funk Funk Funk Funk Randall Funk … … … … … … … 129 0 03 03 03 03 03 03 03 Ota White White Koch White White White … … … … … … … 130 1 … … 04 … 04 … … ... ... ... Durkin ... ... Slichter ... ... ... ... ... ... ... … … … … … … 05 05 ... LaBrie ... ... ... ... Lang ... ... ... ... ... ... ... 01 Seattle … 02 Paris … 03 Tokyo … 01 01 01 01 01 01 01 01 Rudd Rudd Rudd Rudd Rudd Rudd Rudd Rudd … … … … … … … … 01 01 01 01 01 01 01 01 Akhtar Akhtar Akhtar Akhtar Akhtar Akhtar Akhtar Akhtar … … … … … … … … 01 01 01 01 01 01 01 01 Smith Smith Smith Smith Smith Smith Smith Smith … … … … … … … … 04 Atlanta ... 02 02 02 02 02 02 02 02 White White White White White White White White … … … … … … … … 02 02 02 02 02 02 02 02 Funk Funk Funk Funk Funk Funk Funk Funk … … … … … … … … 02 02 02 02 02 02 02 02 Ota Ota Ota Ota Ota Ota Ota Ota … … … … … … … … … ... ... 03 03 03 03 03 03 03 03 Barr Barr Barr Barr Barr Barr Barr Barr … … … … … … … … 03 03 03 03 03 03 03 03 Smith Smith Smith Smith Smith Smith Smith Smith … … … … … … … … 03 03 03 03 03 03 03 03 Jones Jones Jones Jones Jones Jones Jones Jones … … … … … … … … … … … … … … … … ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... 04 04 04 04 04 04 04 04 Martin Martin Martin Martin Martin Martin Martin Martin ... ... ... ... ... ... ... ... … … … … … … … … … … … … … … … … ... ... ... ... ... ... ... ... … … … … … … … … ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... … … … … … … … … ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... … … … … … … … … ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... Nincs index (Heap) IAM Heap Extent 128 Extent 129 Extent 130 Extent 127

16. Non Clustered Index Non-Leaf Level Akhtar ... Martin Akhtar Akhtar Page 37 Page 28 Page 12 - Root Ganio Ganio Martin Martin ... ... Smith ... Leaf Level(Key Value) Page 41 Page 51 Page 61 Page 71 Akhtar Akhtar 4:706:01 4:706:01 Smith Ganio Ganio Smith 4:709:01 4:706:03 4:706:03 4:709:01 Barr Barr 4:705:03 4:705:03 Smith Hall Hall Smith 4:709:04 4:709:04 4:708:04 4:708:04 Martin 4:708:01 Con Con 4:704:01 4:704:01 Smith Smith Jones Jones 4:707:01 4:707:01 4:709:02 4:709:02 Matey Matey 4:706:04 4:706:04 Funk Funk 4:706:02 4:706:02 White White Jones Jones 4:708:03 4:708:03 4:704:03 4:704:03 Ota 4:707:02 Funk Funk 4:704:02 4:704:02 Jones Jones White White 4:705:02 4:705:02 4:707:03 4:707:03 Ota Phua Phua 4:708:02 4:707:02 4:708:02 Rudd Rudd 4:705:01 4:705:01 Heap Page 704 Page 705 Page 706 Page 707 Page 808 Page 709 01 ... Akhtar 01 01 ... ... Conn Conn 01 01 01 ... ... ... Rudd Rudd Rudd 01 01 ... ... Smith Smith 01 01 ... ... Martin Martin 01 01 ... ... Ganio Ganio 02 ... Funk 02 02 ... ... Funk Funk 02 02 ... ... White White 02 02 ... ... Ota Ota 02 02 02 02 ... ... ... ... Ota Phua Phua Phua 02 02 ... ... Jones Jones 03 ... Smith 03 03 ... ... White White 03 03 ... ... Barr Barr 03 03 ... ... Jones Jones 03 03 ... ... Jones Jones 03 03 ... ... Hall Hall 04 ... Matey ... ... ... ... ... ... ... ... ... ... ... ... 04 ... ... ... ... ... ... ... 04 04 ... ... Smith Smith ... ... ... ... ... ... Matey ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... File ID #4 Nonclustered Index Heapen select * from tabla where lastname > ‘Martin’ and lastname < ‘Smith’ Non clustered Index Közbenső és gyökérszint Akhtar ... SELECT lastname, firstname FROM member WHERE lastname BETWEEN 'Masters' AND 'Rudd' Martin Martin Page 37 Page 28 Page 12 - Root Martin Smith ... Levélszint(Kulcsérték + sormutató) Page 41 Page 51 Page 61 Page 71 Martin 4:708:01 Matey 4:706:04 Ota 4:707:02 Phua 4:708:02 Rudd 4:705:01 Heap (adatlapok) Page 704 Page 705 Page 706 Page 707 Page 708 Page 709 01 ... Akhtar 02 ... Funk 03 ... Smith 04 ... Matey ... ... ...

17. Olvasson Ön is Books Online-t! • Dokumentáció • Áttekintések • Architektúra • Fejlesztői útmutatások • Üzemeltetési leírás • Tutorial-ok • Keresés

18. Clustered Index Akhtar Akhtar … … Martin Martin Page 140 - Root Akhtar Akhtar Martin Ganio Ganio Smith … … … Page 141 Page 145 Akhtar Akhtar 2334 2334 ... ... Ganio Ganio 7678 7678 ... ... Martin Martin 1234 1234 ... ... Smith Smith 1434 1434 ... ... Barr Barr 5678 5678 ... ... Hall Hall 8078 8078 ... ... Martin Martin 7778 7778 ... ... Smith Smith 5778 5778 ... ... Con Con 2534 2534 ... ... Jones Jones 2434 2434 ... ... Ota Ota 5878 5878 ... ... Smith Smith 7978 7978 ... ... Funk Funk 1334 1334 ... ... Jones Jones 5978 5978 ... ... Phua Phua 7878 7878 ... ... White White 2234 2234 ... ... ... ... ... ... ... ... ... ... Funk Funk 1534 1534 Jones Jones 2634 2634 Rudd Rudd 6078 6078 White White 1634 1634 ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... Page 100 Page 110 Page 120 Page 130 Ota 5878 ... Clustered Index select * from tabla where lastname = ‘Ota’ Clustered Index Közbenső és gyökérszint Martin Page 140 - Root SELECT lastname, firstname FROM member WHERE lastname = 'Ota' Martin Martin Smith … Page 141 Page 145 Page 100 Page 110 Page 120 Page 130 Adatlapok

19. Nonclustered Index on First Name Non-Leaf Level Aaron Aaron ... ... Jose Jose Aaron Aaron Jose Jose Deanna Deanna Nina Nina … … … … Leaf Level(Clustered Key Value) Aaron Aaron Con Con Jose Jose Lugo Lungo Deanna Deanna Daum Daum Adam Adam Barr Barr Judy Judy Kaethler Kaethler Don Don Hall Hall Amie Amie Baldwin Baldwin Mike Mike Nash Nash Doug Doug Hampton Hampton … … … … … … … … … … … … Barr Barr Clustered Index On Last Name Kim Kim Nagata Nagata O’Melia O’Melia Barr Barr Adam Adam … … Kim Kim Shane Shane … … Nagata Nagata Susanne Susanne … … Cox Cox Arlette Arlette … … Kobara Kobara Linda Linda … … Nash Nash Nash Mike Mike Mike … … … Daum Daum Deanna Deanna … … LaBrie LaBrie Ryan Ryan … … Nixon Nixon Toby Toby … … … … … … … … … … … … … … … … … … … … Nonclustered Index Clustered Indexen Nonclustered Index a FirstName-en Gyökér és közbenső szint SELECT lastname, firstname, phone FROM member WHERE firstname = 'Mike' Levélszint(NC + Clu Kulcsok) Mike Nash Clustered Index LastName-en Nagata

20. Akhtar … Akhtar Jordan … … Jordan … Chai Lang … … … … Dunn Morgan … … Ganio Smith … … Dunn … Lang … Dunn … Martin … Fine … Martin … Fort … Martin … Akhtar Chai … … Morgan Smith … … Funk … Moris … Con Barr … … Nash Smith … … Barr Con … … Smith Nay … … Borm Cox … … Smith Ota … … Dale Buhl … … Smith Rudd … … NC Cover Seek SELECT lastname, firstname FROM tabla WHERE lastname BETWEEN 'Funk' AND 'Lang' Index lapok Gyökér és közbenső szint Index a lastname, firstname oszlopkon Levélszint(Kulcsok) Ganio … Jordan … Hall … Kim … Hart … Kim … Jones … Koch … Jones … Koch … Adatlapok

21. Akhtar … Chai … Dunn … Ganio … Jordan … Lang … Morgan … Smith … Barr … Con … Dunn … Hall … Kim … Martin … Nash … Smith … Barr … Con … Fine … Hart … Kim … Martin … Nay … Smith … Borm … Cox … Fort … Jones … Koch … Martin … Ota … Smith … Buhl … Dale … Funk … Jones … Koch … Moris … Rudd … Smith … Martin Akhtar Akhtar … Ganio Smith … Martin … NC Cover Scan SELECT lastname, firstname FROM tabla WHERE ... Indexlapok Gyökér és közbenső szint Levélszint(Kulcsok) … Adatlapok

22. IncludedColums (2005) • Cover indexekhezkiváló • Kulcsoszlopok indexfába • Nemkulcs oszlopok a lefedéshez • Csak az NC index levélszintjén jelennek meg • Az INCLUDE-ed oszlopok tetszőleges méretűek lehetnek (pl. NVARCHAR(MAX) is) Kulcs oszlop(ok) Nemkulcs oszlopok CREATE INDEX IX_Address_PostalCode ONPerson.Address (PostalCode) INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

23. Indexelési alternatívák

24. Filtered Index (2008) • Az adatok részhalmazára épített nonclustered index • Kisebb indexfa, kisebb karbantartási költség create nonclustered index IX_BOF on Production.BillOfMaterials (ComponentID, StartDate) where EndDate IS NOT NULL

25. Hiányzó indexek azonosítása • DMV-kkel • sys.dm_db_missing_index_group_stats • sys.dm_db_missing_index_groups • sys.dm_db_missing_index_details • sys.dm_db_missing_index_columns • DatabaseTuningAdvisor

26. Hiányzó indexek azonosítása, index létrehozása

27. Állatorvosi lovak • Felesleges kurzorhasználat • Eltúlzott dekompozíció • COUNT vs. EXISTS • Függvényben használt szűrendő oszlop • Implicit konverziótípuseltérésmiatt

28. Praktikák • AutoGrow lassú lehet • Nem shrinkel! • Nem hintel! • AutoCreateStatistics, Auto Update statistics legyen bekapcsolva • Read-onlydatabase, nincs sharedlockselectnél • Nem pakolunk össze sok logot egy diszkre • SQL 2005-től partícionálás nagy adatokhoz • MAXDOP • Kis tranzakciókat összenyalábolni • Indexelt nézetek • # vs. @ és az újrafordítások • Fragmentáltság • DISTINCT és 5 NF • Index oszlop rendezési iránya • UNION vs. UNION ALL • *, NOT, OR, IN -nemszeretjük • Gyorsan vigyük el a lekérdezés eredményét • Csakannyi adatot válogassunk le amennyi tényleg kell • SELECT * - nem! • Prepare • Egymásba ágyazott nézetek felesleges adatokkal • Adattömörítés • Minimallylogged insert-select • MERGE • Lokális változó vs. paraméter (SARG) • Recompilations • Elnevezés (sp_, fn_)