Indexek, amiket a kutya sem használ

(apró technikai malőr miatt a táblázat pillanatnyilag bebújik a sidebar mögé, bocs, legjobb embereink dolgoznak a hiba elhárításán)

Az egyik nagy jósága a SQL 2005+ szervernek, hogy megmutatja az indexek ki(nem)használtságát, illetve elmondja, hogy milyen jelenleg nemlétező index mekkora hasznot hozna. Ezeket természetesen DMV-ken keresztül teszi meg velünk.

Az indexeink kihasználtságát a sys.dm_db_index_usage_stats DMV mutatja meg, egy kis minta így néz ki belőle:

SELECT top 3 * FROM sys.dm_db_index_usage_stats 
WHERE object_id > 255

Az erősen irányított válasz pedig

database_idobject_idindex_iduser_seeksuser_scansuser_lookupsuser_updateslast_user_seeklast_user_scanlast_user_lookuplast_user_updatesystem_seekssystem_scanssystem_lookupssystem_updateslast_system_seeklast_system_scanlast_system_lookuplast_system_update
570957756623345602154356NULL2009.10.18 15:46NULL2009-10-18 16:00:37.09703400NULL2009-10-18 01:00:23.000NULLNULL
5709577566121543560215435621543562009-10-18 16:00:37.097NULL2009-10-18 16:00:37.0972009-10-18 16:00:37.09703400NULL2009-10-18 01:00:23.000NULLNULL
570957756630002154356NULLNULLNULL2009-10-18 16:00:37.09703400NULL2009-10-18 01:00:23.000NULLNULL

Ebből a következőket kell figyelnünk: a db-object-index id-k magukért beszélnek, abból tudjuk, hogy minek is nézzük az adatait. A user seek/scan/lookup és a hozzájuk tartozó last… oszlopok talán a legfontosabbak, ők mutatják meg a tényleges indexhasználatot. Az user_update oszlop érdekes, az index ugyanis akkor update-elődik, ha a tartalmazott adat változik. Ez az úgynevezett indexkarbantartás, ami kissé drága, ezért nem szeretjük a felesleges indexeket. Merő véletlenségből a fenti minta pont tartalmaz egy ilyen indexet, a 3-as számút (egy tábla három indexének az értékeit választottam ki). Látszik, hogy semmi másra nem használja a szerver az indexet, csak karbantartja, frissítgeti. Ez egy tipikus jó jelölt arra, hogy letörölje az ember fia egy vidám hétfő reggelen, ha elég nagy minta alapján még mindig nulla a három mező összege. Volt szerencsém találkozni olyan indexszel, amit 1.1 milliárdszor kellett update-elnie az engine-nek, és soha nem használta. Lehet azt mondani, hogy nem kér enni, de hát szemmel láthatóan ez nem igaz.

Éles szemű egyének észrevehetik, hogy azért a system_scan oszlop értéke egyáltalán nem nulla. Ez anna kköszönhető, hogy az indexstatisztikák frissítéséhez az SQL szerver scannel. Úgyhogy ezt nem kell feltétlenül figyelembe venni.

Mire jó még ez a használati statisztika? Láthatjuk, hogy user_lookup mindig csak az index_id = 1 indexeken történik, azaz a clustered indexen. Természetes, hiszen a bookmark/key lookup itt szokott történni. Lehet, hogy van rá ellenpélda, de azt most kihagynám. És most keressük meg a legnagyobb user_scan értékű indexeket és a hozzájuk tartozó clustered indexeket: ha nagyon magas a clustered indexen a user_lookup érték is és kicsi a scan, akkor találtunk egy jó ellenjelöltet clustered indexre.

És megint, csak egyetlen DMV a sokból, és milyen sok hasznos dolgot árul el… Hát nem csodálatos? (nem, a csodálatos az az, hogy három tesztgépre sikerült három különböző SQL verziót/editiont telepítenem, anélkül, hogy észrevettem volna addig, amíg el nem frakkolt a teszt…)

Visszatérve a címre:

select * from sys.dm_db_index_usage_stats 
where object_id > 255
and user_seeks + user_scans + user_lookups = 0
order by database_id, object_id

Ők azok az indexek, akiket a kutya sem használt – az SQL Server legutóbbi indulása óta. Ez a DMV ugyanis minden szerver restartkor kiürül és újrakezdi a feltöltését a szerver. Tehát ha tegnap óta fut a gép, ne használjuk nagytakarításra az outputot. Tanácsos elmenteni a kimenetet – én mindennap elmentem egy külön táblába, és ha nem indult újra előző nap óta a szerver, akkor az előző napit meeg kitörlöm. Így csak legfeljebb egy napi statisztikát veszíthetek, de megvan hónapokra visszamenőleg minden.

Leave a comment