sys.dm_db_index_physical_stats (języka Transact-SQL)

Tryb, w którym jest wykonywana funkcja Określa poziom skanowania przeprowadzana w celu uzyskania danych statystycznych, która jest używana przez funkcja.modejest określona jako ograniczona, pobrano próbki lub SZCZEGÓŁOWYM.funkcja przechodzi łańcuchów strona dla jednostek alokacji, składające się na określonej partycji tabela lub indeksu.sys.dm_db_index_physical_statswymaga tylko Intent-Shared (IS) tabela blokada, niezależnie od trybu, działającą w.Aby uzyskać więcej informacji na temat blokowania, zobacz Tryby Lock.

Tryb ograniczona jest najszybszy tryb i skanuje najmniejszą liczbę stron.Na indeks są skanowane tylko nadrzędny -poziom strony w B-drzewo (oznacza to, że strony powyżejpoziom liść). Sterty badane są tylko powiązane strony doskonałe utajnienie przekazywania i IAM; strony danych sterty nie były skanowane.W SQL Server 2005, wszystkie strony sterty były skanowane w trybie ograniczonym.

W trybie ograniczonym compressed_page_count ma wartość NULL, ponieważ Aparat baz danych tylko skanowanie nie-stronliść B-drzewo i IAM oraz doskonałe utajnienie przekazywania stron sterty.Użyj trybu pobrano próbki, aby uzyskać szacowaną wartość dla compressed_page_counti w trybie szczegółowe rzeczywistą wartość dla compressed_page_count...Tryb pobrano próbki zwraca statystyki na podstawie próbki 1 procent wszystkich stron w indeksie lub sterty.Jeżeli indeks lub sterty ma mniej niż 10 000 stron, tryb szczegółowy jest używany zamiast pobrano próbki.

Tryb szczegółowy skanuje wszystkie strony i zwraca wszystkie statystyki.

Tryby są stopniowo wolniejsze z ograniczoną szczegółowy, ponieważ więcej pracy jest wykonywane w każdym z trybów.Aby szybko ocenić rozmiaru lub rozdrobnienia z poziom tabela lub indeksie, należy użyć trybu ograniczonej.Jak najszybciej i nie zwróci wiersz dla każdego nie typu liść poziom IN_ROW_DATA jednostka alokacji indeksu.

Można użyć Transact-SQL Funkcje DB_ID i OBJECT_ID , aby określić wartość dla database_id i object_id Parametry.Przekazywanie wartości, które nie są prawidłowe, funkcje te może jednak powodować innych niepożądanych wyniki.Na przykład jeśli nie można odnaleźć nazwy bazy danych lub obiektu, ponieważ nie istnieją lub są nieprawidłowo napisane, zarówno funkcje zwracają wartość NULL.sys.dm_db_index_physical_statsfunkcja interpretuje wartość NULL jako wartość symbole wieloznaczne określające wszystkie bazy danych lub wszystkich obiektów.

Ponadto OBJECT_ID funkcja jest przetwarzane przed sys.dm_db_index_physical_stats funkcja jest nazywana i dlatego jest oceniane w kontekście bieżącej bazy danych nie bazy danych określonej w database_id.Takie zachowanie może powodować OBJECT_ID funkcja zwróci wartość NULL; lub, jeśli istnieje parametr Nazwa obiektu w bieżącym kontekście bazy danych i określonej bazy danych, komunikat o błędzie może być zwrócona.Następujące przykłady demonstrują tych innych niepożądanych wyniki.

W poniższym przykładzie OBJECT_ID oceniane w kontekście master bazy danych.Ponieważ Person.Address nie istnieje w master, funkcja zwraca NULL.Gdy NULL jest określony jako object_id, zwracane są wszystkie obiekty w bazie danych.Określony obiekt, który nie jest prawidłowy zwracane są takie same wyniki .

USE master;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO

Poniższy przykład przedstawia wyniki określający nazwę prawidłowego obiektu, który istnieje w obu bieżącego kontekstu bazy danych i baza danych określona w database_id parametr sys.dm_db_index_physical_stats funkcja.Zwracany jest błąd, ponieważ wartość Identyfikatora zwrócony przez OBJECT_ID jest niezgodna wartość Identyfikatora obiektu w określonej bazie danych.

CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2008R2;
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

W trosce o

Fragmentacja występuje w procesie modyfikacji danych (instrukcje INSERT, UPDATE i DELETE) wprowadzone w tabela i dlatego indeksów zdefiniowane w tabela.Ponieważ te modyfikacje nie są zwykle rozpowszechniane jednakowo między wiersze tabela i indeksów, stopnia ukończenia każdej strona może się zmieniać w czas.W przypadku kwerend, które skanowanie części lub całości indeksów tabelatego rodzaju fragmentacji może spowodować odczyty dodatkowe strona .Przeszkadza, równoległe skanowania danych.

