Free Press

Formålet med at oprette SQL-indekser på databasetabeller er at forbedre ydeevnen for de forespørgsler, der læser data fra grundtabellen, ved at fremskynde datahentningsprocesserne. Men disse transaktionstabeller er ikke statiske og ændrer sig meget ofte over tid. Disse ændringer, der udføres på databasetabellen, omfatter tilføjelse af nye poster, ændring eller sletning af eksisterende poster. Da disse ændringer skal replikeres til de relaterede tabelindekser, vil tabelindekset blive fragmenteret over tid.

SQL Server Index Fragmentering Oversigt

Der er to hovedtyper af SQL-indeksfragmentering; intern og ekstern fragmentering. Den interne fragmentering skyldes, at der indsættes en ny post eller opdateres en eksisterende post med værdier, der ikke passer ind i den aktuelle frie plads i datasiden, hvilket resulterer i, at siden deles op i to sider for at få plads til den nye værdi. I dette tilfælde vil SQL Server Engine flytte ca. 50 % af dataene fra den aktuelle side til den nye dataside, der genereres af opdelingen, for at holde SQL-indekstræet afbalanceret. Intern fragmentering kan også skyldes tilfældige sletningsoperationer, som resulterer i tom plads på datasiderne. Denne type operationer efterlader datasiderne uudfyldte og resulterer i større SQL Server-indeksstørrelse og langsommere ydeevne på grund af behovet for at læse flere datasider for at hente de ønskede data.

Ekstern fragmentering opstår, når datasiderne i SQL Server-indekset er spredt på den fysiske databasefil, fordi den logiske rækkefølge i SQL Server-indekset ikke stemmer overens med den fysiske rækkefølge på det underliggende diskdrev. Ekstern fragmentering kan forårsages af tilfældige sletninger, der efterlader et antal udstrækningssider tomme, mens selve udstrækningen er reserveret. Denne type fragmentering medfører en forringelse af ydeevnen, da det vil tage længere tid og forbruge flere ressourcer at hoppe mellem de spredte datasider for at hente de ønskede data fra det underliggende diskdrev.

Grunden til, at vi bekymrer os meget om SQL-indeksfragmentering, er, at det indeks, som du har oprettet for at forbedre systemets ydeevne, kan forringe dine forespørgsels ydeevne, når det er meget fragmenteret. Forestil dig forskellen i tid og antal disklæsninger mellem at læse et bestemt antal rækker fra et sundt SQL-indeks, hvor dataene er gemt i sammenhængende datasider i databasefilen, og at læse det samme antal rækker fra et stærkt fragmenteret SQL Server-indeks, hvor dataene er spredt i ikke-sammenhængende datasider i databasefilen.

Hvis det konstateres, at tabellen SQL-indekset er fragmenteret, skal du løse fragmenteringsproblemet ved at udføre SQL Server Index Reorganization eller Index Rebuild, baseret på SQL-indeksfragmenteringsprocenten. F.eks. kan et SQL Server-indeks med en fragmenteringsprocent på mindre end 30 % omorganiseres, hvor SQL-indekset med en fragmenteringsprocent på over 30 % skal genopbygges.

• Bemærk: Se Vedligeholdelse af SQL Server-indekser for at få flere oplysninger.

Du kan proaktivt afhjælpe problemer med SQL Server-indeksfragmentering og sideopdeling ved at designe indekset korrekt og indstille indstillingerne Fill Factor og pad_index index creation med properværdierne.

• Bemærk: Se SQL Server Index Operations for at få flere oplysninger.

SQL Server Index Fragmentation Report

Det betragtes ikke som en bedste praksis blot at oprette et SQL-indeks i din databasetabel for at drage fordel af forbedringen af datahentningsoperationen og lade dette SQL Server-indeks stå for evigt uden løbende at overvåge dets fragmentering og vedligeholde det.

SQL Server giver os flere metoder, der kan bruges til at kontrollere SQL-indeksfragmenteringsprocenten.

