sys.dm_db_index_physical_stats (Transact-SQL)

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Zwraca informacje o rozmiarze i fragmentacji dla danych i indeksów określonej tabeli lub widoku w a aparatu bazy danych programu SQL Server. W przypadku indeksu jeden wiersz jest zwracany dla każdego poziomu drzewa B w każdej partycji. W przypadku sterty jeden wiersz jest zwracany dla jednostki alokacji IN_ROW_DATA każdej partycji. W przypadku dużych danych obiektów (LOB) jeden wiersz jest zwracany dla jednostki alokacji LOB_DATA każdej partycji. Jeśli w tabeli istnieją dane przepełnienia wiersza, jeden wiersz jest zwracany dla jednostki alokacji ROW_OVERFLOW_DATA w każdej partycji.

sys.dm_db_index_physical_stats nie zwraca informacji o indeksach zoptymalizowanych pod kątem pamięci. Aby uzyskać informacje o użyciu indeksu zoptymalizowanego pod kątem pamięci, zobacz sys.dm_db_xtp_index_stats.

Jeśli wykonujesz zapytanie sys.dm_db_index_physical_stats w wystąpieniu serwera hostujący grupę dostępności repliki pomocniczej z możliwością odczytu, może wystąpić problem z blokowaniem REDO. Jest to spowodowane tym, że ten dynamiczny widok zarządzania uzyskuje blokadę Intent-Shared (IS) w określonej tabeli użytkownika lub widoku, który może blokować żądania przez wątek REDO dla blokady wyłącznej (X) dla tej tabeli lub widoku użytkownika.

Transact-SQL konwencje składni

Składnia

sys.dm_db_index_physical_stats (
    { database_id | NULL | 0 | DEFAULT }
  , { object_id | NULL | 0 | DEFAULT }
  , { index_id | NULL | 0 | -1 | DEFAULT }
  , { partition_number | NULL | 0 | DEFAULT }
  , { mode | NULL | DEFAULT }
)

Argumenty

database_id | NULL | 0 | DOMYŚLNY

Identyfikator bazy danych. database_id jest smallint. Prawidłowe dane wejściowe to identyfikator bazy danych, NULL, 0lub DEFAULT. Wartość domyślna to 0. NULL, 0i DEFAULT są równoważnymi wartościami w tym kontekście.

Określ NULL, aby zwrócić informacje dla wszystkich baz danych w wystąpieniu programu SQL Server. Jeśli określisz NULL dla database_id, należy również określić NULL dla object_id, index_idi partition_number.

Można określić wbudowaną funkcję DB_ID. Jeśli używasz DB_ID bez określania nazwy bazy danych, poziom zgodności bieżącej bazy danych musi być 90 lub większy.

object_id | NULL | 0 | DOMYŚLNY

Identyfikator obiektu tabeli lub widoku indeksu jest włączony. object_id jest int. Prawidłowe dane wejściowe to identyfikator tabeli i widoku, NULL, 0lub DEFAULT. Wartość domyślna to 0. NULL, 0i DEFAULT są równoważnymi wartościami w tym kontekście.

W programie SQL Server 2016 (13.x) i nowszych wersjach prawidłowe dane wejściowe obejmują również nazwę kolejki brokera usług lub wewnętrzną nazwę tabeli kolejki kolejki. Po zastosowaniu parametrów domyślnych (czyli wszystkich obiektów, wszystkich indeksów itp.), informacje o fragmentacji wszystkich kolejek są uwzględniane w zestawie wyników.

Określ NULL, aby zwrócić informacje dla wszystkich tabel i widoków w określonej bazie danych. Jeśli określisz NULL dla object_id, należy również określić NULL dla index_id i partition_number.

index_id | 0 | NULL | -1 | DOMYŚLNY

Identyfikator indeksu. index_id jest int. Prawidłowe dane wejściowe to identyfikator indeksu, 0 jeśli object_id jest stertą, NULL, -1lub DEFAULT. Wartość domyślna to -1. NULL, -1i DEFAULT są równoważnymi wartościami w tym kontekście.

Określ NULL, aby zwrócić informacje dla wszystkich indeksów dla tabeli podstawowej lub widoku. Jeśli określisz NULL dla index_id, należy również określić NULL dla partition_number.

partition_number | NULL | 0 | DOMYŚLNY