Algorytm obliczania rozdrobnienia jest bardziej precyzyjne w SQL Server 2008 niż w SQL Server 2000.W wyniku fragmentacji wyświetlane wartości będą wyższe.Na przykład w SQL Server 2000, tabela nie jest uważany za fragmentacji, jeśli ma ona strona 11 i strona 13 w takim samym fragment , ale nie strona 12.Jednak dostęp do tych dwóch stron wymaga dwóch fizycznych operacji We/Wy, co to jest liczony jako fragmentacji w SQL Server 2008.

Fragmentacja poziom indeksu lub sterta jest przedstawiony w avg_fragmentation_in_percent kolumna.Dla stert wartość reprezentuje fragment fragmentacji sterty.Indeksy wartość reprezentuje logiczną fragmentacji indeksu.W odróżnieniu od DBCC SHOWCONTIG algorytmy obliczeń fragmentacji w obu przypadkach należy wziąć pod uwagę magazynu, który obejmuje wiele plików i dlatego są dokładne.

Fragmentacja logicznych

Jest to procent poza kolejnością stron na stronach liść indeksu.Poza kolejność strona jest strona , dla których następnej fizycznej strona przydzielonych do indeksu nie jest strona wskazywana przez pag daleje wskaźnik liśćbieżącejstrona.

Stopnia rozdrobnienia

Jest to procent poza kolejnością zakresów stron liść sterty.Poza kolejnością fragment jest dla którego fragment zawiera bieżącą strona sterty nie jest fizycznie następnego fragment po fragment zawiera poprzedniej strona.

Wartość dla avg_fragmentation_in_percent powinien być jak najbliżej zero, jak to możliwe dla maksymalnej wydajności.Jednakże mogą być dopuszczalne wartości od 0 do 10 procent.Wszystkie metody zmniejszenia fragmentacji, takich jak odbudowy, reorganizacji lub odtwarzania, umożliwia zmniejszenie tych wartości.Aby uzyskać więcej informacji dotyczących sposobu analizowania stopień fragmentacji w indeksie, zobacz Reorganizowanie i odbudowa indeksów.

Gdy indeks jest pofragmentowany w sposób, że fragmentacji wpływ na wydajność kwerendy, dostępne są trzy opcje dla zmniejszenia fragmentacji:

• Usuwanie i ponowne utworzenie indeks klastrowany.

  Ponowne tworzenie indeks klastrowany rozkłada dane i wyniki w pełnych danych stron.poziom stopnia ukończenia można skonfigurować za pomocą opcji FILLFACTOR tworzenia indeksu.Wady w tej metoda są że indeks jest w trybie offline podczas upuszczania i ponownie utworzyć cykl, a operacja jest niepodzielny.Jeśli do tworzenia indeksu jest przerwana, nie jest utworzony indeks.Aby uzyskać więcej informacji, zobacz CREATE INDEX (Transact-SQL).

• Za pomocą zmiany INDEKSU reorganizacji, zastąpienie DBCC INDEXDEFRAG kolejności stronpoziom liśćindeksu w logicznej kolejności. Ponieważ jest to operacja online, indeks jest dostępny, instrukcja jest uruchomiona.Operacja jest przerwana, nie tracąc prace już wykonane.Wadą tej metoda jest, że nie ma jako dobra zadanie reorganizacji danych operacji odbudować indeks, a nie aktualizacja statystyki.

• Za pomocą zmiany INDEKSU ODBUDOWAĆ, zastąpienie DBCC DBREINDEX odbudować indeks w trybie online lub offline.Aby uzyskać więcej informacji, zobacz Zmiana INDEKSU (Transact-SQL).

Fragmentacja samodzielnie nie jest wystarczającym powodem do reorganizacji lub odbudowanie indeksu.Głównym skutkiem rozdrobnienia jest, że spowalnia niedziałający strona odczytu z wyprzedzeniem przepustowości podczas skanowania indeksu.Powoduje wolniejsze czasy odpowiedzi.Jeżeli obciążenia kwerendy fragmentacji tabela lub indeksu nie obejmuje skanowanie, ponieważ obciążenie pracą jest przede wszystkim singleton wyszukiwań, usuwanie fragmentacji może być żadnego efektu.Aby uzyskać więcej informacji, zobacz firmy Microsoft w witryna sieci Web.

Zmniejszenie fragment fragmentacji sterty, Utwórz indeks klastrowany w tabela , a następnie upuść indeksu.Rozkłada dane, podczas tworzenia indeks klastrowany .Również dzięki temu w optymalnej, jak to możliwe, biorąc pod uwagę dystrybucji wolnego miejsca w bazie danych.indeks klastrowany jest następnie przenoszony na ponownie utworzyć sterty, dane nie są przenoszone i optymalnie pozostaje w pozycji.Aby uzyskać informacje dotyczące sposobu wykonywania tych operacji, zobacz CREATE INDEX i DROP INDEKSU.

Przestroga
Tworzenie i upuszczając indeks klastrowany na tabela, buduje ponownie wszystkie indeksami nieklastrowanymi w tej tabela .

Domyślnie, zmieniać REORGANIZOWAĆ INDEKSU instrukcja kompaktuje stron zawierających dane dużych obiektów (LOB).Ponieważ LOB stron są nie dealokowane wtedy, kiedy jest to pusty, kompaktowanie danych może poprawić wykorzystanie miejsca na dysku Jeśli partie dane LOB został usunięty lub porzucone LOB kolumna .

