Free Press

Syftet med att skapa SQL-index på databastabeller är att förbättra prestandan hos de frågor som läser data från grundtabellen genom att påskynda datahämtningen. Men dessa transaktionstabeller är inte statiska och ändras mycket ofta över tiden. De ändringar som utförs i databastabellen är bland annat att lägga till nya poster, ändra eller ta bort befintliga poster. Eftersom dessa ändringar bör replikeras till de relaterade tabellindexen kommer tabellindexen att fragmenteras med tiden.

SQL Server Index Fragmentation Overview

Det finns två huvudtyper av SQL-indexfragmentering; intern och extern fragmentering. Intern fragmentering orsakas av att en ny post infogas eller en befintlig post uppdateras med värden som inte ryms i det aktuella lediga utrymmet på datasidan och resulterar i att sidan delas upp i två sidor för att få plats med det nya värdet. I det här fallet flyttar SQL Server-motorn cirka 50 % av data från den aktuella sidan till den nya datasidan som genereras från delningen för att hålla SQL-indexträdet balanserat. Intern fragmentering kan också orsakas av slumpmässiga raderingsoperationer som resulterar i tomt utrymme på datasidorna. Denna typ av operationer gör att datasidorna inte fylls ut och resulterar i större SQL Server-indexstorlek och långsammare prestanda på grund av behovet av att läsa fler datasidor för att hämta de begärda uppgifterna.

Extern fragmentering orsakas när datasidorna i SQL Server-indexet är utspridda i databasens fysiska fil på grund av att den logiska ordningen i SQL Server-indexet inte stämmer överens med den fysiska ordningen på den underliggande diskettenheten. Extern fragmentering kan orsakas av slumpmässiga raderingsoperationer som lämnar ett antal av utbredningssidorna tomma medan själva utbredningen är reserverad. Denna typ av fragmentering leder till försämrad prestanda, eftersom det tar längre tid och förbrukar mer resurser att hoppa mellan de utspridda datasidorna för att hämta de begärda uppgifterna från den underliggande hårddisken.

Anledningen till att vi bryr oss så mycket om SQL-indexfragmentering är att indexet som du har skapat för att förbättra systemets prestanda kan försämra prestandan för dina frågor efter att ha blivit mycket fragmenterat. Föreställ dig skillnaden i tid och antal diskläsningar mellan att läsa ett visst antal rader från ett friskt SQL-index där data lagras i sammanhängande datasidor i databasfilen, och att läsa samma antal rader från ett starkt fragmenterat SQL Server-index, där data är utspridda i icke sammanhängande datasidor i databasfilen.

Om det konstateras att SQL-indexet för tabellen är fragmenterat måste du åtgärda fragmenteringsproblemet genom att utföra SQL Server Index Reorganization eller Index Rebuild, baserat på SQL-indexets fragmenteringsgrad. Till exempel kan ett SQL Server-index med en fragmenteringsprocent på mindre än 30 % omorganiseras, medan SQL-index med en fragmenteringsnivå högre än 30 % bör byggas om.

• Obs: Se Underhåll av SQL Server-index för mer information.

Du kan proaktivt lösa problem med fragmentering av SQL Server-index och uppdelning av sidor genom att utforma indexet på rätt sätt och ställa in alternativen för skapande av indexen Fill Factor och pad_index med propervalues.

• Obs: Se SQL Server Index Operations för mer information.

SQL Server Index Fragmentation Report

Det anses inte vara bästa praxis att bara skapa ett SQL-index i din databastabell för att dra nytta av förbättringen av datahämtningsoperationen och lämna det SQL Server-indexet för alltid utan att kontinuerligt övervaka dess fragmentering och underhålla det.

SQL Server ger oss flera metoder som kan användas för att kontrollera fragmenteringsprocenten för SQL-indexet.

SQL Server Index Properties

Den första metoden är Fragmentationssidan i fönstret SQL Server Index Properties. Högerklicka på det index som du vill kontrollera och välj alternativet Egenskaper. På sidan Egenskaper för SQL Server-index bläddrar du till sidan Fragmentering och ser indexfragmenteringsprocenten och hur mycket SQL Server-indexsidorna är fulla under avsnittet Fragmentering, som visas nedan:

I några få steg kan du enkelt kontrollera SQL Server-indexfragmenteringsprocenten för det valda indexet från fönstret för indexegenskaper. Men vad händer om du vill ha en översikt över fragmenteringsprocenten för alla tabellernas index eller alla databastabellernas index? I det här fallet måste du gå och kontrollera det en efter en. En enda arbetsvecka räcker alltså inte till för en databasrapport!

sys.dm_db_index_physical_stats

