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.

UNIQUE és több NULL

(This article has an English version…)
Ha szeretnénk biztosítani azt, hogy egy oszlop értékei mind egyeidek legyenek, akkor használhatjuk a UNIQUE kulcsszót (akár constraint, akár index formában), hogy ezt kierőszakoljuk. Ez eddig szép és jó, de mi a helyzet a NULL értékekkel? Egy NULL-t simán be tudunk szúrni (ez ugye a nagy különbség a UNIQUE és a PRIMARY KEY között, utóbbi nem enged semmilyen NULL-t), de mi van, ha sok NULL-t akarok bepakolni? Continue reading ‘UNIQUE és több NULL’ »

UNIQUE INDEX vs multiple NULL values

In Microsoft SQL Server, if you want to make sure that a column contains no duplicates, you can use the UNIQUE keyword to enforce this constraint, either by creating a unique index or by adding a unique constraint to the column. It’s very useful and nice, but… What about NULL values? I mean, obviously, you can insert the first NULL value, but what if you have more null values and you need to enforce only the uniqueness of the non-null values? Continue reading ‘UNIQUE INDEX vs multiple NULL values’ »