Free Press

Het doel achter het maken van SQL-indexen op databasetabellen is het verbeteren van de prestaties van de query’s die gegevens lezen uit de basistabel door het versnellen van de gegevensopvraagprocessen. Maar deze transactionele tabellen zijn niet statisch en veranderen zeer frequent in de tijd. De wijzigingen die op de databasetabel worden uitgevoerd, omvatten het toevoegen van nieuwe records, het wijzigen of verwijderen van bestaande records. Aangezien deze veranderingen moeten worden gerepliceerd naar de gerelateerde tabel indexen, zal de tabel index na verloop van tijd gefragmenteerd raken.

SQL Server Index Fragmentatie Overzicht

Er zijn twee hoofdtypen van SQL index fragmentatie; Interne en Externe Fragmentatie. Interne Fragmentatie wordt veroorzaakt door het invoegen van een nieuwe record of het bijwerken van een bestaande record met waarden die niet passen in de huidige vrije ruimte in de data pagina en resulteren in het opsplitsen van de pagina in twee pagina’s om de nieuwe waarde te kunnen plaatsen. In dit geval zal de SQL Server Engine ongeveer 50% van de gegevens van de huidige pagina verplaatsen naar de nieuwe gegevenspagina die ontstaat door de splitsing, om de SQL index boom in balans te houden. Interne fragmentatie kan ook worden veroorzaakt door de willekeurige verwijder operaties die resulteren in lege ruimte op de data pagina’s. Dit soort operaties laat de data pagina’s ongevuld en resulteert in een grotere SQL Server index grootte en tragere prestaties als gevolg van de noodzaak om meer data pagina’s te lezen om de gevraagde gegevens op te halen.

De externe fragmentatie wordt veroorzaakt wanneer de data pagina’s van de SQL Server index verspreid zijn over het fysieke bestand van de database, als gevolg van het feit dat de logische volgorde van de SQL Server index niet overeenkomt met de fysieke volgorde op de onderliggende disk drive. Externe fragmentatie kan worden veroorzaakt door willekeurige verwijderoperaties die een aantal van de extent pagina’s leeg laten terwijl de extent zelf is gereserveerd. Dit type fragmentatie resulteert in een verslechtering van de prestaties, omdat het meer tijd kost en meer middelen verbruikt om tussen de verspreide data pagina’s te springen om de gevraagde gegevens op te halen van de onderliggende disk drive.

De reden waarom we ons zorgen maken over de SQL index fragmentatie is dat de index die u hebt gemaakt om de systeemprestaties te verbeteren, de prestaties van uw queries kan verslechteren nadat deze sterk gefragmenteerd is. Stel je het verschil voor in tijd en aantal schijflezingen tussen het lezen van een specifiek aantal rijen van een gezonde SQL-index waarin de gegevens zijn opgeslagen in aaneengesloten gegevenspagina’s binnen het databasebestand, en het lezen van hetzelfde aantal rijen van een sterk gefragmenteerde SQL Server-index, waarin de gegevens zijn verspreid in niet aaneengesloten gegevenspagina’s binnen het databasebestand.

Als wordt vastgesteld dat de tabel SQL-index gefragmenteerd is, moet u het fragmentatieprobleem oplossen door het uitvoeren van SQL Server Index Reorganisatie of Index Rebuild, op basis van het SQL index fragmentatiepercentage. Een SQL Server-index met een fragmentatiepercentage van minder dan 30% kan bijvoorbeeld worden gereorganiseerd, terwijl een SQL-index met een fragmentatieniveau van meer dan 30% moet worden herbouwd.

• Opmerking: Zie SQL Server-indexen onderhouden voor meer informatie.

U kunt problemen met SQL Server-indexfragmentatie en het splitsen van pagina’s proactief ondervangen door de index op de juiste manier te ontwerpen en de opties Fill Factor en Pad_index voor het maken van indexen in te stellen met de juiste waarden.

• Opmerking: Zie SQL Server Index Operations voor meer informatie.

SQL Server Index Fragmentation Report