SQL Server Indeksegenskaber

Den første metode er siden Fragmentering i vinduet Indeksegenskaber i SQL Server. Højreklik på det indeks, som du skal kontrollere, og vælg Egenskaber. Fra siden SQL Server Indeksegenskaber skal du gennemse siden Fragmentering, og du vil se indeksfragmenteringsprocenten, og hvor meget SQL Server indekssiderne er fulde under afsnittet Fragmentering, som vist nedenfor:

I et par trin kan du nemt kontrollere SQL Server indeksfragmenteringsprocenten for det valgte indeks fra vinduet Indeksegenskaber. Men hvad nu hvis du har brug for at få et overblik over fragmenteringsprocenten for alle tabellernes indekser eller alle databasetabellernes indekser? I dette tilfælde skal du gå hen og kontrollere det en efter en. Derfor vil en enkelt arbejdsuge ikke være nok til en enkelt databaserapport!

sys.dm_db_index_physical_stats

En anden metode, der kan bruges til at kontrollere fragmenteringsprocenten for databaseindeksene, er at spørge den dynamiske forvaltningsfunktion sys.dm_db_index_physical_stats, der blev indført første gang i SQL Server 2005 som erstatning for den forældede DBCC SHOWCONTIG-kommando. Den giver oplysninger om størrelsen og fragmenteringsprocenten for databaseindeksene.

For at få meningsfulde oplysninger om fragmenteringsprocenten for alle indekser under den angivne database fra sys.dm_db_index_physical_stats DMF, skal du sammenføje den med sys.indexes DMV, som i T-SQL-scriptet nedenfor:

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

Baseret på dine SQL-udviklingsevner kan du udføre den filtrering, du har brug for, for at hente SQL-indeksfragmenteringen på det ønskede område, uden mulighed for at få en oversigt for de samlede instansdatabaser, idet du tager hensyn til, at de viste data er siden den seneste genstart af SQL Server-tjenesten. Resultatet i vores tilfælde vil se sådan ud:

SQL Server Index Physical Statistics Standard Report

SQL Server giver os flere standardrapporter, der indeholder nyttige statistiske oplysninger om den valgte database. Rapporten Index Physical Statistics er en af SQL Server-standardrapporterne, der returnerer statistiske oplysninger om SQL Server-indekspartitioner, fragmenteringsprocent og antallet af sider på hver SQL-indekspartition på databaseniveau siden den seneste genstart af SQL Server-tjenesten med anbefaling af, hvordan man kan løse problemet med indeksfragmentering, hvis det findes, baseret på fragmenteringsprocenten for SQL Server-indekset.

For at få vist SQL Server Index Physical Statistics-rapporten skal du højreklikke på din database, vælge Reports -> Standard Reports og derefter vælge Index Physical Statistics-rapporten, som vist nedenfor:

Og den genererede rapport på databaseniveau, der ikke kan filtreres og skal udvides manuelt for hvert tabelindeks-resultat, vil være som vist nedenfor:

ApexSQL Defrag

ApexSQL Defrag værktøjet er et tredjeparts SQL indeks defragmenteringsværktøj, der nemt kan bruges til at kontrollere SQL Server indeksfragmentering og brugsoplysninger og udføre eller planlægge den passende rettelse til indeksfragmenteringsproblemet.

Fra et centralt sted kan du analysere og gennemgå fragmenterings- og brugsoplysningerne på forskellige niveauer, herunder SQL-indeksfragmentering og brugsoplysninger på SQL Server-indeksniveau, tabelniveau, databaseniveau og endelig på det overordnede SQL Server-instansniveau. Den giver os forskellige typer filtre, der kan bruges til kun at vise og vedligeholde dine målindekser.

ApexSQL Defrag giver os også mulighed for at konfigurere Fill Factor-indstillingen for tabelindekser, oprette advarsler for alle defragmenteringsjobs og politikker og sende e-mail-meddelelser for vellykkede eller mislykkede job.

