Windows - Lekérdezések vizsgálata

Adatbázis optimalizálás 5. rész

forráskód letöltése
A lekérdezések optimalizálásánál fontos lehet megvizsgálni, hogy az optimalizáló milyen futtatási tervet készít. A cikkben ismertetjük ezek megjelenítési lehetőségeit, és javaslatokat is találunk a bonyolultabb, nagy erőforrás igényű lekérdezések átgondolt használatához.
Bevezető
Ha SQL adatbázisunk használhatóságát szeretnénk javítani, gyorsítani a lekérdezéseket, akkor nem elég az adatbázis logikai és fizikai struktúrájával, elhelyezkedésével foglalkozni, hanem meg kell vizsgálni a lekérdezéseket is, hiszen nagymértékben függ tőlük, hogy a kívánt adatokat milyen gyorsan, milyen hatékonyan tudja a rendszer a rendelkezésünkre bocsátani.
Lekérdezéseink vizsgálatára kiváló eszköz az SQL szerver Profiler. Segítségével könnyen beazonosíthatjuk azokat a lekérdezéseket, amelyek processzorigénye, lemez I/O műveleti igénye magas. Ha pontosabban szeretnénk megvizsgálni az egyes lekérdezéseket, akkor erre az esetre néhány speciális kapcsoló segítségét vehetjük igénybe.
Lekérdezések vizsgálata SET kapcsolókkal és grafikusan megjelenített terv segítségével
Az alábbi kapcsolók használata során a lekérdezések nem futnak le a szerveren, hanem elemzésre kerülnek, és a visszaadott eredménytábla tartalmazza a lekérdezés speciális lefutásához kapcsolódó információkat.
  • A SHOWPLAN_ALL kapcsoló a lekérdezés metódusát szolgáltatja
  • A STATISTICS IO kapcsoló a logikai és fizikai olvasási műveletek vizsgálatára hivatott
  • A STATISTICS TIME kapcsoló a lekérdezés elemzéséhez, lefordításához, és futtatásához szükséges időt mutatja meg
  • A STATISTICS PROFILE a lekérdezéshez tartozó profile információkat mutatja meg
A kapcsolókat úgy használjuk, hogy pl. a Query Analyzer-ben futtassuk le az alábbi batch-t:
USE Northwind
GO
SET SHOWPLAN_ALL ON
GO
SELECT * FROM Employees WHERE LastName like '%l%'
GO
SET SHOWPLAN_ALL OFF
GO
Az eredménytáblázatot a mellékelt eredmeny.txt állományban láthatjuk. A benne foglalt tartalom alapján megállapíthatjuk, hogy a lekérdezés milyen módszert használ az adatok összeállításához.
A Query Analyzer ezeket az adatokat képes grafikusan is, közérthetőbb formában megjeleníteni, ha bekapcsoljuk a Display Estimated Execution Plan kapcsolót. Ezt a CTRL+L billentyűkombinációval könnyen megtehetjük.
A lekérdezés mindig a lehető legoptimálisabban fut le. Erről a lekérdezés optimalizáló gondoskodik a számára szolgáltatott statisztikai adatok alapján. A lekérdezési metódus, vagy lekérdezési terv szerint ha túl nagy erőforrásigénye van egy lekérdezésnek, akkor vizsgáljuk meg a statisztikát, hogy vajon mennyire naprakész. Ezen túlmenően gondoljuk át, hogy index létrehozása gyorsíthatja-e a lekérdezést, és megéri-e ezért indexet létrehoznunk.
A Query Analyzer a fentiekben említett grafikus lekérdezési terv megjelenítésén túl képes javaslatot tenni újabb indexekre, vagy statisztikákra indexelés alól kihagyott oszlopokat illetően, ami növelheti a lekérdezés futtatásának gyorsaságát.
Lekérdezés optimalizáló javaslatok
Néhány lekérdezés önmagában erőforrásfaló. Ez az alapvető adatbázis és index környezettel áll kapcsolatban. Ezeket a lekérdezéseket nem tarthatjuk kevésbé használhatónak, ugyanis a lekérdezés optimalizáló az elérhető legeredményesebb módon fogja végrehajtani. Ezek mégis nagy erőforrás igényűek, és a Transact-SQL környezet nem tartja hatékonynak őket. A lekérdezés optimalizáló intelligenciája nem tudja kiküszöbölni ezt a belső erőforrásigényt.
A következő típusú lekérdezések lehetnek ilyen erőforrás igényűek:
  • Nagyméretű eredménytáblát szolgáltató lekérdezések
  • Nagymértékben nem egyedi WHERE feltételek
A lekérdezések optimalizálását célzó javaslatok a következők:
  • Használjunk több memóriát. (Ha a szerver sok bonyolult lekérdezést futtat, és ezek lassan futnak)
  • Az SQL szervert többprocesszoros gépen futtassuk. (Ez párhuzamos lekérdezés futtatást tesz lehetővé)
  • Fontoljuk meg a lekérdezés átírását
  • Ha a lekérdezés kurzort használ, akkor megállapíthatjuk, hogy a lekérdezés sokkal eredményesebb lehet, ha más típusú kurzort használ: fast forward-only, vagy egyedüli lekérdezés. Az egyedüli lekérdezések általában teljesítményben felülmúlják a kurzor műveleteket.
  • Ha az alkalmazás ciklust használ, akkor fontoljuk meg ciklus elhelyezését a lekérdezésen belül. Gyakran az alkalmazások olyan ciklust tartalmaznak, amelyek paraméterezett lekérdezéseket futtatnak, és többször lefutnak, így nagyméretű hálózati oda-vissza forgalmat generálnak az SQL és IIS szerver között. Inkább használjunk egy bonyolult lekérdezést átmeneti táblával, így a hálózati adatforgalom is lecsökken, és a lekérdezés optimalizáló "jobban kézbe tudja venni" az egyedi lekérdezést.
  • Ne használjunk több alias nevet ugyanahhoz a táblához egy lekérdezésen belül indexek keresztezéséhez. Ez nem szükséges, mivel az SQL szerver automatikusan figyelembe veszi az index keresztezéseket, és használni tud több indexet egy táblához egy lekérdezésen belül.
  • Ne használjunk query hint-eket, csak nagyon szükséges esetben, mert ezek a korábbi verziójú szervereken voltak fontosak. Ha ezeket használjuk, akkor a lekérdezés optimalizáló nem tudja kifejteni hatását, és automatikusan megkeresni az optimális működtetést.
  • Használjuk a query governor konfigurációs opciót, beállítást. Ez lehetővé teszi, hogy korlátozzuk a hosszú lefutású lekérdezéseket, hogy ne tudják felemészteni az erőforrásokat. Alapesetben ez úgy van beállítva, hogy nincs semmiféle korlátozás. Megadható egy másodperc érték a lekérdezések futásának korlátozására. Ha a SET QUERY_GOVERNOR_COST_LIMIT beállítást használjuk, akkor az aktuális kapcsolatra lesz érvényes a beállítás. Előnye mindennek az, hogy a várható futási idő alapján ítél, tehát el sem indul egy olyan lekérdezés, amelynek várható futtatási ideje meghaladja a beállított szintet.

Adatbázis optimalizálás cikksorozat