Numer partycji w obiekcie. partition_number jest int. Prawidłowe dane wejściowe to partition_number indeksu lub sterta, NULL, 0lub DEFAULT. Wartość domyślna to 0. NULL, 0i DEFAULT są równoważnymi wartościami w tym kontekście.

Określ NULL, aby zwrócić informacje dla wszystkich partycji obiektu, który jest właścicielem.

partition_number jest oparty na 1. Indeks niepartycyjny lub sterta ma partition_number ustawioną na wartość 1.

tryb | NULL | DOMYŚLNY

Nazwa trybu. tryb określa poziom skanowania używany do uzyskiwania statystyk. tryb jest nazwa_systemu. Prawidłowe dane wejściowe to DEFAULT, NULL, LIMITED, SAMPLEDlub DETAILED. Wartość domyślna (NULL) to LIMITED.

Zwrócona tabela

Uwagi

Funkcja dynamicznego zarządzania sys.dm_db_index_physical_stats zastępuje instrukcję DBCC SHOWCONTIG.

Tryby skanowania

Tryb, w którym jest wykonywana funkcja, określa poziom skanowania wykonywanego w celu uzyskania danych statystycznych używanych przez funkcję. tryb jest określony jako LIMITED, SAMPLEDlub DETAILED. Funkcja przechodzi przez łańcuchy stron dla jednostek alokacji, które składają się na określone partycje tabeli lub indeksu. sys.dm_db_index_physical_stats wymaga tylko blokady tabeli Intent-Shared (IS), niezależnie od trybu, w jaki działa.

Tryb LIMITED jest najszybszym trybem i skanuje najmniejszą liczbę stron. W przypadku indeksu skanowane są tylko strony na poziomie nadrzędnym drzewa B (czyli strony powyżej poziomu liścia). W przypadku sterta skojarzone strony PFS i IAM są badane, a strony danych sterta są skanowane w trybie LIMITED.

W trybie LIMITEDcompressed_page_count jest NULL, ponieważ aparat bazy danych skanuje tylko strony nieoświetne drzewa B oraz strony IAM i PFS sterta. Użyj trybu SAMPLED, aby uzyskać szacowaną wartość compressed_page_counti użyć trybu DETAILED, aby uzyskać rzeczywistą wartość dla compressed_page_count. Tryb SAMPLED zwraca statystyki na podstawie próbki 1 procent wszystkich stron w indeksie lub stercie. Wyniki w trybie SAMPLED należy traktować jako przybliżone. Jeśli indeks lub sterta ma mniej niż 10 000 stron, DETAILED tryb jest używany zamiast SAMPLED.

Tryb DETAILED skanuje wszystkie strony i zwraca wszystkie statystyki.

Tryby są stopniowo wolniejsze od LIMITED do DETAILED, ponieważ w każdym trybie jest wykonywana większa praca. Aby szybko ocenić rozmiar lub poziom fragmentacji tabeli lub indeksu, użyj trybu LIMITED. Jest to najszybszy i nie zwraca wiersza dla każdego poziomu nieuporządkowego w jednostce alokacji IN_ROW_DATA indeksu.

Określanie wartości parametrów przy użyciu funkcji systemowych

Funkcji Transact-SQL można użyć DB_ID i OBJECT_ID, aby określić wartość parametrów database_id i object_id. Jednak przekazywanie wartości, które nie są prawidłowe dla tych funkcji, może spowodować niezamierzone wyniki. Jeśli na przykład nie można odnaleźć nazwy bazy danych lub obiektu, ponieważ nie istnieją lub są niepoprawnie napisane, obie funkcje zwracają NULL. Funkcja sys.dm_db_index_physical_stats interpretuje NULL jako wartość wieloznaczny określającą wszystkie bazy danych lub wszystkie obiekty.

Ponadto funkcja OBJECT_ID jest przetwarzana przed wywołaniem funkcji sys.dm_db_index_physical_stats i dlatego jest obliczana w kontekście bieżącej bazy danych, a nie bazy danych określonej w database_id. To zachowanie może spowodować, że funkcja OBJECT_ID zwróci wartość NULL; lub jeśli nazwa obiektu istnieje zarówno w bieżącym kontekście bazy danych, jak i w określonej bazie danych, zwracany jest komunikat o błędzie. W poniższych przykładach pokazano te niezamierzone wyniki.

