sys.dm_db_index_physical_stats (Transact-SQL)

The mode in which the function is executed determines the level of scanning performed to obtain the statistical data that is used by the function.mode is specified as LIMITED, SAMPLED, or DETAILED.The function traverses the page chains for the allocation units that make up the specified partitions of the table or index.sys.dm_db_index_physical_stats requires only an Intent-Shared (IS) table lock, regardless of the mode that it runs in.Aby uzyskać więcej informacji na temat blokowania Zobacz Tryby blokada.

Tryb LIMITED to najszybszy tryb i skanuje najmniejszą liczbę stron.Do indeksu są skanowane tylko strony poziomu nadrzędnego B-drzewo (to znaczy strony powyżej poziomu typu poziom liścia).Na stosie badane są tylko te skojarzone doskonałe utajnienie przekazywania i IAM strony; nie były skanowane strony danych sterty.W SQL Server 2005, wszystkie strony ze stosu są skanowane w trybie LIMITED.

W trybie LIMITED compressed_page_count ma wartość NULL, ponieważ Database Engine tylko skanowanie stron typu nie liść B-drzewo i IAM i doskonałe utajnienie przekazywania strony sterty. W trybie SAMPLED uzyskać wartość szacunkowa compressed_page_count i uzyskać wartość rzeczywista compressed_page_count za pomocą tryb szczegółowyTryb SAMPLED zwraca statystyki na podstawie próbki 1 procent wszystkich stron w indeksie lub sterty.Jeśli indeks lub sterty ma mniej niż 10 000 stron, zamiast SAMPLED używany jest tryb szczegółowy.

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

W trybach są stopniowo wolniejsze od LIMITED DETAILED, ponieważ więcej pracy odbywa się w każdym z trybów.Aby szybko ocenić poziom rozmiaru lub fragmentacji tabela lub indeksu, należy użyć trybu LIMITED.Jest najszybsze i nie będzie zwracać wiersz dla każdego poziom nonleaf w jednostce alokacji IN_ROW_DATA indeksu.

Można użyć Transact-SQL Funkcje DB_ID and OBJECT_ID , aby określić wartość database_id i object_id Parametry. Jednak przekazywanie wartości, które nie są prawidłowe, tych funkcji może spowodować niezamierzone rezultaty.Na przykład jeśli nie można odnaleźć nazwy bazy danych lub obiektu, ponieważ one nie istnieją lub są niepoprawnie wpisany, funkcjami zwróci wartość NULL.Funkcja sys.dm_db_index_physical_stats interpretuje wartość NULL jako symbol wieloznaczny wartości wszystkich baz danych lub wszystkie obiekty.

Ponadto funkcja OBJECT_ID są przetwarzane, zanim funkcja sys.dm_db_index_physical_stats nazywa się i dlatego jest oceniany w odniesieniu do bieżącej bazy danych, nie bazy danych określona w database_id. Zachowanie to może spowodować, że funkcja OBJECT_ID ma zostać zwrócona wartość NULL; lub, jeśli nazwa obiektu jest zarówno w bieżącym kontekście bazy danych, jak i w określonej bazy danych, komunikat o błędzie może być zwrócona.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'AdventureWorks'), 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 AdventureWorks;
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

Najważniejsze wskazówki

Fragmentacja występuje w procesie modyfikacji danych (instrukcje INSERT, UPDATE i DELETE), dokonane przed tabela, a w związku z tym, indeksów, zdefiniowane w tabela.Ponieważ te modyfikacje nie zazwyczaj umieszczone są równie wierszy tabela i indeksów, stopnia ukończenia każdej strona może się zmieniać w czasie.Dla kwerend skanowanie części lub całości indeksy tabela ten rodzaj fragmentacji może spowodować, że odczyty dodatkową strona.To przeszkadza równoległych skanowania danych.

Algorytm obliczania fragmentacji jest bardziej precyzyjne w SQL Server 2008 niż w SQL Server 2000. W wyniku wartości fragmentacji pojawi się 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 tym samym zakresie, ale nie strona 12. Jednak aby uzyskać dostęp do tych dwóch stron wymaga dwóch fizycznych operacji We/Wy, co to jest liczony jako fragmentacji w SQL Server 2008.

Poziom rozdrobnienia indeksu lub stosu jest wyświetlany kolumna avg_fragmentation_in_percent.Hałd wartość reprezentuje fragmentacji fragment sterty.Dla indeksów wartość reprezentuje logiczną fragmentacji indeksu.W odróżnieniu od SHOWCONTIG DBCC algorytmów obliczeń fragmentacji w obu przypadkach należy wziąć pod uwagę pamięci masowej, która obejmuje wiele plików i dlatego są dokładne.

