Free Press

SQL-indeksien luomisen tarkoituksena tietokantataulukoihin on parantaa sellaisten kyselyjen suorituskykyä, jotka lukevat tietoja kantataulukosta, nopeuttamalla tiedonhakuprosesseja. Nämä transaktiotaulukot eivät kuitenkaan ole staattisia ja muuttuvat hyvin usein ajan myötä. Näihin tietokantatauluun tehtäviin muutoksiin kuuluu uusien tietueiden lisääminen, olemassa olevien tietueiden muuttaminen tai poistaminen. Koska nämä muutokset olisi toistettava niihin liittyviin taulukkoindekseihin, taulukkoindeksi pirstaloituu ajan mittaan.

SQL-palvelimen indeksin pirstaloitumisen yleiskatsaus

SQL-indeksin pirstaloitumisessa on kaksi päätyyppiä; sisäinen ja ulkoinen pirstaloituminen. Sisäinen pirstoutuminen aiheutuu uuden tietueen lisäämisestä tai olemassa olevan tietueen päivittämisestä arvoilla, jotka eivät mahdu tietosivun nykyiseen vapaaseen tilaan ja johtavat sivun jakamiseen kahdeksi sivuksi, jotta uusi arvo mahtuu. Tässä tapauksessa SQL Server Engine siirtää noin 50 prosenttia tiedoista nykyiseltä sivulta uudelle datasivulle, joka syntyy jaetun operaation tuloksena, jotta SQL-indeksipuu pysyy tasapainossa. Sisäistä pirstoutumista voivat aiheuttaa myös satunnaiset poisto-operaatiot, jotka aiheuttavat tyhjää tilaa datasivuilla. Tämäntyyppiset operaatiot jättävät datasivuja täyttämättä ja johtavat suurempaan SQL Server -indeksin kokoon ja hitaampaan suorituskykyyn, koska pyydettyjen tietojen hakeminen edellyttää useampien datasivujen lukemista.

Ulkoinen pirstaloituminen aiheutuu, kun SQL Server -indeksin datasivut ovat hajallaan tietokannan fyysisessä tiedostossa, koska SQL Server -indeksin looginen järjestys ei vastaa fyysistä järjestystä taustalla olevassa levyasemassa. Ulkoinen pirstoutuminen voi johtua satunnaisista poisto-operaatioista, jotka jättävät osan laajuussivuista tyhjiksi, vaikka itse laajuus on varattu. Tämäntyyppinen pirstaloituminen johtaa suorituskyvyn heikkenemiseen, koska hajallaan olevien tietosivujen välillä hyppiminen vie enemmän aikaa ja kuluttaa enemmän resursseja, jotta pyydetyt tiedot voidaan hakea taustalla olevalta levyasemalta.

Syy siihen, miksi SQL-indeksin pirstaloitumisesta ollaan huolissaan, on se, että indeksi, jonka olet luonut parantaaksesi systeemin suorituskykyä, saattaa heikentää kyselyidesi suorituskykyä sen jälkeen, kun se on pirstaloitunut voimakkaasti. Kuvittele ero ajassa ja levylukujen määrässä sen välillä, kun luetaan tietty määrä rivejä terveestä SQL-indeksistä, jossa tiedot on tallennettu tietokantatiedoston vierekkäisille tietosivuille, ja kun luetaan sama määrä rivejä erittäin pirstaloituneesta SQL Server -indeksistä, jossa tiedot on hajautettu tietokantatiedoston ei- vierekkäisille tietosivuille.

Jos havaitaan, että taulukon SQL-indeksi on pirstaloitunut, pirstaloituneisuusongelma on korjattava suorittamalla SQL Server -indeksin uudelleenjärjestelyä (SQL Server Index Reorganization) tai indeksin uudelleenkehittämistä (Index Rebuild), joka perustuu SQL-indeksin hajanaisuusprosenttiosuuteen. Esimerkiksi SQL Server -indeksi, jonka pirstaloitumisprosentti on alle 30 %, voidaan järjestää uudelleen, kun taas SQL-indeksi, jonka pirstaloitumisaste on yli 30 %, on rakennettava uudelleen.

• Huomautus: Katso lisätietoja kohdasta SQL Server -indeksien ylläpitäminen.

SQL Server -indeksin pirstaloitumis- ja sivujen pilkkoutumisongelmista voi selviytyä ennaltaehkäisevästi suunnittelemalla indeksin oikein ja asettamalla fill factor- ja pad_index-indeksin luontiasetukset propervalues.

• Huomautus: Katso lisätietoja kohdasta SQL Server -indeksin käyttö.

SQL Server Index Fragmentation Report