USE master;
GO
-- In this example, OBJECT_ID is evaluated in the context of the master database.
-- Because Person.Address does not exist in master, the function returns NULL.
-- When NULL is specified as an object_id, all objects in the database are returned.
-- The same results are returned when an object that is not valid is specified.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks2022'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- This example demonstrates the results of specifying a valid object name
-- that exists in both the current database context and
-- in the database specified in the database_id parameter of the
-- sys.dm_db_index_physical_stats function.
-- An error is returned because the ID value returned by OBJECT_ID does not
-- match the ID value of the object in the specified database.
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2022;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO

Najlepsze rozwiązanie

Zawsze upewnij się, że podczas używania DB_ID lub OBJECT_IDjest zwracany prawidłowy identyfikator. Na przykład w przypadku użycia OBJECT_IDokreśl trzyczęściową nazwę, taką jak OBJECT_ID(N'AdventureWorks2022.Person.Address'), lub przetestuj wartość zwracaną przez funkcje przed użyciem ich w funkcji sys.dm_db_index_physical_stats. Przykłady A i B, które są zgodne z opisem bezpiecznego sposobu określania identyfikatorów baz danych i obiektów.

Wykrywanie fragmentacji

Fragmentacja odbywa się przez proces modyfikacji danych (INSERT, UPDATEi instrukcji DELETE), które są wykonywane względem tabeli, a zatem do indeksów zdefiniowanych w tabeli. Ponieważ te modyfikacje nie są zwykle równomiernie rozłożone między wiersze tabeli i indeksów, pełna cała każda strona może się różnić w czasie. W przypadku zapytań, które skanują część lub wszystkie indeksy tabeli, ten rodzaj fragmentacji może spowodować więcej operacji odczytu strony, co utrudnia równoległe skanowanie danych.

Poziom fragmentacji indeksu lub sterta jest wyświetlany w kolumnie avg_fragmentation_in_percent. W przypadku stert wartość reprezentuje fragmentację zakresu sterta. W przypadku indeksów wartość reprezentuje fragmentację logiczną indeksu. W przeciwieństwie do DBCC SHOWCONTIGalgorytmy obliczania fragmentacji w obu przypadkach uwzględniają magazyn obejmujący wiele plików i dlatego są dokładne.

Fragmentacja logiczna

Jest to procent stron poza kolejnością na stronach liści indeksu. Strona poza kolejnością to strona, dla której następna strona fizyczna przydzielona do indeksu nie jest stroną wskazywaną przez wskaźnik następnej strony na bieżącej stronie liścia.

Fragmentacja zakresu

Jest to procent zakresów poza kolejnością na stronach liści sterta. Zakres poza kolejnością jest taki, dla którego zakres zawierający bieżącą stronę sterta nie jest fizycznie następnym zakresem po zakresie, który zawiera poprzednią stronę.

Wartość avg_fragmentation_in_percent powinna być jak najbardziej zbliżona do zera w celu uzyskania maksymalnej wydajności. Jednak wartości z zakresu od 0 do 10 procent mogą być akceptowalne. Wszystkie metody zmniejszania fragmentacji, takie jak ponowne kompilowanie, reorganizacja lub ponowne tworzenie, mogą służyć do zmniejszenia tych wartości. Aby uzyskać więcej informacji na temat analizowania stopnia fragmentacji w indeksie, zobacz Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów.

Zmniejszanie fragmentacji w indeksie

Gdy indeks jest fragmentowany w sposób, w jaki fragmentacja wpływa na wydajność zapytań, istnieją trzy opcje zmniejszenia fragmentacji:

• Upuść i utwórz ponownie indeks klastrowany.

  Ponowne utworzenie klastrowanego indeksu redystrybuuje dane i powoduje wyświetlenie pełnych stron danych. Poziom pełnej można skonfigurować przy użyciu opcji FILLFACTOR w CREATE INDEX. Wadą tej metody jest to, że indeks jest w trybie offline podczas upuszczania i ponownego tworzenia cyklu, a operacja jest niepodzielna. Jeśli tworzenie indeksu zostanie przerwane, indeks nie zostanie ponownie utworzony. Aby uzyskać więcej informacji, zobacz CREATE INDEX.