Fragmentacja logicznych

Jest to procent poza kolejnością stron na stronach typu liść indeksu.strona poza kolejnością jest stroną, dla którego następnej strona fizyczne, przydzielonych do indeksu nie jest wskazywany przez pag następnej stronae wskaźnik na bieżącej stronie typu liść.

Fragmentacja fragment

Jest to procent poza kolejnością zakresów na stronach typu liść sterty.W zakresie poza kolejnością to dla których fragment zawierający bieżącą strona do stosu nie jest fizycznie następnego fragment po jakim zawiera poprzedniej strona.

Wartość dla avg_fragmentation_in_percent powinna być jak zbliżona do zera, jak to możliwe w celu uzyskania maksymalnej wydajności.Jednak wartości od 0 do 10 procent mogą być akceptowane.Wszystkie metody zmniejszenie fragmentacji, takie jak przebudowy, zmiana lub odtwarzania, można użyć do zmniejszenia tych wartości.Aby uzyskać więcej informacji dotyczących sposobu analizowania stopień fragmentacji w indeksie zobacz Reorganizing and Rebuilding Indexes.

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

• Usuwanie i ponowne utworzenie indeks klastrowany.

  Ponowne tworzenie indeks klastrowany rozkłada dane i powoduje stron pełnych danych.Poziom stopnia ukończenia można konfigurować za pomocą opcji FILLFACTOR w CREATE INDEX.Niedogodności tej metoda są czy indeks jest w trybie offline podczas listy rozwijanej i ponownie utworzyć cykl, i że operacja jest niepodzielny.Jeśli do tworzenia indeksu zostanie przerwana, indeks nie jest ponownie tworzony.Aby uzyskać więcej informacji zobaczCREATE INDEX (języka Transact-SQL).

• Aby zmienić kolejność strony poziomu poziom liścia indeksu w celu logicznego za pomocą instrukcji ALTER REORGANIZOWAĆ indeks, zastępujące INDEXDEFRAG DBCC.Ponieważ jest to operacja online, indeks jest dostępny podczas pracy w instrukcja.Operacja może być również przerwana bez utraty pracy już wykonanej.Wadą tej metoda jest, że go nie działa jak dobre zadanie z reorganizowanie danych operacji odbudować indeks, a nie powoduje aktualizacja statystyki.

• Użyć instrukcji ALTER ODBUDOWAĆ indeks, zastępujące DBCC DBREINDEX odbudować indeks w trybie online lub offline.Aby uzyskać więcej informacji zobaczALTER INDEX (języka Transact-SQL).

Fragmentacja samodzielnie nie jest wystarczający powód, aby zreorganizować lub odbudowanie indeksu.Głównym skutkiem fragmentacji jest, że go spowalnia strona wydajność odczytu z wyprzedzeniem podczas skanowania indeksu.Powoduje wolniejsze czasy odpowiedzi.Jeśli obciążenia kwerendy fragmentacji tabela lub indeksu nie pociąga za sobą skanowanie, ponieważ obciążenie pracą jest przede wszystkim pojedynczych wyszukiwań, usuwanie fragmentacji może miało żadnego efektu.Aby uzyskać więcej informacji zobacz temat to Witryny firmy Microsoft w sieci Web.

Aby zmniejszyć fragmentacji fragment sterty, tworzenie indeks klastrowany w tabela, a następnie upuść indeksu.Rozkłada dane, podczas gdy tworzony jest indeks klastrowany.Również ułatwia optymalne tak jak to możliwe, biorąc pod uwagę rozkład wolnego miejsca w bazie danych.Indeks klastrowany zostanie następnie następowało ponowne utworzenie sterty, dane nie są przenoszone i optymalnie pozostaje w miejscu.Aby uzyskać informacje dotyczące sposobu wykonania tych operacji zobacz TWORZENIE INDEKSU and USUWANIE INDEKSU.

Domyślnie w instrukcja ALTER REORGANIZOWAĆ indeks kompaktuje stron, które zawierają dane dużych obiektów (LOB).Ponieważ LOB stron nie są przydziałów, gdy pusta, kompaktowanie danych może poprawić wykorzystania miejsca na dysku partii dane LOB zostały usunięte, czy kolumna LOB zostało porzucone.