Het wordt niet beschouwd als een best practice om alleen maar een SQL-index in uw databasetabel aan te maken om te profiteren van de verbeterde gegevensontsluiting en die SQL Server-index voor altijd te laten staan zonder voortdurend de fragmentatie ervan te bewaken en deze te onderhouden.

SQL Server biedt ons verschillende methoden die kunnen worden gebruikt om het SQL index fragmentatie percentage te controleren.

SQL Server Index Eigenschappen

De eerste methode is de Fragmentatie pagina van het SQL Server Index Eigenschappen venster. Klik met de rechter muisknop op de index die u wilt controleren en kies de optie Eigenschappen. Vanuit de SQL Server Index eigenschappen pagina bladert u naar de Fragmentatie pagina, en u zult het index fragmentatie percentage zien en hoeveel de SQL Server index pagina’s vol zijn onder de Fragmentatie sectie, zoals hieronder getoond:

In een paar stappen kunt u eenvoudig het SQL Server index fragmentatie percentage van de geselecteerde index controleren vanuit het index eigenschappen scherm. Maar wat als u een overzicht wilt hebben van het fragmentatie percentage voor alle indexen van alle tabellen of alle databasetabellen indexen? In dat geval moet je het één voor één gaan controleren. Als gevolg hiervan zal een enkele werkweek niet genoeg zijn voor één database rapport!

sys.dm_db_index_physical_stats

Een andere methode die kan worden gebruikt om het fragmentatie percentage voor de database indexen te controleren is het bevragen van de sys.dm_db_index_physical_stats dynamische management functie, die voor het eerst werd geïntroduceerd in SQL Server 2005, als vervanging voor het afgeschreven DBCC SHOWCONTIG commando. Het geeft informatie over de grootte en het fragmentatie percentage voor de database indexen.

Om zinvolle informatie over het fragmentatie percentage van alle indexen onder de gespecificeerde database uit de sys.dm_db_index_physical_stats DMF te krijgen, moet u deze samenvoegen met de sys.indexes DMV, zoals in het onderstaande T-SQL script:

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

Gebaseerd op uw SQL ontwikkel vaardigheden, kunt u de filtratie uitvoeren die u nodig heeft om de SQL index fragmentatie op te halen op het gewenste bereik, zonder optie om een samenvatting te krijgen voor de totale instance databases, rekening houdend met het feit dat de getoonde gegevens sinds de laatste SQL Server service herstart zijn. In ons geval ziet het resultaat er als volgt uit:

SQL Server Index Physical Statistics Standard Report

SQL Server voorziet ons van diverse standaard rapporten die nuttige statistische informatie bevatten over de geselecteerde database. Het Index Physical Statistics rapport, is een van de SQL Server standaard rapporten die statistische informatie retourneert over de SQL Server index partities, het fragmentatie percentage en het aantal pagina’s op elke SQL index partitie op database niveau, sinds de laatste SQL Server service herstart, met aanbevelingen hoe het index fragmentatie probleem te verhelpen, indien bestaat, gebaseerd op het fragmentatie percentage van de SQL Server index.

Om het SQL Server Index Physical Statistics rapport te bekijken, klikt u met de rechtermuisknop op uw database, kiest u Rapporten -> Standaardrapporten en vervolgens selecteert u het Index Physical Statistics rapport, zoals hieronder getoond:

En het gegenereerde rapport op databaseniveau, dat niet kan worden gefilterd en waarvoor elke tabelindex handmatig moet worden uitgebreid, ziet er dan uit zoals hieronder getoond:

ApexSQL Defrag

ApexSQL Defrag tool is een 3e partij SQL index defragmentatie tool die eenvoudig kan worden gebruikt om SQL Server index fragmentatie en gebruiksinformatie te controleren en de juiste fix voor het index fragmentatie probleem uit te voeren of te plannen.

Vanuit één centrale locatie kunt u de fragmentatie en gebruiksinformatie op verschillende niveaus analyseren en bekijken, inclusief de SQL index fragmentatie en gebruiksinformatie op het SQL Server index niveau, het tabel niveau, het database niveau en tenslotte op het algemene SQL Server instance niveau. Het voorziet ons van verschillende soorten filters die kunnen worden gebruikt om alleen uw doel indexen weer te geven en te onderhouden.