• Użyj ALTER INDEX REORGANIZE, zamiany DBCC INDEXDEFRAG, aby zmienić kolejność stron na poziomie liścia indeksu w kolejności logicznej. Ponieważ jest to operacja online, indeks jest dostępny, gdy instrukcja jest uruchomiona. Operację można również przerwać bez utraty już ukończonej pracy. Wadą tej metody jest to, że nie robi tak dobrego zadania, aby zreorganizować dane jako operację ponownego kompilowania indeksu i nie aktualizuje statystyk.

• Użyj ALTER INDEX REBUILD, zastąpienie DBCC DBREINDEX, aby ponownie skompilować indeks w trybie online lub offline. Aby uzyskać więcej informacji, zobacz ALTER INDEX (Transact-SQL).

Sama fragmentacja nie jest wystarczającą przyczyną reorganizacji lub ponownego kompilowania indeksu. Głównym efektem fragmentacji jest to, że spowalnia przepływność odczytu strony z wyprzedzeniem podczas skanowania indeksu. Powoduje to wolniejsze czasy odpowiedzi. Jeśli obciążenie zapytania w pofragmentowanej tabeli lub indeksu nie obejmuje skanowania, ponieważ obciążenie jest przede wszystkim pojedynczymi odnośnikami, usunięcie fragmentacji nie może mieć wpływu.

Zmniejszanie fragmentacji w stercie

Aby zmniejszyć fragmentację zakresu sterta, utwórz indeks klastrowany w tabeli, a następnie upuść indeks. Spowoduje to ponowne dystrybuowanie danych podczas tworzenia klastrowanego indeksu. Dzięki temu jest ona również jak najbardziej optymalna, biorąc pod uwagę rozkład wolnego miejsca dostępnego w bazie danych. Po usunięciu klastrowanego indeksu w celu ponownego utworzenia sterty dane nie są przenoszone i pozostają optymalnie w położeniu. Aby uzyskać informacje o sposobie wykonywania tych operacji, zobacz CREATE INDEX and DROP INDEX.

Kompaktowanie dużych danych obiektów

Domyślnie instrukcja ALTER INDEX REORGANIZE kompaktuje strony zawierające dane dużego obiektu (LOB). Ponieważ strony LOB nie są cofane, gdy są puste, kompaktowanie tych danych może poprawić użycie miejsca na dysku, jeśli wiele danych biznesowych zostanie usuniętych lub kolumna LOB zostanie porzucona.

Reorganizacja określonego indeksu klastrowanego kompaktuje wszystkie kolumny LOB zawarte w indeksie klastrowanym. Reorganizacja indeksu nieklastrowanego kompaktuje wszystkie kolumny LOB, które są kolumnami niekluczowymi (dołączonymi) w indeksie. Po określeniu ALL w instrukcji wszystkie indeksy skojarzone z określoną tabelą lub widokiem zostaną zreorganizowane. Ponadto wszystkie kolumny LOB skojarzone z indeksem klastrowanym, tabelą bazową lub indeksem nieklastrowanym z dołączonymi kolumnami są kompaktowane.

Ocena użycia miejsca na dysku

Kolumna avg_page_space_used_in_percent wskazuje pełnię strony. Aby osiągnąć optymalne użycie miejsca na dysku, ta wartość powinna być zbliżona do 100 procent dla indeksu, który nie ma wielu losowych wstawiania. Jednak indeks, który ma wiele losowych wstawień i ma bardzo pełne strony, ma zwiększoną liczbę podziałów stron. Powoduje to większą fragmentację. W związku z tym, aby zmniejszyć podziały stron, wartość powinna być mniejsza niż 100 procent. Ponowne kompilowanie indeksu przy użyciu określonej opcji FILLFACTOR umożliwia zmianę pełnej strony w celu dopasowania wzorca zapytania do indeksu. Aby uzyskać więcej informacji na temat współczynnika wypełnienia, zobacz Określanie współczynnika wypełnienia dla indeksu. Ponadto ALTER INDEX REORGANIZE skompaktuje indeks, próbując wypełnić strony do FILLFACTOR, które zostały ostatnio określone. Zwiększa to wartość w avg_space_used_in_percent. ALTER INDEX REORGANIZE nie można zmniejszyć pełnej liczby stron. Zamiast tego należy wykonać ponowną kompilację indeksu.

Ocena fragmentów indeksu