Reorganizacja określony indeks klastrowany Kompaktuje wszystkie kolumny LOB, znajdujących się w indeksie klastrowanym.Reorganizacja indeks nieklastrowany Kompaktuje wszystkie LOB kolumny nonkey (włączone) w indeksie.Gdy wszystko jest określona w instrukcja, zreorganizowana są wszystkie indeksy, które są skojarzone z określoną tabela lub widok.Ponadto wszystkie kolumny LOB, związane z indeksem klastrowanym, tabela podstawowej lub nieklastrowany indeks kolumny dołączone są skompaktować.

Kolumna avg_page_space_used_in_percent wskazuje strona stopnia ukończenia.Aby możliwe było wykorzystanie miejsca na dysku optymalne, wartość ta powinna być zbliżone do 100 procent dla indeksu, który nie będzie miał wiele losowych wstawia.Indeks, który ma wiele losowych wstawia bardzo pełne strona i konieczne będzie jednak zwiększenie liczby podziałów strona.Powoduje to, że więcej fragmentacji.Dlatego w celu zmniejszenia podziały strona, wartość powinna być mniejsza niż 100 procent.Przebudowywanie indeksu z FILLFACTOR określono opcję umożliwia stopnia ukończenia strona ma być zmieniony, aby dopasować wzorzec kwerendy w indeksie.Aby uzyskać więcej informacji na temat współczynnik wypełnienia zobacz Fill Factor. Ponadto ALTER REORGANIZOWAĆ indeks będzie skompaktuj indeks próby wypełnienia stron FILLFACTOR, ostatnio określona.Powoduje to zwiększenie wartości avg_space_used_in_percent.Należy zauważyć, że ALTER REORGANIZOWAĆ indeks nie może zmniejszyć strona stopnia ukończenia.Zamiast tego należy wykonać odbudowywania indeksu.

Fragment składa się z typu liść fizycznie kolejnych stron w jednym pliku dla jednostka alokacji.Indeks ma co najmniej jeden fragment.Maksymalna liczba fragmentów indeks może być są równe liczbę stron na poziomie poziom liścia indeksu.Większe fragmenty oznacza, że mniej dysku we/wy jest wymagany do wczytania taką samą liczbę stron.W związku z tym im większa wartość avg_fragment_size_in_pages, tym lepsza wydajność skanowania zakres.Wartości avg_fragment_size_in_pages i avg_fragmentation_in_percent są odwrotnie proporcjonalne do siebie.W związku z tym przebudowywanie lub zmiana indeksu należy zredukować stopień fragmentacji i zwiększenie rozmiaru fragmentu.

W poniższym przykładzie zwraca rozmiar i fragmentacji statystyki dla wszystkich indeksów i partycje o Person.Address Tabela w AdventureWorks Baza danych. Ustawienie trybu skanowania 'LIMITED' Aby uzyskać najlepszą wydajność oraz ograniczyć dane statystyczne, które są zwracane. Wykonywanie kwerendy tego wymaga, co najmniej uprawnienie Kontrola w Person.Address Tabela.

W poniższym przykładzie są zwracane wszystkie statystyki dla sterty dbo.DatabaseLog w AdventureWorks Baza danych. Ponieważ tabela zawiera dane LOB, wiersz jest zwracana dla jednostka alokacji LOB_DATA oprócz wiersza zwróciła IN_ROW_ALLOCATION_UNIT, które zapisuje dane na stronach sterty.Wykonywanie kwerendy tego wymaga, co najmniej uprawnienie Kontrola w dbo.DatabaseLog Tabela.

W poniższym przykładzie są zwracane wszystkie statystyki dla wszystkich tabel i indeksów w obrębie wystąpienie SQL Server określając symbol wieloznaczny NULL dla wszystkich parametrów. Wykonywanie kwerendy wymaga uprawnienia VIEW SERVER STATE.

W poniższym przykładzie automatycznie Reorganizuje lub buduje ponownie wszystkie partycje w bazie danych, które mają średnia fragmentacji ponad 10 procent.Wykonywanie kwerendy wymaga uprawnienia VIEW DATABASE STATE.W poniższym przykładzie określa DB_ID jako pierwszy parametr bez określania nazwy bazy danych.Jeśli bieżąca baza danych zawiera poziom zgodności, 80 lub niżej, zostanie wygenerowany błąd.Aby usunąć błąd, należy zastąpić DB_ID() prawidłową nazwę bazy danych.Aby uzyskać więcej informacji na temat poziomów zgodności bazy danych zobacz sp_dbcmptlevel (języka Transact-SQL).