Parhaana käytäntönä ei pidetä sitä, että luodaan SQL-indeksi tietokantataulukkoon vain siksi, että voidaan hyötyä tiedonhakutoiminnon parannuksesta, ja jätetään kyseinen SQL Server -indeksi ikuisiksi ajoiksi ilman, että sen pirstoutumista seurataan jatkuvasti ja sitä ylläpidetään.

SQL Server tarjoaa meille useita menetelmiä, joita voidaan käyttää SQL-indeksin pirstoutumisprosentin tarkistamiseen.

SQL Server Index Properties

Ensimmäinen menetelmä on SQL Server Index Properties -ikkunan Fragmentation-sivu. Napsauta hiiren kakkospainikkeella tarkistettavaa indeksiä ja valitse Ominaisuudet-vaihtoehto. Selaa SQL Server Index Properties -sivulta Fragmentation-sivua, ja näet indeksin pirstaloitumisprosentin ja sen, kuinka paljon SQL Server -indeksin sivuja on täynnä Fragmentation-osiossa, kuten alla näkyy:

Muutamassa vaiheessa voit helposti tarkistaa valitun indeksin SQL Server -indeksin pirstaloitumisprosentin indeksin ominaisuuksien ikkunasta. Mutta entä jos tarvitset yleiskuvan kaikkien taulujen indeksien tai kaikkien tietokantataulujen indeksien pirstoutumisprosentista? Tässä tapauksessa sinun on tarkistettava se yksitellen. Tämän seurauksena yksi työviikko ei riitä yhteen tietokantaraporttiin!

sys.dm_db_index_physical_stats

Toinen tapa, jota voidaan käyttää tietokantaindeksien pirstaloitumisprosentin tarkistamiseen, on kysyä dynaamista hallintatoimintoa sys.dm_db_index_physical_stats, joka otettiin käyttöön ensimmäisen kerran SQL Server 2005:ssä korvaamaan vanhentunutta DBCC SHOWCONTIG -komentoa. Se antaa tietoja tietokannan indeksien koosta ja pirstaloitumisprosentista.

Jotta sys.dm_db_index_physical_stats DMF:stä saisi mielekästä tietoa kaikkien määritetyn tietokannan alla olevien indeksien pirstaloitumisprosentista, se on yhdistettävä sys.indexes DMV:hen, kuten alla olevassa T-SQL-skriptissä:

SELECT OBJECT_NAME(Index_Info.OBJECT_ID) AS TableName ,Index_Info.name AS IndexName ,Index_Stat.index_type_desc AS IndexType ,Index_Stat.avg_fragmentation_in_percent IndexFragmPercentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) Index_StatINNER JOIN sys.indexes Index_Info ON Index_Info.object_id = Index_Stat.object_id AND Index_Info.index_id = Index_Stat.index_idORDER BY IndexFragmPercent DESC

SQL-kehitystaitojesi perusteella voit suorittaa suodatuksen, jota tarvitset SQL-indeksien pirstaloituneisuuden hakemiseen tarvittavassa laajuudessa, eikä sinulla ole vaihtoehtoa saada yhteenvetoa kaikista instanssitietokannoista ottaen huomioon, että näytetyt tiedot ovat viimeisimmän SQL Server -palvelun uudelleenkäynnistyksen jälkeen. Tapauksessamme tulos on seuraavanlainen:

SQL Server Index Physical Statistics Standard Report

SQL Server tarjoaa meille useita vakioraportteja, jotka sisältävät hyödyllisiä tilastotietoja valitusta tietokannasta. Index Physical Statistics (Fyysiset indeksitilastot) -raportti on yksi SQL Serverin vakioraporteista, joka palauttaa tilastotietoja SQL Serverin indeksiosioista, pirstaloitumisprosentista ja sivujen määrästä kussakin SQL-indeksiosassa tietokantatasolla viimeisimmän SQL Server -palvelun uudelleenkäynnistyksen jälkeen ja antaa suosituksen siitä, miten indeksin pirstaloitumisongelma korjataan SQL Server -indeksin pirstaloitumisprosentin perusteella, jos sellainen on olemassa.

Jos haluat tarkastella SQL Server Index Physical Statistics -raporttia, napsauta hiiren kakkospainikkeella tietokantaa, valitse Raportit -> Vakioraportit ja valitse sitten Index Physical Statistics -raportti, kuten alla on esitetty:

Ja luotu tietokantatason raportti, jota ei voi suodattaa ja joka tarvitsee kunkin taulukon indeksien tuloksen laajentamisen manuaalisesti, on alla olevan kuvan mukainen:

ApexSQL Defrag

ApexSQL Defrag -työkalu on kolmannen osapuolen SQL-indeksin defragmentointityökalu, jota voidaan helposti käyttää SQL Server -indeksin fragmentointi- ja käyttötietojen tarkistamiseen ja sopivan korjauksen suorittamiseen tai aikatauluttamiseen indeksin fragmentointiongelmaan.