Fragment składa się z fizycznie kolejnych stron liści w tym samym pliku dla jednostki alokacji. Indeks ma co najmniej jeden fragment. Maksymalna liczba fragmentów indeksu może być równa liczbie stron na poziomie liścia indeksu. Większe fragmenty oznaczają, że do odczytu tej samej liczby stron wymagana jest mniejsza liczba operacji we/wy dysku. W związku z tym tym większa wartość avg_fragment_size_in_pages, tym większa jest wydajność skanowania zakresu. Wartości avg_fragment_size_in_pages i avg_fragmentation_in_percent są odwrotnie proporcjonalne do siebie. W związku z tym ponowne kompilowanie lub reorganizacja indeksu powinno zmniejszyć ilość fragmentacji i zwiększyć rozmiar fragmentu.

Ograniczenia

Nie zwraca danych dla klastrowanych indeksów magazynu kolumn.

Uprawnienia

Wymaga następujących uprawnień:

• CONTROL uprawnienia do określonego obiektu w bazie danych.

• VIEW DATABASE STATE lub VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) uprawnienie do zwracania informacji o wszystkich obiektach w określonej bazie danych przy użyciu symbolu wieloznakowego obiektu @object_id = NULL.

• VIEW SERVER STATE lub VIEW SERVER PERFORMANCE STATE (SQL Server 2022) uprawnienie do zwracania informacji o wszystkich bazach danych przy użyciu symbolu wieloznacznych bazy danych @database_id = NULL.

Udzielenie VIEW DATABASE STATE umożliwia zwracanie wszystkich obiektów w bazie danych, niezależnie od wszelkich uprawnień CONTROL odmowy dla określonych obiektów.

Odmowa VIEW DATABASE STATE nie zezwala na zwracanie wszystkich obiektów w bazie danych, niezależnie od uprawnień CONTROL przyznanych dla określonych obiektów. Ponadto, gdy zostanie określona symbol wieloznaczny bazy danych @database_id = NULL, baza danych zostanie pominięta.

Aby uzyskać więcej informacji, zobacz Dynamiczne widoki zarządzania systemu.

Przykłady

Przykłady kodu Transact-SQL w tym artykule korzystają z przykładowej bazy danych AdventureWorks2022 lub AdventureWorksDW2022, którą można pobrać ze strony głównej Przykłady programu Microsoft SQL Server i projekty społeczności.

A. Zwracanie informacji o określonej tabeli

Poniższy przykład zwraca statystyki dotyczące rozmiaru i fragmentacji dla wszystkich indeksów i partycji tabeli Person.Address. Tryb skanowania jest ustawiony na LIMITED, aby uzyskać najlepszą wydajność i ograniczyć zwracane statystyki. Wykonanie tego zapytania wymaga co najmniej CONTROL uprawnienia do tabeli Person.Address.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.Person.Address');

IF @db_id IS NULL
BEGIN;
    PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO

B. Zwracanie informacji o stercie

Poniższy przykład zwraca wszystkie statystyki dla sterty dbo.DatabaseLog w bazie danych AdventureWorks2022. Ponieważ tabela zawiera dane LOB, wiersz jest zwracany dla jednostki alokacji LOB_DATA oprócz wiersza zwróconego dla IN_ROW_ALLOCATION_UNIT, który przechowuje strony danych sterty. Wykonanie tego zapytania wymaga co najmniej CONTROL uprawnienia do tabeli dbo.DatabaseLog.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2022');
SET @object_id = OBJECT_ID(N'AdventureWorks2022.dbo.DatabaseLog');
IF @object_id IS NULL
BEGIN;
    PRINT N'Invalid object';
END;
ELSE
BEGIN;
    SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED');
END;
GO

C. Zwracanie informacji dla wszystkich baz danych

Poniższy przykład zwraca wszystkie statystyki dla wszystkich tabel i indeksów w wystąpieniu programu SQL Server, określając symbol wieloznaczny NULL dla wszystkich parametrów. Wykonanie tego zapytania wymaga uprawnienia VIEW SERVER STATE.

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO

D. Używanie sys.dm_db_index_physical_stats w skryscie w celu ponownego kompilowania lub reorganizacji indeksów