De viste oplysninger vil give et godt overblik over SQL-indeksstørrelsen og fragmenteringsprocenten, som ikke vil blive påvirket af en genstart af SQL Server-tjenesten eller en genstart af serveren. Dette skyldes, at disse oplysninger vil blive gemt i en central repository-database for ApexSQL Defrag-værktøjet.

Installation af ApexSQL Defrag

ApexSQL Defrag kan nemt downloades fra ApexSQL Download Center og installeres på din server ved at følge den ligetil installationsguide, som vist nedenfor:

Først bliver du bedt om at angive den servicekonto, der skal bruges som eksekveringskontekst for ApexSQL Defrag-agenten og overfører kommandoer til SQL Server-instansen, som følger:

Først bliver du bedt om at angive den servicekonto, der skal bruges som eksekveringskontekst for ApexSQL Defrag-agenten og overfører kommandoer til SQL Server-instansen, som følger:

Når du har angivet ApexSQL Defrag-tjenestekontoen, bliver du bedt om at angive, hvor ApexSQL Defrag-værktøjet skal installeres, og om der skal oprettes et genvejsikon til værktøjet på skrivebordet, som vist nedenfor:

Hvis du kontrollerer, at den angivne sti indeholder mindst 127 MB ledig plads, vil ApexSQL Defrag blive installeret med succes på din server. Og følgende meddelelse vil blive vist, hvilket giver dig mulighed for at starte ApexSQL Defrag direkte:

Hvis du kører ApexSQL Defrag for første gang, vil du blive bedt om at bekræfte oprettelsen af den centrale repository-database, hvor disse historiske data og konfigurationsdata vil blive gemt, som følger:

Add New Server

Når du har oprettet repository-databasen, startes ApexSQL Defrag uden nogen SQL-instans. Hvis du vil kontrollere SQL-indeksfragmenteringsprocenten på en bestemt SQL Server-instans, skal du tilføje denne instans til dette værktøj. For at opnå dette skal du klikke på knappen Tilføj under fanen Hjem, som vist nedenfor:

I vinduet Opret forbindelse til SQL Server bliver du bedt om at angive navnet på den SQL Server-instans, der skal bruges, og de legitimationsoplysninger, der kræves for at oprette forbindelse til den pågældende server, som følger:

SQL Server Indeksanalyse

Når serveren er tilføjet, kan du udføre en ny kontrol for alle SQL-indeksfragmenteringsoplysninger fra fanen Fragmentering nedenfor:

ApexSQL Defrag giver dig mulighed for at analysere alle indekser på den pågældende instans baseret på tre tilstande:

ApexSQL Defrag giver dig mulighed for at analysere alle indekser på den pågældende instans baseret på tre tilstande:

• DETAILED: hvor alle data- og SQL-indeks-sider vil blive læst under scanningen
• SAMPLED: hvor kun 1 % af siderne vil blive læst, idet det tages i betragtning, at SQL Server-indekset indeholder mere end 10.000 sider
• LIMITED: for at analysere SQL Server-indeksoplysninger fra den tilsluttede SQL Server-instans skal du klikke på knappen Analyser under fanen Fragmentering og vælge at udføre en hurtig eller dybdegående analyse. Vi vil udføre Deep analyzing for at få fuld information, som vist nedenfor:

  

  Hold øje med fremskridtslinjen nederst i værktøjet for at kontrollere analysens fremskridt, som følger:

  

  Og en omfattende rapport, der indeholder fuld information om alle SQL-indekser oprettet under hver database i denne SQL Server-instans, vil blive vist som vist nedenfor:

  

  Med mulighed for at dykke mellem de forskellige niveauer; SQL Server-instans, database, tabel eller indeks, med et enkelt klik under det samme centrale vindue, og filtrere det hentede resultat baseret på et specifikt fragmenteringsniveau eller tærskel, som vist nedenfor:

  

  Derudover kan du trække en hvilken som helst kolonne fra rapportens kolonner for at gruppere den samlede rapport baseret på de pågældende kolonneværdier, som f.eks. eksemplet nedenfor, hvor vi grupperede rapportdataene baseret på tabelnavnet:

  

  ApexSQL Defrag Reports

  ApexSQL Defrag giver os en række rapporter, der viser statistiske oplysninger om databaseindeksene. Disse rapporter kan konfigureres og kontrolleres under fanen Rapporter nedenfor:

  

  Den første rapport er Totalrapporten, der viser en grafisk repræsentation for SQL-indeksfragmentering og diskpladsforbrug på det valgte niveau. Hvis du f.eks. klikker på instansnavnet, tegner rapporten grafen for fragmenteringsprocenten og grafen for pladsforbrug på SQL Server-instansniveau, som vist nedenfor:

  

  Hvis du klikker på en database fra vinduet Server Explorer, ændrer rapporten automatisk visningen til at vise grafen på databaseniveau, som vist nedenfor:

  

  Den anden rapport, som også kan ses på forskellige niveauer, er rapporten Statistics (Statistik). Denne rapport viser statistiske oplysninger om de samlede indeksers størrelse og fragmentering og fordelingen af indekstyperne på det valgte niveau, som vist nedenfor:

  

  Den tredje rapport er rapporten Top 10, der viser de 10 største clusterede og ikke-clusterede indekser med den største størrelse og højeste SQL-indeksfragmenteringsprocent, på det angivne niveau. Følgende rapport viser f.eks. de 10 dyreste indekser baseret på størrelse og fragmentering på SQL-serverinstansniveau:

  

  Hvis du bevæger dig ned til en specifik database, viser rapporten automatisk de 10 dyreste indekser baseret på størrelse og fragmentering på det valgte databaseniveau, som vist nedenfor:

  

  Ved at dykke dybt ned til en specifik tabel under den pågældende database, vil rapporten vise de 10 dyreste indekser, baseret på størrelse og fragmentering, som følger:

  

  ApexSQL Defrag Report Export

  ApexSQL Defrag giver os mulighed for at eksportere fragmenteringsrapporterne til forskellige filformater, herunder PDF, IMG, HTML, CS og XML, med mulighed for at tilpasse rapportens indstilling, før den genereres. Under fanen Rapporter skal du klikke på knappen Indstillinger:

  

  Fra vinduet Rediger eksportindstillinger kan du konfigurere de forskellige indstillinger, startende fra navngivningskonventionen for den genererede fil, stien, hvor filen skal gemmes, sideorienteringen og endelig indholdet af hver rapport, som vist nedenfor:

  

  Hvis du f.eks. vil eksportere den genererede Totalrapport på det angivne niveau, skal du klikke på knappen Eksporter under fanen Rapporter og vælge den filtype, som denne Totalrapport skal eksporteres til. I løbet af få sekunder genereres rapporten og du bliver spurgt, om du vil åbne den genererede rapport. Klik på Ja for at åbne rapporten direkte, som vist nedenfor:

  

  Derudover kan du eksportere Top 10-rapporten på det angivne niveau ved at klikke på knappen Eksporter under fanen Rapporter og angive eksportfiltypen, hvorefter rapporten hurtigt genereres og spørger dig, om du vil se den genererede rapport direkte. Klik på Ja for at åbne den genererede, som vist nedenfor:

  

  Det fremgår tydeligt af de foregående eksempler, at vi nemt kan bruge ApexSQL Defrag-værktøjet til at gennemgå og analysere SQL-indeksfragmenteringsprocentoplysningerne og dykke ned på de forskellige niveauer fra SQL Server-instansniveau til SQL Server-indeksniveau og vise dem i forskellige formater, der kan eksporteres, uden at de viste oplysninger påvirkes af en server eller genstart af en tjeneste, da de vil blive gemt i en arkivdatabase.