Yhdestä keskitetystä paikasta voit analysoida ja tarkastella pirstaloitumis- ja käyttötietoja eri tasoilla, mukaan lukien SQL-indeksin pirstaloitumis- ja käyttötiedot SQL Serverin indeksitasolla, taulukkotasolla, tietokantatasolla ja lopulta koko SQL Server -instanssitasolla. Se tarjoaa meille erilaisia suodatintyyppejä, joita voidaan käyttää vain kohdeindeksien näyttämiseen ja ylläpitoon.

ApexSQL Defragin avulla voimme myös määrittää taulukkoindeksien täyttökerroinasetukset, luoda hälytyksiä kaikille defragmentointitehtäville ja -käytännöille sekä lähettää sähköposti-ilmoituksen onnistuneista tai epäonnistuneista tehtävistä.

Näytetyt tiedot tarjoavat hyvän näkymän SQL-indeksin koosta ja fragmentoitumisprosenttiosuudesta, johon ei vaikuta SQL Serverin palvelun uudelleenkäynnistäminen tai palvelimen uudelleenkäynnistys. Tämä johtuu siitä, että nämä tiedot tallennetaan ApexSQL Defrag -työkalun keskitettyyn arkistotietokantaan.

ApexSQL Defragin asentaminen

ApexSQL Defrag voidaan helposti ladata ApexSQL Download Centeristä ja asentaa palvelimelle noudattamalla suoraviivaista asennusohjattua ohjatun asennuksen ohjeistusta alla esitetyllä tavalla:

Ensin sinua pyydetään määrittelemään palvelintili, jota ApexSQL Defrag -agentti käyttää ApexSQL Defragin suoritusyhteytenä ja joka välittää SQL Server -instanssin komennot SQL Server -instanssiin seuraavasti:

Kun olet määrittänyt ApexSQL Defrag -palvelutilin, sinua pyydetään määrittämään, mihin ApexSQL Defrag -työkalu asennetaan ja luodaanko työkalulle pikakuvake työpöydälle, kuten seuraavassa näytetään:

Kun olet tarkistanut, että annetussa polussa on vapaana vähintään 127 Mt tilaa, ApexSQL Defrag asennetaan onnistuneesti palvelimelle. Ja näyttöön tulee seuraava ilmoitus, joka antaa sinulle mahdollisuuden käynnistää ApexSQL Defrag suoraan:

Jos käynnistät ApexSQL Defragin ensimmäistä kertaa, sinua pyydetään vahvistamaan keskitetyn arkistotietokannan luominen, johon nämä tietohistoriatiedot ja konfigurointitiedot tallennetaan, seuraavasti:

Add New Server

Kun arkistotietokanta on luotu, ApexSQL Defrag käynnistetään ilman SQL-instanssia. Jos haluat tarkistaa SQL-indeksin pirstoutumisprosentin tietyssä SQL Server -instanssissa, sinun on lisättävä kyseinen instanssi kyseiseen työkaluun. Tätä varten napsauta Home-välilehden Add-painiketta alla olevan kuvan mukaisesti:

Connect to SQL Server -ikkunassa sinua pyydetään antamaan käytettävän SQL Server -instanssin nimi ja kyseiseen palvelimeen muodostettavaan yhteyteen vaadittavat tunnistetiedot seuraavasti:

SQL-palvelimen indeksianalyysi

Kun palvelin on lisätty, voit suorittaa uuden tarkistuksen kaikkien SQL-indeksien pirstaloitumistietojen tarkistamisen alla olevasta Pirstaloituminen-välilehdestä:

ApexSQL Defrag tarjoaa sinulle mahdollisuuden analysoida kaikki kyseisen instanssin indeksit kolmen tilan perusteella:

• DETAILED: jossa kaikki data- ja SQL-indeksisivut luetaan skannausprosessin aikana
• SAMPLED: jossa vain 1 % sivuista luetaan ottaen huomioon, että SQL Server -indeksi sisältää yli 10 000 sivua
• LIMITED: jossa vain b-puun vanhemmalla tasolla sijaitsevat sivut luetaan

Jos haluat analysoida SQL Server -indeksitietoja yhdistetystä SQL Server -instanssista, napsauta Analysoi-painiketta Fragmentaatio-välilehden alla ja valitse, haluatko suorittaa nopean vai syvällisen analyysin. Suoritamme Syväanalyysin saadaksemme täydelliset tiedot, kuten alla näkyy:

Tarkkaile edelleen työkalun alareunassa olevaa edistymispalkkia tarkistaaksesi analyysin etenemisen seuraavasti:

Ja kattava raportti, joka sisältää täydelliset tiedot kaikista SQL-indekseistä, jotka on luotu tämän SQL Server -instanssin kussakin tietokannassa, näytetään, kuten alla näkyy:

Mahdollisuus sukeltaa eri tasojen välillä; SQL Server -instanssi, tietokanta, taulukko tai indeksi, yhdellä napsautuksella samassa keskusikkunassa, ja suodattaa haetut tulokset tietyn fragmentointitason tai kynnysarvon perusteella, kuten alla näkyy:

Lisäksi voit vetää minkä tahansa sarakkeen raportin sarakkeista ryhmitelläksesi kokonaisraportin kyseisen sarakkeen arvojen perusteella, kuten alla olevassa esimerkissä, jossa ryhmittelimme raportin tiedot taulukon nimen perusteella:

ApexSQL Defrag -raportit

ApexSQL Defrag -ohjelmisto tarjoaa useita raportteja, jotka esittävät tilastotietoja tietokannan indekseistä. Nämä raportit voidaan määrittää ja tarkistaa alla olevasta Raportit-välilehdestä:

Ensimmäinen raportti on Kokonaisraportti, joka näyttää graafisen esityksen SQL-indeksien pirstoutumisesta ja levytilan käytöstä valitulla tasolla. Jos napsautat esimerkiksi instanssin nimeä, raportti piirtää pirstaloitumisprosentin kuvaajan ja tilankäytön kuvaajan SQL Server -instanssin tasolla, kuten alla:

Jos napsautat mitä tahansa tietokantaa Server Explorer -ikkunassa, raportti vaihtaa näkymän automaattisesti näyttämään kuvaajan tietokantatasolla, kuten alla:

Toinen raportti, jota voi myös tarkastella eri tasoilla, on Tilastot-raportti. Tämä raportti näyttää tilastotietoja indeksien kokonaiskoosta ja pirstaleisuudesta sekä indeksityyppien jakautumisesta valitulla tasolla, kuten alla näkyy:

Kolmas raportti on Top 10 -raportti, jossa tarkastellaan klusteroituja ja ei-klusteroituja indeksejä, joilla on suurin koko ja korkein SQL-indeksin pirstaloitumisprosentti, määrätyllä tasolla. Esimerkiksi seuraavassa raportissa näytetään 10 kalleinta indeksiä koon ja pirstaloituneisuuden perusteella SQL-palvelimen instanssitasolla:

Siirryttäessä tiettyyn tietokantaan raportti näyttää automaattisesti 10 kalleinta indeksiä koon ja pirstaloituneisuuden perusteella valitulla tietokantatasolla, kuten alla näkyy:

Kun siirrytään syvälle tiettyyn tauluun kyseisen tietokannan alla, raportti näyttää 10 kalleinta indeksiä koon ja pirstaleisuuden perusteella seuraavasti:

ApexSQL Defrag -raportin vienti

ApexSQL Defragin avulla voimme viedä pirstaloitumisraportit eri tiedostomuotoihin, mukaan lukien PDF-, IMG-, HTML-, CS- ja XML-tiedostomuodot, ja raportin asetuksia voidaan mukauttaa ennen sen luomista. Napsauta Raportit-välilehdellä Asetukset-painiketta:

Muokkaa vientiasetuksia -ikkunassa voit määrittää eri asetuksia, alkaen luodun tiedoston nimeämiskäytännöstä, polusta, johon tiedosto tallennetaan, sivusuuntauksesta ja lopuksi kunkin raportin sisällöstä, kuten alla näkyy:

Jos haluat esimerkiksi viedä luodun Kokonaisraportin määritetyllä tasolla, napsauta Raportit-välilehdellä olevaa Vie-painiketta ja valitse tiedostotyyppi, johon tämä Kokonaisraportti viedään. Muutaman sekunnin kuluttua raportti luodaan ja kysytään, haluatko avata luodun raportin. Napsauttamalla Kyllä voit avata kyseisen raportin suoraan, kuten alla näkyy:

Lisäksi voit viedä Top 10 -raportin määritetyllä tasolla napsauttamalla Raportit-välilehden Vienti-painiketta ja määrittelemällä vientitiedostotyypin, jolloin raportti luodaan nopeasti ja kysytään, haluatko tarkastella luotua raporttia suoraan. Napsauta Kyllä avataksesi luodun, kuten alla näkyy:

Edellisistä esimerkeistä on selvää, että voimme helposti käyttää ApexSQL Defrag -työkalua SQL-indeksin pirstaloitumisprosenttitietojen tarkasteluun ja analysointiin ja sukellukseen eri tasoilla SQL Serverin instanssitasolta SQL Serverin indeksitasolle asti, ja näyttämään ne erilaisissa vientikelpoisissa tiedostomuodoissa ilman, että palvelimen tai palvelun uudelleenkäynnistäminen vaikuttaisi näytettyyn informaatioon, koska ne tallennetaan arkistotietokantaan.