Poniższy przykład automatycznie reorganizuje lub ponownie kompiluje wszystkie partycje w bazie danych, które mają średnią fragmentację ponad 10 procent. Wykonanie tego zapytania wymaga uprawnienia VIEW DATABASE STATE. W tym przykładzie określono DB_ID jako pierwszy parametr bez określania nazwy bazy danych.

-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;

DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @partitioncount BIGINT;
DECLARE @schemaname NVARCHAR(130);
DECLARE @objectname NVARCHAR(130);
DECLARE @indexname NVARCHAR(130);
DECLARE @partitionnum BIGINT;
DECLARE @partitions BIGINT;
DECLARE @frag FLOAT;
DECLARE @command NVARCHAR(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0
    AND index_id > 0;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR
FOR
SELECT *
FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1 = 1)
BEGIN;

    FETCH NEXT
    FROM partitions
    INTO @objectid,
        @indexid,
        @partitionnum,
        @frag;

    IF @@FETCH_STATUS < 0
        BREAK;

    SELECT @objectname = QUOTENAME(o.name),
        @schemaname = QUOTENAME(s.name)
    FROM sys.objects AS o
    INNER JOIN sys.schemas AS s
        ON s.schema_id = o.schema_id
    WHERE o.object_id = @objectid;

    SELECT @indexname = QUOTENAME(name)
    FROM sys.indexes
    WHERE object_id = @objectid
        AND index_id = @indexid;

    SELECT @partitioncount = count(*)
    FROM sys.partitions
    WHERE object_id = @objectid
        AND index_id = @indexid;

    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    IF @frag < 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

    IF @frag >= 30.0
        SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

    IF @partitioncount > 1
        SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10));

    EXEC (@command);

    PRINT N'Executed: ' + @command;
END;

-- Close and deallocate the cursor.
CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

E. Użyj sys.dm_db_index_physical_stats, aby wyświetlić liczbę skompresowanych stron

W poniższym przykładzie pokazano, jak wyświetlać i porównywać łączną liczbę stron względem stron, które są skompresowane wierszami i stronami. Te informacje mogą służyć do określania korzyści zapewnianej przez kompresję indeksu lub tabeli.

SELECT o.name,
    ips.partition_number,
    ips.index_type_desc,
    ips.record_count,
    ips.avg_record_size_in_bytes,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.page_count,
    ips.compressed_page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.objects o
    ON o.object_id = ips.object_id
ORDER BY record_count DESC;

F. Używanie sys.dm_db_index_physical_stats w trybie SAMPLED

W poniższym przykładzie pokazano, jak SAMPLED tryb zwraca przybliżoną wartość inną niż wyniki trybu DETAILED.

CREATE TABLE t3 (
    col1 INT PRIMARY KEY,
    col2 VARCHAR(500)
    )
    WITH (DATA_COMPRESSION = PAGE);
GO

BEGIN TRANSACTION

DECLARE @idx INT = 0;

WHILE @idx < 1000000
BEGIN
    INSERT INTO t3 (col1, col2)
    VALUES (
        @idx,
        REPLICATE('a', 100) + CAST(@idx AS VARCHAR(10)) + REPLICATE('a', 380)
        )

    SET @idx = @idx + 1
END

COMMIT;
GO

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'SAMPLED');

SELECT page_count,
    compressed_page_count,
    forwarded_record_count,
    *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'DETAILED');

G. Wykonywanie zapytań względem kolejek brokera usługi na potrzeby fragmentacji indeksu

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje

W poniższym przykładzie pokazano, jak wykonywać zapytania dotyczące kolejek brokera serwera na potrzeby fragmentacji.

--Using queue internal table name
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('sys.queue_messages_549576996'), DEFAULT, DEFAULT, DEFAULT);

--Using queue name directly
SELECT *
FROM sys.dm_db_index_physical_stats(db_id(), object_id('ExpenseQueue'), DEFAULT, DEFAULT, DEFAULT);

Powiązana zawartość

• dynamiczne widoki zarządzania systemu
• dynamiczne widoki zarządzania i funkcje związane z indeksem (Transact-SQL)
• sys.dm_db_index_operational_stats (Transact-SQL)
• sys.dm_db_index_usage_stats (Transact-SQL)
• sys.dm_db_partition_stats (Transact-SQL)
• sys.allocation_units (Transact-SQL)
• dokumentacja Transact-SQL (aparat bazy danych)