By: Brady Upton | Updated: 2016-06-22 | Comments (5) | Related: More > Fragmentation and Index Maintenance
probleem
we hebben allemaal gehoord over database/index fragmentation (en als je dat niet hebt gedaan, continuereading), maar wat is het? Is het een probleem? Hoe Weet ik of het zich in mijn database bevindt?Hoe los ik het op? Deze vragen kunnen al een tip op zich, maar Ik zal proberen om u een idee van elk in deze tip.
oplossing
zonder in detail te treden, slaat SQL Server gegevens op op 8KB-pagina ‘ s. Wanneer we gegevens invoegen in een tabel, zal SQL Server een pagina toewijzen om die gegevens op te slaan, tenzij de ingevoegde gegevens meer dan 8KB zijn waarin het meerdere pagina ‘ s zou omvatten.Elke pagina wordt toegewezen aan één tabel. Als we 10 tabellen maken dan hebben we 10 verschillende pagina ‘ s.
Als u gegevens in een tabel invoegt, gaan de gegevens eerst naar het transactielogboekbestand. Het transactielogboekbestand is een sequentiële record, wat betekent dat als u records invoegt, bijwerkt en verwijdert, het logboek deze transacties van begin tot eind registreert. Het gegevensbestand daarentegen is nietvolgend. Het logbestand zal de gegevens te spoelen naar het gegevensbestand maken van pagina ‘ s allover de plaats.
nu we een idee hebben van hoe gegevens worden opgeslagen, wat heeft dit te maken met fragmentatie?
Er zijn twee soorten fragmentatie: interne fragmentatie en externe fragmentatie.
interne fragmentatie van SQL Server
interne fragmentatie van SQL Server wordt veroorzaakt door pagina ‘ s die te veel vrije ruimte hebben.Laten we aan het begin van de dag doen alsof we een tabel hebben met 40 pagina ’s die 100% vol zijn, maar aan het einde van de dag hebben we een tabel met 50 pagina’ s die slechts 80% vol zijn vanwege verschillende Delete en insert statements gedurende de dag.Dit veroorzaakt een probleem, want nu als we moeten lezen uit deze tabel hebben we Toscaanse 50 pagina ‘ s in plaats van 40 die zou kunnen leiden tot een daling van de prestaties.Laten we een snel en vies voorbeeld zien.
stel dat ik de volgende tabel heb met een primaire sleutel en een niet-geclusterde index op Voornaam en achternaam:
Ik zal het later in deze tip hebben over manieren om fragmentatie te analyseren, maar voor nu kunnen we met de rechtermuisknop op de index klikken, op Eigenschappen en fragmentatie klikken om fragmentatie en paginavolheid te zien. Dit is een gloednieuwe index dus het is op 0% fragmentatie.
laten we 1000 rijen in deze tabel invoegen:
INSERT INTO Person VALUES('Brady', 'Upton', '123 Main Street', 'TN', 55555)GO 1000
nu controleren we onze index opnieuw:
u kunt zien dat onze index 75% gefragmenteerd wordt en dat het gemiddelde percentage vol pagina ’s (volheid van pagina’ s) toeneemt tot 80%. Deze tabel is nog steeds zo klein dat 75% fragmentatie waarschijnlijk geen prestatieproblemen zou veroorzaken, maar als de tabel groter wordt en de paginatellingen toenemen, kunt u de prestaties zien verslechteren. U kunt ook zien uit de screenshot hierboven dat deze tabel ging van 0 pagina ‘ s naar 4.
externe fragmentatie van SQL Server
externe fragmentatie wordt veroorzaakt door pagina ‘ s die niet op volgorde staan. Aan het begin van de dag hebben we een perfect geordende tafel. Tijdens de dag geven we honderden updateverklaringen mogelijk verlaten wat lege ruimte op een pagina en proberen om ruimte te passen in andere pagina ‘ s. Dit betekent dat onze opslag moet springen rond om de gegevens die nodig zijn in plaats van het lezen in een richting te verkrijgen.
analyseren van SQL Server fragmentatie
is fragmentatie een probleem? Ik geloof van wel. Als je je hele database in het geheugen kunt opslaan of als je Database alleen wordt gelezen, dan zou ik me er geen zorgen over maken, maar de meesten van ons hebben die luxe niet.Ik heb gewerkt aan duizenden servers en het analyseren van fragmentatieniveaus is een van de eerste dingen waar ik naar kijk. In feite, gewoon door de vaststelling van fragmentatie, ik heb gezien tot200% verbeteringen in query prestaties.
sprekend over het analyseren van fragmentatieniveaus vraagt u zich misschien af hoe we dit kunnen doen. Nou, er zijn een paar manieren…
DBCC SHOWCONTIG – deze functie is oud en zal worden verwijderd in toekomstige versies van SQL Server, maar als je nog steeds SQL Server 2000 of lager gebruikt, zal dit helpen. In plaats van erover te schrijven, wijs ik je hier of je kunt Chad Boyd ‘ s tip hier bekijken. Beide zijn goede middelen.
sys.dm_db_index_physical_stats – deze dynamic managementview (DMV) is geà ntroduceerd in SQL Server 2005 en geeft informatie over grootte en fragmentatie terug voor de gegevens en indexen van de opgegeven tabel of weergave.
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
Dit is waarschijnlijk de meest gebruikte methode om fragmentatie te analyseren. U kunt zien uit de screenshot hierboven dat ik een index met de naam IX_RPCust_1 op de pcust tabel die 98.83% gefragmenteerd. U kunt meer informatie over deze Dmvhere zien van Arshad Ali.
software van derden – software van derden helpt fragmentatie te identificeren, zodat u dat niet hoeft te doen. Greg Robidoux geeft hier een goede tip over Idera ‘ s SQL Defrag Manager.
fixing SQL Server Fragmentation
nu we fragmentatie in de database hebben gevonden, hoe lossen we dit op? Zoals het analyseren van indexen, zijn er meerdere manieren.
SQL Server onderhoudsplannen-onderhoudsplannen worden meegeleverd met SQLServer en zijn geschikt voor sommige taken. Index onderhoud is naar mijn mening niet een van hen. U kunt een rebuild index task toevoegen of reorganiseren index taak in het maintenanceplan, maar het probleem is dat je niet echt enige logica toe te passen op het plan. Zonder logic zal het plan elke index herbouwen/reorganiseren. Sommige indexen zijn mogelijk niet gefragmenteerd, zodat ze niet opnieuw hoeven te worden opgebouwd. Sommige indexen kunnen een minimale fragmentatie hebben, zodat ze prima zouden zijn met een reorganisatie. Er is geen manier om dit te specificeren in een onderhoudsplan. InSQL Server 2016 Er zijn nieuwe opties voor het beheren van fragmentatie.
aangepast Script-aangepaste scripts zijn waarschijnlijk de manier om te gaan, omdat je logica kunt toepassen om index fragmentatie te controleren en dan te beslissen of je de index wilt overslaan, reorganiseren of opnieuw opbouwen. Ik gebruik hallengren ‘ s scripts en ze doen precies wat ik wil dat ze doen. MSSQLTips.com heeft ook deseresources-SQL Server fragmentatie en Index onderhoud Tips.
software van derden – software van derden zal helpen bij het defragmenteren van indexesso dat u niet hoeft te doen.
volgende stappen
- Deze tip geeft u een idee van enkele van de componenten die betrokken zijn bij indexfragmentatie. MSSQLTips.com biedt uitgebreidere tips gericht op elk van de onderwerpen die ik hier heb genoemd.
- check Outchad Boyd ‘ s SQL Server Fragmentation tutorial die veel inzicht biedt in tal van onderwerpen met betrekking tot fragmentatie
laatst bijgewerkt: 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…