By: Brady Upton | Updated: 2016-06-22 | Comments (5) | Related: More > Fragmentation and Index Maintenance
Problem
we ’ve all heard about database/index fragmentation (and if you haven’ t, continuereading), but what is it? Onko se ongelma? Mistä tiedän, onko se tietokannassani?Miten korjaan sen? Nämä kysymykset voivat olla vihje sinänsä, mutta yritän antaa sinulle käsityksen jokaisesta tässä vinkissä.
ratkaisu
menemättä yksityiskohtiin, SQL Server tallentaa tiedot 8KB: n sivuille. Whenwe insert data into a table, SQL Server jakaa yhden sivun tallentaa, että datauntless lisätty data on yli 8KB, jossa se kattaisi useita sivuja.Jokainen sivu on liitetty yhteen taulukkoon. Jos luomme 10 pöytää, meillä on 10 eri sivua.
kun syötät tietoja taulukkoon, tiedot siirtyvät ensin tapahtumalokitiedostoon. Transaction lokitiedosto on peräkkäinen ennätys merkitys Kun lisäät, päivittää ja poistaa tietueita loki tallentaa nämä tapahtumat alusta loppuun. Toisaalta tiedot eivät ole riittäviä. Lokitiedosto huuhtoo tiedot datatiedoston luomiseen sivut allover paikka.
nyt kun meillä on käsitys siitä, miten tietoja tallennetaan, mitä tekemistä tällä on fragmentoinnin kanssa?
on olemassa kahdenlaisia fragmentaatioita: sisäinen fragmentaatio ja ulkoinen fragmentaatio.
SQL Server Internal Fragmentation
SQL Server Internal Fragmentation johtuu sivuista, joilla on liikaa vapaata tilaa.Teeskennellään päivän alussa, että meillä on taulukko, jossa on 40 sivua, jotka ovat100% täynnä, mutta päivän loppuun mennessä meillä on taulukko, jossa on 50 sivua, jotka ovat vain 80% täynnä, koska eri poistaa ja lisätä lausuntoja koko päivän.Tämä aiheuttaa ongelman, koska nyt kun meidän on luettava tästä taulukosta, meillä on toscan 50 sivua 40: n sijasta, mikä voi johtaa suorituskyvyn heikkenemiseen.Katsotaanpa nopea ja likainen esimerkki.
sanotaan, että minulla on seuraava taulukko, jossa on ensisijainen avain ja etu-ja sukunimen ei-ryhmitelty indeksi:
puhun tavoista analysoida pirstoutumista myöhemmin tässä vinkissä, mutta nyt voimme napsauttaa hiiren oikealla painikkeella indeksiä, napsauttamalla ominaisuuksia ja pirstoutumista nähdäksesi fragmentaation ja sivun täyteläisyyden. Tämä on upouusi indeksi, joten se on 0% fragmentaatio.
lisätään 1000 riviä tähän taulukkoon:
INSERT INTO Person VALUES('Brady', 'Upton', '123 Main Street', 'TN', 55555)GO 1000
nyt tarkistetaan indeksimme uudelleen:
näet, että indeksimme tulee 75% hajanaiseksi ja keskimääräinen prosentti kokosivuista (sivun täyteläisyys) kasvaa 80%: iin. Tämä taulukko on vielä niin pieni, että 75%: n fragmentaatio ei todennäköisesti aiheuta suorituskykyyn liittyviä ongelmia, mutta kun taulukon koko ja sivumäärä kasvavat, saatat nähdä suorituskyvyn heikkenevän. Voit myös nähdä yllä olevasta kuvasta, että tämä taulukko meni 0 sivusta 4: ään.
SQL Server External Fragmentation
External Fragmentation johtuu sivuista, jotka ovat epäkunnossa. Let ’ spretend at the beginning of the day we have a perfectly ordered table. Päivän aikana julkaisemme satoja päivitysilmoituksia, jotka mahdollisesti jättävät jonkin verran tyhjää tilaa yhdelle sivulle ja yrittävät sovittaa tilaa muille sivuille. Tämä tarkoittaa, että tallennustilan on hypittävä ympäri saadakseen tarvittavat tiedot yhteen suuntaan lukemisen sijaan.
analysoimalla SQL Server Fragmentation
joten onko fragmentaatio ongelma? Uskon niin. Jos voit tallentaa koko tietokannan muistiin tai jos tietopankki on luettu vain silloin en olisi huolissani siitä, mutta useimmilla meistä ei ole sitä ylellisyyttä.Olen työskennellyt tuhansilla palvelimilla ja pirstoutumistasojen analysointi on yksi ensimmäisistä asioista, joita katson. Itse asiassa, vain korjaamalla pirstoutuminen, olen nähnyt jopa 200% parannuksia kyselyn suorituskykyä.
pirstoutumistasojen analysoinnista puhuttaessa saatat ihmetellä, miten voimme tehdä tämän. No, on olemassa muutamia tapoja…
DBCC SHOWCONTIG – tämä ominaisuus on vanha ja poistetaan tulevissa SQL Server-versioissa, mutta jos käytät edelleen SQL Server 2000: ta tai sitä alempaa, tämä auttaa. Ennen kuin kirjoitan siitä, osoitan sinut tänne tai voit tarkistaa Chad Boydin tipheren. Molemmat ovat hyviä voimavaroja.
sys.dm_db_index_physical_stats – Introduced in SQL Server 2005, this dynamic managementview (DMV) returns size and fragmentation information for the data and indexes of specified table or view.
SELECT OBJECT_NAME(ips.OBJECT_ID) ,i.NAME ,ips.index_id ,index_type_desc ,avg_fragmentation_in_percent ,avg_page_space_used_in_percent ,page_countFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ipsINNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)ORDER BY avg_fragmentation_in_percent DESC
tämä lienee laajimmin käytetty fragmentaation analysointimenetelmä. Näet yllä olevasta kuvakaappauksesta, että minulla on indeksi nimeltä IX_RPCust_1 theRPCust-taulukossa, joka on 98.83% hajanainen. Lisätietoja tästä ajoneuvorekisteristä saa Arshad Alilta.
kolmannen osapuolen ohjelmisto – kolmannen osapuolen ohjelmisto auttaa tunnistamaan fragmentationso sinun ei tarvitse. Greg Robidoux tarjoaa hyvän vinkin Ideran SQL Defrag Manageriin täällä.
Fixing SQL Server Fragmentation
Now that we have found fragmentation in the database, how do we fix it? Kuten analysointi indeksit, on olemassa useita tapoja.
SQL Server Maintenance plans – Huoltosuunnitelmat toimitetaan sqlserverin mukana ja ovat mukavia joihinkin tehtäviin. Indeksin ylläpito ei mielestäni ole yksi niistä. Voit lisätä rebuild index-tehtävän tai järjestää indeksitehtävän uudelleen maintenanceplaniin, mutta ongelmana on, että et voi todella soveltaa mitään logiikkaa suunnitelmaan. Withoutlogic, suunnitelma rakentaa / uudelleen jokaisen indeksin. Jotkin indeksit eivät ehkä ole hajanaisia, joten niitä ei tarvitse rakentaa uudelleen. Jotkut indeksit voivat olla vähintään fragmentationso ne olisivat kunnossa uudelleenjärjestely. Tätä ei voi määritellä huoltosuunnitelmassa. InSQL Server 2016 pirstoutumisen hallintaan on uusia vaihtoehtoja.
Custom Script – Custom script-Custom script ovat luultavasti tapa mennä, koska voit soveltaa logiikkaa tarkistaa indeksin fragmentaatio ja sitten päättää, skip the index, uudelleenjärjestely, tai rakentaa uudelleen. Käytän hallengrenin käsikirjoituksia ja he tekevät juuri niin kuin tarvitsen. MSSQLTips.com on myös nämä lähteet-SQL Server pirstoutuminen ja indeksin ylläpito vinkkejä.
kolmannen osapuolen ohjelmisto – kolmannen osapuolen ohjelmisto auttaa eheyttämään indeksiä niin kuin sinun ei tarvitse.
seuraavat vaiheet
- tästä vinkistä saa käsityksen muutamista indeksin eheytymiseen osallistuvista komponenteista. MSSQLTips.com tarjoaa kattavampia vinkkejä, joissa keskitytään kuhunkin täällä mainitsemaani aiheeseen.
- muista tarkistaa check outChad Boydin SQL Server Fragmentation tutorial, joka tarjoaa suuren oivalluksen lukuisiin aiheisiin liittyen sirpaloitumiseen
Viimeksi päivitetty: 2016-06-22
About the author
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.View all my tips
- More SQL Server DBA Tips…