En annan metod som kan användas för att kontrollera fragmenteringsprocenten för databasindexen är att fråga den dynamiska hanteringsfunktionen sys.dm_db_index_physical_stats som introducerades för första gången i SQL Server 2005, som en ersättning för det föråldrade kommandot DBCC SHOWCONTIG. Det ger information om storleken och fragmenteringsprocenten för databasens index.

För att få meningsfull information om fragmenteringsprocenten för alla index under den angivna databasen från sys.dm_db_index_physical_stats DMF måste du förena den med sys.indexes DMV, som i T-SQL-skriptet nedan:

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

Baserat på dina SQL-utvecklingsfärdigheter kan du utföra den filtrering du behöver för att hämta fragmenteringen av SQL-indexen i det önskade tillämpningsområdet, utan möjlighet att få en sammanfattning för de totala instansdatabaserna, med hänsyn till att de visade uppgifterna är från den senaste omstarten av SQL Server-tjänsten. Resultatet i vårt fall blir som följer:

SQL Server Index Physical Statistics Standard Report

SQL Server ger oss flera standardrapporter som innehåller användbar statistisk information om den valda databasen. Rapporten Index Physical Statistics är en av SQL Server-standardrapporterna som returnerar statistisk information om SQL Server-indexpartitioner, fragmenteringsprocent och antalet sidor på varje SQL-indexpartition på databasnivå sedan den senaste omstarten av SQL Server-tjänsten, med rekommendationer om hur problemet med indexfragmentering ska åtgärdas, om det finns, baserat på fragmenteringsprocenten för SQL Server-indexet.

För att visa rapporten SQL Server Index Physical Statistics högerklickar du på din databas, väljer Reports -> Standard Reports och väljer sedan rapporten Index Physical Statistics, som visas nedan:

Och den genererade rapporten på databasnivå, som inte kan filtreras och som behöver expanderas manuellt för varje tabellindexresultat, kommer att se ut som nedan:

ApexSQL Defrag

ApexSQL Defrag är ett tredjepartsverktyg för defragmentering av SQL-index som enkelt kan användas för att kontrollera SQL Server-indexfragmentering och information om användning och utföra eller schemalägga lämplig korrigering av indexfragmenteringsproblemet.

Från en central plats kan du analysera och granska fragmentering och användningsinformation på olika nivåer, inklusive SQL-indexfragmentering och användningsinformation på SQL Server-indexnivå, tabellnivå, databasnivå och slutligen på övergripande SQL Server-instansnivå. Det ger oss olika typer av filter som kan användas för att visa och underhålla endast dina målindex.

ApexSQL Defrag låter oss också konfigurera inställningen Fill Factor för tabellindexen, skapa varningar för alla defragmenteringsjobb och policyer och skicka e-postmeddelanden för lyckade eller misslyckade jobb.

Den visade informationen kommer att ge en bra överblick över SQL-indexstorleken och fragmenteringsprocenten, som inte kommer att påverkas av någon omstart av SQL Server-tjänsten eller omstart av servern. Detta beror på att denna information kommer att lagras i en central databas för ApexSQL Defrag-verktyget.

Installation av ApexSQL Defrag

ApexSQL Defrag kan enkelt laddas ner från ApexSQL Download Center och installeras på din server genom att följa den enkla installationsguiden, som visas nedan:

Först ombeds du att ange det tjänstekonto som kommer att användas som exekveringskontext för ApexSQL Defrag-agenten och som överför kommandon till SQL Server-instansen, enligt följande:

När du har angett tjänstekontot för ApexSQL Defrag ombeds du ange var du ska installera verktyget ApexSQL Defrag och om du ska skapa en genvägsikon för verktyget på skrivbordet, enligt nedan:

När du har kontrollerat att sökvägen som du har angett innehåller minst 127 MB ledigt utrymme kommer ApexSQL Defrag att installeras framgångsrikt på din server. Följande meddelande visas och ger dig möjlighet att starta ApexSQL Defrag direkt:

Om du kör ApexSQL Defrag för första gången kommer du att bli ombedd att bekräfta skapandet av den centrala arkivdatabasen, där dessa historiska data och konfigurationsdata kommer att lagras, enligt följande:

Add New Server

När arkivdatabasen har skapats startas ApexSQL Defrag utan någon SQL-instans. Om du vill kontrollera procentandelen för fragmentering av SQL-index på en specifik SQL Server-instans måste du lägga till den instansen i verktyget. För att göra det klickar du på knappen Lägg till under fliken Hem, som visas nedan:

I fönstret Anslut till SQL Server ombeds du att ange namnet på den SQL Server-instans som kommer att användas och de autentiseringsuppgifter som krävs för att ansluta till den servern, enligt följande:

SQL Server Index Analysis

När servern har lagts till kan du utföra en ny kontroll av all SQL-indexfragmenteringsinformation från fliken Fragmentering nedan:

ApexSQL Defrag ger dig möjlighet att analysera alla index på den instansen baserat på tre lägen:

• DETALJERAD: där alla data- och SQL-indexsidor kommer att läsas under skanningsprocessen
• SAMPLED: där endast 1 % av sidorna kommer att läsas, med hänsyn till att SQL Server-indexet innehåller mer än 10 000 sidor
• LIMITED: Om du vill analysera SQL Server-indexinformation från den anslutna SQL Server-instansen klickar du på knappen Analysera under fliken Fragmentering och väljer att utföra en snabb eller djupgående analys. Vi kommer att utföra Deep analyzing för att få fullständig information, som visas nedan:

  

  Fortsätt att övervaka förloppsfältet längst ner i verktyget för att kontrollera analysens framskridande, enligt följande:

  

  Och en omfattande rapport, som innehåller fullständig information om alla SQL-index som skapats under varje databas i den här SQL Server-instansen, kommer att visas som visas nedan:

  

  Med möjligheten att dyka mellan de olika nivåerna; SQL Server-instans, databas, tabell eller index, med ett enda klick under samma centrala fönster, och filtrera det hämtade resultatet baserat på en specifik fragmenteringsnivå eller tröskel, som visas nedan:

  

  Dessutom kan du dra vilken kolumn som helst från rapportkolumnerna för att gruppera den övergripande rapporten baserat på kolumnvärdena, som i exemplet nedan, där vi grupperade rapportdata baserat på tabellnamnet:

  

  ApexSQL Defrag Rapporter

  ApexSQL Defrag ger oss ett antal rapporter som visar statistisk information om databasindexen. Dessa rapporter kan konfigureras och kontrolleras under fliken Rapporter nedan:

  

  Den första rapporten är Totalrapporten, som visar en grafisk representation för SQL-indexfragmentering och diskutrymmeanvändning på den valda nivån. Om du till exempel klickar på instansnamnet kommer rapporten att visa grafen för fragmenteringsprocenten och grafen för utrymmesanvändning på SQL Server-instansnivå, enligt nedan:

  

  Om du klickar på en databas i fönstret Server Explorer ändrar rapporten automatiskt vyn så att den visar grafen på databasnivå, enligt nedan:

  

  Den andra rapporten, som också kan visas på olika nivåer, är statistikrapporten. Den här rapporten visar statistisk information om de totala indexens storlek och fragmentering samt fördelningen av indextyperna på den valda nivån, enligt nedan:

  

  Den tredje rapporten är rapporten Topp 10, som visar de tio största klustrade och icke-klustrade indexen med den största storleken och den högsta procentuella andelen SQL-indexfragmentering, på den angivna nivån. Följande rapport visar till exempel de tio dyraste indexen, baserat på storlek och fragmentering, på SQL-serverinstansnivå:

  

  Om du går ner till en specifik databas kommer rapporten automatiskt att visa de tio dyraste indexen, baserat på storlek och fragmentering, på den valda databasnivån, vilket visas nedan:

  

  När du dyker djupt ner till en specifik tabell under den databasen kommer rapporten att visa de tio dyraste indexen, baserat på storlek och fragmentering, enligt följande:

  

  ApexSQL Defrag Report Export

  ApexSQL Defrag gör det möjligt för oss att exportera fragmenteringsrapporterna till olika filformat, inklusive PDF, IMG, HTML, CS och XML, med möjlighet att anpassa rapportens inställning innan den genereras. Under fliken Rapporter klickar du på knappen Alternativ:

  

  I fönstret Redigera exportalternativ kan du konfigurera de olika alternativen, med början i namnkonvention för den genererade filen, sökvägen där filen kommer att sparas, sidriktningen och slutligen innehållet i varje rapport, som visas nedan:

  

  Om du till exempel vill exportera den genererade totalrapporten på den angivna nivån klickar du på knappen Exportera under fliken Rapporter och väljer den filtyp som totalrapporten ska exporteras till. Inom några sekunder kommer rapporten att genereras med en fråga om du vill öppna den genererade rapporten. Klicka på Ja för att öppna rapporten direkt, enligt nedan:

  

  Dessutom kan du exportera Top 10-rapporten på den angivna nivån genom att klicka på knappen Export under fliken Rapporter och ange exportfilens typ. Rapporten genereras snabbt och frågar om du vill visa den genererade rapporten direkt. Klicka på Ja för att öppna den genererade filen som visas nedan:

  

  Det framgår tydligt av de tidigare exemplen att vi enkelt kan använda ApexSQL Defrag-verktyget för att granska och analysera informationen om SQL-indexfragmenteringsprocenten och dyka upp på olika nivåer, från SQL Server-instansnivå till SQL Server-indexnivå, och visa den i olika format som kan exporteras utan att informationen påverkas av omstart av servern eller tjänsten, eftersom den kommer att lagras i en arkivdatabas.