ApexSQL Defrag staat ons ook toe om de Fill Factor instelling voor de tabel indexen te configureren, waarschuwingen te creëren voor alle defragmentatie jobs en policies en Email notificatie te sturen voor succesvolle of mislukte jobs.

De getoonde informatie zal een goed beeld geven over de SQL index grootte en het fragmentatie percentage, dat niet zal worden beïnvloed door een SQL Server service herstart of server reboot. Dit komt omdat deze informatie wordt opgeslagen in een centrale repository database voor de ApexSQL Defrag tool.

Installeren van ApexSQL Defrag

ApexSQL Defrag kan eenvoudig worden gedownload van ApexSQL Download Center en op uw server worden geïnstalleerd door de eenvoudige installatie wizard te volgen, zoals hieronder getoond:

Eerst wordt u gevraagd de service account op te geven die als executie context voor de ApexSQL Defrag agent zal worden gebruikt en commando’s naar de SQL Server instance zal sturen, zoals hieronder is aangegeven:

Na het specificeren van de ApexSQL Defrag service account, wordt u gevraagd te specificeren waar de ApexSQL Defrag tool moet worden geïnstalleerd en of er een snelkoppeling op het bureaublad moet worden gemaakt, zoals hieronder is aangegeven:

Als u controleert of het opgegeven pad minimaal 127MB vrije ruimte bevat, zal ApexSQL Defrag succesvol op uw server worden geïnstalleerd. En de volgende melding zal worden getoond, die u de mogelijkheid geeft om ApexSQL Defrag direct te starten:

Als u ApexSQL Defrag voor de eerste keer draait, wordt u gevraagd om de creatie van de centrale repository database, waar deze historische data en configuratie data zullen worden opgeslagen, als volgt te bevestigen:

Add New Server

Na het aanmaken van de repository database, zal ApexSQL Defrag worden gestart zonder SQL instance. Om het SQL index fragmentatie percentage op een specifieke SQL Server instantie te controleren, moet u die instantie aan de tool toevoegen. Klik daartoe op de knop Toevoegen onder het tabblad Home, zoals hieronder weergegeven:

In het venster Verbinden met SQL Server wordt u gevraagd de naam van de SQL Server-instance die zal worden gebruikt en de referenties die nodig zijn om verbinding te maken met die server op te geven, en wel als volgt:

SQL Server Index Analyse

Wanneer de server is toegevoegd, kunt u een nieuwe controle uitvoeren op alle SQL index fragmentatie informatie van het Fragmentatie tabblad hieronder:

ApexSQL Defrag biedt u de mogelijkheid om alle indexen op die instantie te analyseren op basis van drie modi:

• DETAILED: waarin alle data en SQL index pagina’s worden gelezen tijdens het scan proces
• SAMPLED: waarin slechts 1% van de pagina’s worden gelezen, rekening houdend met het feit dat de SQL Server index meer dan 10.000 pagina’s bevat
• LIMITED: waarin alleen de pagina’s die zich op het bovenliggende niveau van de b-tree bevinden, zullen worden gelezen

Om SQL Server index informatie van de aangesloten SQL Server instance te analyseren, klikt u op de knop Analyseren, onder het tabblad Fragmentatie, en kiest u om een Snelle of een Diepe analyse uit te voeren. We zullen een diepe analyse uitvoeren om volledige informatie te verkrijgen, zoals hieronder getoond:

Blijf de voortgangsbalk aan de onderkant van de tool in de gaten houden om de voortgang van de analyse te controleren, zoals hieronder getoond:

En een uitgebreid rapport, dat volledige informatie bevat over alle SQL-indexen die onder elke database van deze SQL Server-instantie zijn gemaakt, zal worden getoond zoals hieronder getoond:

Met de mogelijkheid om te duiken tussen de verschillende niveaus; SQL Server instantie, database, tabel of index, met een enkele klik onder hetzelfde centrale venster, en filter het opgehaalde resultaat op basis van een specifiek fragmentatie niveau of drempel, zoals hieronder getoond:

Bovendien kunt u elke kolom uit het rapport slepen om het totale rapport te groeperen op basis van die kolom waarden, zoals het voorbeeld hieronder, waarin we de rapport data hebben gegroepeerd op basis van de Tabel naam:

ApexSQL Defrag Rapporten

ApexSQL Defrag voorziet ons van een aantal rapporten die statistische informatie over de database indexen laat zien. Deze rapporten kunnen worden geconfigureerd en gecontroleerd onder de Rapporten tab hieronder:

Het eerste rapport is het Totaal rapport, dat een grafische weergave laat zien voor SQL index fragmentatie en schijfruimte gebruik op het geselecteerde niveau. Als u bijvoorbeeld op de naam van een instantie klikt, worden in het rapport de grafieken voor het fragmentatiepercentage en het ruimtegebruik op het niveau van de SQL Server-instantie weergegeven, zoals hieronder:

Als u in het venster Server Explorer op een database klikt, wordt de weergave automatisch gewijzigd in een grafiek op databaseniveau, zoals hieronder wordt weergegeven:

Het tweede rapport, dat ook op verschillende niveaus kan worden bekeken, is het Statistieken-rapport. Dit rapport toont statistische informatie over de totale grootte en fragmentatie van de indexen en de verdeling van de indextypen op het geselecteerde niveau, zoals hieronder getoond:

Het derde rapport is het Top 10-rapport, dat de top 10 geclusterde en niet-geclusterde indexen weergeeft met de grootste grootte en het hoogste percentage SQL-indexfragmentatie, op het gespecificeerde niveau. Het volgende rapport toont bijvoorbeeld de top 10 dure indexen, gebaseerd op de grootte en fragmentatie, op het niveau van de SQL-serverinstantie:

Als u naar een specifieke database gaat, toont het rapport automatisch de top 10 dure indexen, gebaseerd op de grootte en fragmentatie, op het geselecteerde databaseniveau, zoals hieronder getoond:

Als u diep naar een specifieke tabel onder die database duikt, toont het rapport de top 10 dure indexen, gebaseerd op de grootte en de fragmentatie, zoals hieronder getoond:

ApexSQL Defrag Rapport Export

ApexSQL Defrag stelt ons in staat om de fragmentatie rapporten te exporteren naar verschillende bestandsformaten, waaronder PDF, IMG, HTML, CS, en XML, met de mogelijkheid om de rapport instellingen aan te passen voordat het wordt gegenereerd. Klik onder het tabblad Rapporten op de knop Opties:

In het venster Exportopties bewerken kunt u de verschillende opties configureren, te beginnen met de naamgevingsconventie van het gegenereerde bestand, het pad waar het bestand wordt opgeslagen, de paginastand en ten slotte de inhoud van elk rapport, zoals hieronder wordt weergegeven:

Om bijvoorbeeld het gegenereerde Totaalrapport op het gespecificeerde niveau te exporteren, klikt u op de knop Exporteren, onder het tabblad Rapporten, en kiest u het bestandstype waarnaar dit Totaalrapport zal worden geëxporteerd. Binnen enkele seconden wordt het rapport gegenereerd, en wordt u gevraagd of u het gegenereerde rapport wilt openen. Klik op Ja om het rapport direct te openen, zoals hieronder getoond:

Bovendien kunt u het Top 10 rapport op het gespecificeerde niveau exporteren door op de knop Exporteren te klikken, onder het tabblad Rapporten, en het exportbestandstype te specificeren, waarna het rapport snel wordt gegenereerd en u wordt gevraagd of u het gegenereerde rapport direct wilt bekijken. Klik op Ja om de gegenereerde te openen, zoals hieronder getoond:

Het is duidelijk uit de voorgaande voorbeelden, dat we ApexSQL Defrag tool gemakkelijk kunnen gebruiken om de SQL index fragmentatie percentage informatie te bekijken en te analyseren en te duiken op de verschillende niveaus vanaf het SQL Server instance niveau tot aan het SQL Server index niveau en deze weer te geven in verschillende exporteerbare formaten, zonder dat de weergegeven informatie wordt beïnvloed door een server of service herstart, omdat ze worden opgeslagen in een repository database.