av: Brady Upton / uppdaterad: 2016-06-22 / kommentarer (5)/relaterade: mer > fragmentering och Index underhåll
Problem
Vi har alla hört talas om Databas / index fragmentering (och om du inte har, continuereading), men vad är det? Är det ett problem? Hur vet jag om den finns i min databas?Hur fixar jag det? Dessa frågor kan vara ett tips i sig, men jag ska försöka ge dig en uppfattning om var och en i detta tips.
lösning
utan att gå in i mycket detaljer lagrar SQL Server data på 8KB-sidor. När vi sätter in data i en tabell, kommer SQL Server att allokera en sida för att lagra den dataunless data infogas är mer än 8KB där det skulle sträcka sig över flera sidor.Varje sida tilldelas en tabell. Om vi skapar 10 tabeller har vi 10 olikasidor.
När du infogar data i en tabell går data först till transaktionsloggfilen. Thetransaction loggfilen är en sekventiell post betydelse som du infogar, uppdatera och ta bort postloggen kommer att spela in dessa transaktioner från början till slut. Datafilen å andra sidan är intesekventiell. Loggfilen kommer att spola data till datafilen skapa sidor allover platsen.
Nu när vi har en uppfattning om hur data lagras, vad har detta att göra medfragmentering?
det finns två typer av fragmentering: intern fragmentering och extern fragmentering.
SQL Server intern fragmentering
SQL Server intern fragmentering orsakas av sidor som har för mycket ledigt utrymme.Låt oss låtsas i början av dagen har vi en tabell med 40 sidor som är100% full, men i slutet av dagen har vi en tabell med 50 sidor som bara är80% full på grund av olika radera och infoga uttalanden under hela dagen.Detta orsakar ett problem för nu när vi behöver läsa från den här tabellen måste Viskan 50 sidor istället för 40 vilket kan leda till en minskning av prestanda.Låt oss se ett snabbt och smutsigt exempel.
låt oss säga att jag har följande tabell med en primärnyckel och ett icke-grupperat index på Förnamn och efternamn:
jag pratar om sätt att analysera fragmentering senare i det här tipset, men för Nuvi kan högerklicka på indexet, klicka på Egenskaper och fragmentering för att se fragmenteringoch sidans fullhet. Detta är ett helt nytt index så det är vid 0% fragmentering.
låt oss infoga 1000 rader i denna tabell:
INSERT INTO Person VALUES('Brady', 'Upton', '123 Main Street', 'TN', 55555)GO 1000
nu, låt oss kolla vårt index igen:
Du kan se att vårt index blir 75% fragmenterat och den genomsnittliga procenten av fullsidor (sidans fullhet) ökar till 80%. Denna tabell är fortfarande så liten att 75% fragmenteringskulle förmodligen inte orsaka några prestandaproblem, men eftersom tabellen ökar i storlek och sidantal ökar kan du se prestanda försämras. Du kan också se frånskärmbilden ovan att denna tabell gick från 0 sidor till 4.
SQL Server extern fragmentering
extern fragmentering orsakas av sidor som inte är i ordning. Låt ossstäng i början av dagen har vi ett perfekt beställt bord. Under dagen utfärdar vi hundratals uppdateringsuttalanden som eventuellt lämnar tomt utrymme på en sida och försöker passa utrymme på andra sidor. Det betyder att vår lagring måste hoppa runt för att få de data som behövs istället för att läsa i en riktning.
analysera SQL Server fragmentering
så är fragmentering ett problem? Jag tror att det är det. Om du kan lagra hela databasen i minnet eller om din databasär bara läst då skulle jag inte oroa mig för det, men de flesta av oss har inte den lyxen.Jag har arbetat på tusentals servrar och analysera fragmentering nivåer är en av de första sakerna jag tittar på. Faktum är att bara genom att fixa fragmentering har jag sett upp till200% förbättringar i frågans prestanda.
På tal om att analysera fragmenteringsnivåer kanske du undrar hur vi kan göraDetta. Tja, det finns några sätt…
DBCC SHOWCONTIG-den här funktionen är gammal och kommer att tas bort i framtida versionerav SQL Server, men om du fortfarande använder SQL Server 2000 eller senare, kommer det att hjälpa. I stället för att skriva om det, pekar jag på dig här eller så kan du kolla in Chad Boyds tiphere. Båda är bra resurser.
sys. dm_db_index_physical_stats-introducerad i SQL Server 2005 returnerar denna dynamiska managementview (DMV) storlek och fragmenteringsinformation för data och index för den angivna tabellen eller vyn.
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
detta är förmodligen den mest använda metoden för att analysera fragmentering. Du kan se från skärmdumpen ovan att jag har ett index som heter ix_rpcust_1 på theRPCust-tabellen som är 98.83% fragmenterad. Du kan se mer information om denna DMVherefrom Arshad Ali.
programvara från tredje part-programvara från tredje part hjälper till att identifiera fragmentationso du behöver inte. Greg Robidoux erbjuder ett bra tips om ideras SQL Defrag Managerhär.
Fixing SQL Server fragmentering
nu när vi har hittat fragmentering i databasen, Hur fixar vi det? Somanalysera index, det finns flera sätt.
SQL Server Maintenance plans-underhållsplaner levereras med SQLServer och är trevliga för vissa uppgifter. Indexunderhåll enligt min mening är inte en avdem. Du kan lägga till en ombyggnadsindexaktivitet eller omorganisera indexaktivitet i underhållsplanen, men problemet är att du inte riktigt kan tillämpa någon logik på planen. Utanlogic kommer planen att bygga om / omorganisera varje index. Vissa index kanske inte är fragmenterade så att de inte behöver byggas om. Vissa index kan ha minsta fragmentationso de skulle vara bra med en omorganisera. Det finns inget sätt att ange detta i en underhållsplan. InSQL Server 2016 Det finns nya alternativ för hantering av fragmentering.
Custom Script-anpassade skript är förmodligen vägen att gå eftersom du kan använda logik för att kontrollera indexfragmentering och sedan bestämma om du ska hoppa över index, omorganisera eller bygga om. Jag använderola Hallengrens manus och de gör precis vad jag behöver dem att göra. MSSQLTips.com har också dessaresources-SQL Server fragmentering och Index underhåll Tips.
programvara från tredje part-programvara från tredje part hjälper till att defragmentera indexså du behöver inte.
nästa steg
- detta tips ger dig en uppfattning om några av de komponenter som är involverade i indexfragmentering. MSSQLTips.com erbjuder mer omfattande tips fokuserade på varjeav de ämnen jag nämnde här.
- se till att kolla Inchad Boyds SQL Server fragmentering handledning som erbjuder bra insightinto många ämnen om fragmentering
Senast uppdaterad: 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…