Reorganizowanie określony indeks klastrowany Kompaktuje wszystkie kolumny LOB, które są zawarte w indeks klastrowany.Reorganizowanie indeks nieklastrowany Kompaktuje wszystkie kolumny LOB, które są kolumnami (dołączone) w indeksie.Gdy wszystko jest określona w instrukcja, wszystkie indeksy, które są skojarzone z określonej tabela lub widoku są zmieniono ich organizację.Ponadto wszystkie kolumny LOB, które są skojarzone z indeks klastrowany, podstawowej tabelalub indeks nieklastrowany z uwzględnionych kolumn są skompaktować.

avg_page_space_used_in_percentkolumna wskazuje strona stopnia ukończenia. Aby osiągnąć wykorzystania miejsca na dysku optymalnej, wartość ta powinna być zbliżona do 100 procent dla indeksu, który nie będzie miał wiele losowe wstawiane.Jednakże indeks, który ma wiele wstawia losowe i bardzo pełne strony mają zwiększoną liczbę podziałów strona .Powoduje to więcej fragmentacji.W związku z tym aby zmniejszyć podziały strona , wartość powinna być mniejsza niż 100 procent.Przebudowywanie indeksu z FILLFACTOR dzięki opcji określonego stopnia ukończenia strona ma zostać zmieniony aby dopasowanie wzorzec kwerendy w indeksie.Aby uzyskać więcej informacji o współczynnik wypełnienia, zobacz Współczynnik wypełnienia.Ponadto zmieniać REORGANIZOWAĆ INDEKSU będzie skompaktuj indeks próby wypełnienia stron FILLFACTOR, który został ostatnio określony.Zwiększa wartość w avg_space_used_in_percent.Należy zauważyć, że ZMIENIA REORGANIZOWAĆ INDEKSU nie można zmniejszyć strona stopnia ukończenia.Zamiast tego należy wykonać przebudowy indeksu.

Fragment składa się z fizycznie kolejnych liść stron w jednym pliku jednostka alokacji.Indeks ma co najmniej jeden fragment.Maksymalną fragmentów, które mogą mieć indeks jest równa Liczba stron liść poziom indeksu.Większe fragmenty oznacza, że mniej dysk, który we/wy jest wymagany do wczytania ten sam numer strony.Dlatego większych avg_fragment_size_in_pages wartość, tym lepiej zakres skanowania wydajności.avg_fragment_size_in_pages i avg_fragmentation_in_percent wartości jest odwrotnie proporcjonalne do siebie.W związku z tym odbudowy lub reorganizacji indeksu należy zredukować stopień fragmentacji i zwiększenie rozmiaru fragmentu.

Poniższy przykład zwraca rozmiar i rozdrobnienie statystyki wszystkie indeksy i partycji z Person.Address tabela w AdventureWorks2008R2 bazy danych.Tryb skanowania jest zestaw na 'LIMITED' Aby uzyskać najlepszą wydajność i ograniczyć statystyki, które są zwracane.Wykonywanie kwerendy tego wymaga, co najmniej uprawnienie Kontrola na Person.Address tabela.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.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

W przykładzie poniżej są zwracane wszystkie statystyki sterty dbo.DatabaseLog w AdventureWorks2008R2 bazy danych.Ponieważ tabela zawiera dane LOB, wiersz jest zwracana dla LOB_DATA jednostka alokacji oprócz wierszy zwracanych w celu IN_ROW_ALLOCATION_UNIT zapisuje dane strony sterty.Wykonywanie kwerendy tego wymaga, co najmniej uprawnienie Kontrola na dbo.DatabaseLog tabela.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.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

W przykładzie poniżej są zwracane wszystkie statystyki dla wszystkich tabel i indeksów w wystąpienie SQL Server przez określenie symbolu wieloznacznego NULL dla wszystkich parametrów.Wykonywanie kwerendy wymaga uprawnień WYŚWIETL stan serwera.

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

Poniższy przykład automatycznie Reorganizuje lub buduje ponownie wszystkie partycje w bazie danych, których fragmentacja średnia ponad 10 procent.Wykonywanie tej kwerendy wymaga uprawnienia Wyświetlanie stanu bazy danych.Określa, w tym przykładzie DB_ID jako pierwszy parametr bez określania nazwy bazy danych.Jeśli bieżąca baza danych zawiera zgodności poziom 80 lub niższy, zostanie wygenerowany błąd.Aby rozwiązać problem, należy zastąpić DB_ID() z nazwy prawidłowej bazy danych.Aby uzyskać więcej informacji na temat poziomów zgodności bazy danych, zobacz sp_dbcmptlevel (języka Transact-SQL).

-- 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
        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

Poniższy przykład pokazuje sposób wyświetlania i porównaj całkowitą liczbę stron wobec stron, które są wiersza i strona skompresowane.Informacja ta może służyć do określenia korzyści, zapewniającym kompresji dla indeksu lub tabela.

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
JOIN sys.objects o on o.object_id = ips.object_id
ORDER BY record_count DESC;