Windows - SQL lekérdezést segítő statisztikák

Lekérdezéseink futtatásakor az optimalizáló algoritmus mindig megpróbálja felbecsülni, hogy melyik lekérdezési eljárás a leggyorsabb. Ezt a statisztikák alapján dönti el, amelyeket az adatok adatbázisba kerülésekor készített. Mit érdemes tudni ezekről a statisztikákról? Hogyan tudunk saját kezűleg javítani a naprakészségen? Le lehet őket tiltani? Mindezeket megtudhatja cikkünkből.
Bevezetés
Az SQL szerver statisztikai információkat képez azokról az adatokról, amelyeket a különböző mezőkben letárol. Ezek az információk elősegítik a lekérdezés feldolgozót, hogy optimális stratégiát vázoljon fel a lekérdezések végrehajtására. Amikor egy indexet készítünk, akkor az SQL szerver automatikusan tárol le statisztikai információkat az indexelt oszlopok adatainak eloszlását illetően. Az SQL lekérdezés optimalizálója felhasználja ezt a statisztikát ahhoz, hogy megbecsülje az index használatát a lekérdezésben. Ezen felül, amikor az AUTO_CREATE_STATISTICS adatbázis opció bekapcsolt állapotban van, a szerver automatikusan statisztikát készít az oszlopokról az indexek felhasználása nélkül.
Az elavult statisztika
Amint az adat változik az oszlopban, úgy idővel a rá vonatkozó index és oszlop statisztika elavul, és ez azt okozhatja, hogy a lekérdezés optimalizáló feldolgozást segítő döntései egyre messzebb járnak majd az optimálistól. Például ha létrehozunk egy táblát ezer rekorddal, és egy indexelt oszloppal, és az index mező minden értéke egyedi, akkor a lekérdezés optimalizáló megállapítja, hogy az indexelt keresés egy jó módszer az adatok lekérdezéséhez. Ha módosítunk az adatokon, és ezáltal nagyszámú duplikált adat áll elő, akkor ez az oszlop már nem a legideálisabb a lekérdezésben való használatra, viszont az optimalizáló a korábban készült statisztika alapján még mindig azonos döntést fog hozni a lekérdezés feldolgozását illetően.
Automatikus frissítés
Ahhoz, hogy az ilyen hátrányos döntéseket elkerülje, az SQL szerver automatikusan frissíti a statisztikát rendszeresen, az adat változásával együtt. A mintavételezés véletlenszerű az adatlapokat illetően, és a táblát, vagy a legkisebb nonclustered index-et használja fel a statisztikához. Miután egy adatlap beolvasásra került a lemezről, minden sort az adatlapból felhasznál a statisztikához. A statisztika frissítésének gyakorisága az oszlopban vagy index-ben lévő adat mennyiségétől és az adatváltozás mértékétől függ. Például egy statisztika egy 10000 soros táblához frissítődhet, amikor 1000 index érték változott, ugyanis az 1000 érték már egy jelentős százaléka az adatoknak. Ha viszont egy tábla 10 millió indexelt értéket tartalmaz, akkor 1000 érték változása még nem számottevő, és a statisztika nem változik automatikusan. Az SQL szerver viszont mindig meghatároz egy minimum értéket, amennyi sort felhasznál a mintavételhez. A 8 MB-nál kisebb táblákat mindig teljesen beolvassa a statisztikakészítéshez.
Az automatikus statisztika frissítésének ára minimalizálható a mintavételezéssel. Sokkal eredményesebb így, mint analizálni az egész adathalmazt. Néhány körülmény hatására a statisztikai mintavétel nem tudja hitelesen jellemezni az adatokat a táblában, ezért saját kezűleg is kontrollálni tudjuk a statisztikai mintavételhez felhasznált adatmennyiséget manuális statisztikafrissítéssel. Használjuk a SAMPLE és FULLSCAN záradékot az UPDATE STATISTICS utasításban.
UPDATE STATISTICS Vasarlok WITH FULLSCAN
A FULLSCAN feltétel segítségével minden adatot felhasználunk a statisztika előállításához, ellenben a SAMPLE feltétel segítségével meghatározhatjuk százalékban, hogy mennyi adatot szeretnénk a statisztikához felhasználni.
UPDATE STATISTICS Eladok WITH SAMPLE 10 PERCENT
Statisztika készítés tiltása
Azt szintén közölhetjük a szerverrel, hogy ne kezdeményezze statisztika létrehozását egy megadott oszlopra, vagy index-re, ily módon:
  • Használjuk az sp_autostats rendszer tárolt eljárást
  • Használjuk a STATISTICS_NORECOMPUTE feltételt a CREATE INDEX utasításban
CREATE INDEX au_id_ind ON authors (au_id) WITH STATISTICS_NORECOMPUTE
  • Használjuk a NORECOMPUTE feltételt az UPDATE STATISTICS utasításban
UPDATE STATISTICS Vasarlok WITH FULLSCAN, NORECOMPUTE
  • Használjuk a NORECOMPUTE feltételt a CREATE STATISTICS utasításban
CREATE STATISTICS Vasarlok WITH NORECOMPUTE
  • Kapcsoljuk ki az AUTO_CREATE_STATISTICS és az AUTO_UPDATE_STATISTICS adatbázis opciókat az ALTER DATABASE utasítás segítségével
ALTER DATABASE NORTHWIND SET AUTO_UPDATE_STATISTICS OFF
Ha az SQL szervert úgy állítjuk be, hogy automatikusan ne frissítse a statisztikai információkat, akkor ezt nekünk kell manuálisan megtennünk.
Az UPDATE STATISTICS utasítás visszakapcsolja az automatikus statisztika frissítést, ha nem használjuk a NORECOMPUTE feltételt.
Statisztika készülhet minden alkalmas oszlopra, minden felhasználói táblára az aktuális adatbázisban egy parancs segítségével. Ezt az sp_createstats tárolt eljárással kezdeményezhetjük.
Egy oszlophoz tartozó statisztika törölhető, ha már nem akarjuk, hogy megmaradjon. Az SQL szerver által készített statisztika idővel elavul és törlődik.
Több oszlopos statisztika
A saját kezűleg készített statisztika lehetővé teszi, hogy több oszlopot is felhasználjunk hozzá. Például, ha egy lekérdezés ilyen feltételt tartalmaz:
WHERE a=7 and b=5
Egyedi statisztika készítése mindkét oszlopra elősegíti az SQL szervernek a jobb eredmény megbecsülését, ugyanis a statisztika így tartalmazni fogja a különálló értékek átlagos számát az a és b oszlop kombinációra.
CREATE STATISTICS names 
ON Customers (CompanyName, ContactName)
WITH SAMPLE 5000 ROWS
Ebben az esetben egy 5000 soros minta segítségével készül statisztika két oszlop, a CompanyName és Contactname felhasználásával.