sys.dm_db_index_physical_stats (Transact-SQL)

Článek
12/19/2024

platí pro:SQL Server Azure SQL Database azure SQL Managed Instance

Vrátí informace o velikosti a fragmentaci dat a indexů zadané tabulky nebo zobrazení v databázovém stroji SQL Serveru. Pro index se vrátí jeden řádek pro každou úroveň stromu B v každém oddílu. Pro haldu se vrátí jeden řádek pro IN_ROW_DATA alokační jednotku každého oddílu. U velkých dat objektu (LOB) se vrátí jeden řádek pro LOB_DATA alokační jednotku každého oddílu. Pokud data přetečení řádků v tabulce existují, vrátí se jeden řádek pro ROW_OVERFLOW_DATA alokační jednotku v každém oddílu.

Poznámka

Dokumentace používá termín B-tree obecně v odkazu na indexy. V indexech rowstore databázový stroj implementuje strom B+. To neplatí pro indexy columnstore ani indexy v tabulkách optimalizovaných pro paměť. Další informace najdete v SQL Serveru a architektuře indexu Azure SQL a průvodci návrhem.

sys.dm_db_index_physical_stats nevrací informace o indexech optimalizovaných pro paměť. Informace o použití indexu optimalizovaného pro paměť naleznete v tématu sys.dm_db_xtp_index_stats.

Pokud dotazujete sys.dm_db_index_physical_stats na instanci serveru, která je hostitelem skupiny dostupnosti čitelné sekundární repliky, může dojít k problému REDO blokování. Důvodem je to, že toto zobrazení dynamické správy získává zámek Intent-Shared (IS) u zadané uživatelské tabulky nebo zobrazení, které může blokovat požadavky pomocí vlákna REDO pro zámek výhradního uživatele (X) v této tabulce nebo zobrazení uživatele.

Transact-SQL konvence syntaxe

Syntax

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 | VÝCHOZÍ

ID databáze. database_id je malý. Platné vstupy jsou ID databáze, NULL, 0nebo DEFAULT. Výchozí hodnota je 0. NULL, 0a DEFAULT jsou v tomto kontextu ekvivalentní hodnoty.

Zadejte NULL k vrácení informací pro všechny databáze v instanci SQL Serveru. Pokud zadáte NULL pro database_id, musíte také zadat NULL pro object_id, index_ida partition_number.

Lze zadat předdefinovanou funkci DB_ID. Při použití DB_ID bez zadání názvu databáze musí být úroveň kompatibility aktuální databáze 90 nebo vyšší.

object_id | NULL | 0 | VÝCHOZÍ

ID objektu tabulky nebo zobrazení indexu je zapnuté. object_id je . Platné vstupy jsou ID tabulky a zobrazení, NULL, 0nebo DEFAULT. Výchozí hodnota je 0. NULL, 0a DEFAULT jsou v tomto kontextu ekvivalentní hodnoty.

V SQL Serveru 2016 (13.x) a novějších verzích zahrnují platné vstupy také název fronty zprostředkovatele služeb nebo název interní tabulky fronty fronty. Při použití výchozích parametrů (tj. všechny objekty, všechny indexy atd.), informace o fragmentaci pro všechny fronty jsou zahrnuty do sady výsledků.

Zadejte NULL pro vrácení informací pro všechny tabulky a zobrazení v zadané databázi. Pokud zadáte NULL pro object_id, musíte také zadat NULL pro index_id a partition_number.

index_id | 0 | NULL | -1 | VÝCHOZÍ

ID indexu. index_id je . Platné vstupy jsou ID indexu, 0 pokud je object_id haldou, NULL, -1nebo DEFAULT. Výchozí hodnota je -1. NULL, -1a DEFAULT jsou v tomto kontextu ekvivalentní hodnoty.

Zadejte NULL k vrácení informací pro všechny indexy základní tabulky nebo zobrazení. Pokud zadáte NULL pro index_id, musíte také zadat NULL pro partition_number.

partition_number | NULL | 0 | VÝCHOZÍ

Číslo oddílu v objektu. partition_number je . Platné vstupy jsou partition_number indexu nebo haldy, NULL, 0nebo DEFAULT. Výchozí hodnota je 0. NULL, 0a DEFAULT jsou v tomto kontextu ekvivalentní hodnoty.

Zadejte NULL pro vrácení informací pro všechny oddíly vlastnícího objektu.

partition_number je založená na 1. Nedílný index nebo halda má partition_number nastaven na 1.

režimu | NULL | VÝCHOZÍ

Název režimu. režim určuje úroveň kontroly, která se používá k získání statistiky. režim je sysname . Platné vstupy jsou DEFAULT, NULL, LIMITED, SAMPLEDnebo DETAILED. Výchozí (NULL) je LIMITED.

Vrácená tabulka

Název sloupce	Datový typ	Popis
`database_id`	malé	ID databáze tabulky nebo zobrazení Ve službě Azure SQL Database jsou hodnoty jedinečné v rámci jedné databáze nebo elastického fondu, ale ne v rámci logického serveru.
`object_id`	int	ID objektu tabulky nebo zobrazení, ve které je index zapnutý.
`index_id`	int	ID indexu `0` = halda.
`partition_number`	int	Číslo oddílu založené na 1 v rámci vlastnícího objektu; tabulka, zobrazení nebo index. `1` = nedílný index nebo halda.
`index_type_desc`	nvarchar(60)	Popis typu indexu: - `HEAP` - `CLUSTERED INDEX` - `NONCLUSTERED INDEX` - `PRIMARY XML INDEX` - `EXTENDED INDEX` - `XML INDEX` - `COLUMNSTORE MAPPING INDEX` (interní) - `COLUMNSTORE DELETEBUFFER INDEX` (interní) - `COLUMNSTORE DELETEBITMAP INDEX` (interní)
`alloc_unit_type_desc`	nvarchar(60)	Popis typu alokační jednotky: - `IN_ROW_DATA` - `LOB_DATA` - `ROW_OVERFLOW_DATA` Jednotka přidělení `LOB_DATA` obsahuje data uložená ve sloupcích textové, ntext, obrázku, varchar(max), nvarchar(max), varbinary(max)a xml. Další informace naleznete v tématu Datové typy. Jednotka přidělení `ROW_OVERFLOW_DATA` obsahuje data uložená ve sloupcích typu varchar(n), nvarchar(n), varbinary(n)a sql_variant, které jsou posunuty mimo řádek.
`index_depth`	tinyint	Počet úrovní indexu `1` = halda, `LOB_DATA` nebo `ROW_OVERFLOW_DATA` alokační jednotka.
`index_level`	tinyint	Aktuální úroveň indexu `0` pro úrovně listů indexu, haldy a `LOB_DATA` nebo `ROW_OVERFLOW_DATA` alokační jednotky. Větší než `0` pro neleafé úrovně indexu. index_level je nejvyšší na kořenové úrovni indexu. Neleafové úrovně indexů se zpracovávají pouze v režimu je `DETAILED`.
`avg_fragmentation_in_percent`	plovoucí	Logická fragmentace indexů nebo fragmentace rozsahu pro haldy v jednotce přidělování `IN_ROW_DATA`. Hodnota se měří jako procento a bere v úvahu více souborů. Definice logické a rozsahové fragmentace naleznete v tématu Poznámky. `0` pro `LOB_DATA` a `ROW_OVERFLOW_DATA` alokační jednotky. `NULL` pro haldy, když režim je `SAMPLED`.
`fragment_count`	bigint	Počet fragmentů na úrovni listu `IN_ROW_DATA` alokační jednotky Další informace o fragmentech naleznete v tématu Poznámky. `NULL` pro nelechotné úrovně indexu a `LOB_DATA` nebo `ROW_OVERFLOW_DATA` alokační jednotky. `NULL` pro haldy, když režim je `SAMPLED`.
`avg_fragment_size_in_pages`	plovoucí	Průměrný počet stránek v jednom fragmentu na úrovni listu `IN_ROW_DATA` alokační jednotky `NULL` pro nelechotné úrovně indexu a `LOB_DATA` nebo `ROW_OVERFLOW_DATA` alokační jednotky. `NULL` pro haldy, když režim je `SAMPLED`.
`page_count`	bigint	Celkový počet indexů nebo datových stránek Pro index celkový počet stránek indexu v aktuální úrovni stromu B v `IN_ROW_DATA` alokační jednotce. U haldy je celkový počet datových stránek v `IN_ROW_DATA` alokační jednotce. U `LOB_DATA` nebo `ROW_OVERFLOW_DATA` alokačních jednotek celkový počet stránek v alokační jednotce.
`avg_page_space_used_in_percent`	plovoucí	Průměrné procento dostupného prostoru úložiště dat použitého na všech stránkách Pro index platí průměr na aktuální úroveň stromu B v `IN_ROW_DATA` alokační jednotce. U haldy je průměr všech datových stránek v `IN_ROW_DATA` alokační jednotce. U `LOB_DATA` nebo `ROW_OVERFLOW_DATA` alokačních jednotek je průměr všech stránek v alokační jednotce. při režimu .
`record_count`	bigint	Celkový počet záznamů U indexu se celkový počet záznamů vztahuje na aktuální úroveň stromu B v `IN_ROW_DATA` alokační jednotce. U haldy je celkový počet záznamů v `IN_ROW_DATA` alokační jednotce. Poznámka: Pro haldu nemusí počet záznamů vrácených z této funkce odpovídat počtu řádků vrácených spuštěním `SELECT COUNT(*)` proti haldě. Důvodem je to, že řádek může obsahovat více záznamů. Například v některých situacích aktualizace může mít jeden řádek haldy záznam pro předávání a přesměrovaný záznam v důsledku operace aktualizace. Většina velkých obchodních řádků je také rozdělená do několika záznamů v `LOB_DATA` úložišti. U `LOB_DATA` nebo `ROW_OVERFLOW_DATA` alokačních jednotek celkový počet záznamů v celé alokační jednotce. při režimu .
`ghost_record_count`	bigint	Počet záznamů duchů připravených k odstranění úlohou čištění duchů v alokační jednotce. `0` pro neleafní úrovně indexu v `IN_ROW_DATA` alokační jednotce. při režimu .
`version_ghost_record_count`	bigint	Počet záznamů ghostů uchovávaných v nevyřízených transakcích izolace snímků v alokační jednotce. `0` pro neleafní úrovně indexu v `IN_ROW_DATA` alokační jednotce. při režimu .
`min_record_size_in_bytes`	int	Minimální velikost záznamu v bajtech Minimální velikost záznamu pro index platí pro aktuální úroveň stromu B ve `IN_ROW_DATA` alokační jednotce. U haldy je minimální velikost záznamu v `IN_ROW_DATA` alokační jednotce. U `LOB_DATA` nebo `ROW_OVERFLOW_DATA` alokačních jednotek je minimální velikost záznamu v úplné alokační jednotce. při režimu .
`max_record_size_in_bytes`	int	Maximální velikost záznamu v bajtech U indexu platí maximální velikost záznamu na aktuální úrovni stromu B v `IN_ROW_DATA` alokační jednotce. U haldy je maximální velikost záznamu v `IN_ROW_DATA` alokační jednotce. U `LOB_DATA` nebo `ROW_OVERFLOW_DATA` alokačních jednotek je maximální velikost záznamu v celé alokační jednotce. při režimu .
`avg_record_size_in_bytes`	plovoucí	Průměrná velikost záznamu v bajtech Pro index platí průměrná velikost záznamu na aktuální úrovni stromu B v `IN_ROW_DATA` alokační jednotce. U haldy je průměrná velikost záznamu v `IN_ROW_DATA` alokační jednotce. U `LOB_DATA` nebo `ROW_OVERFLOW_DATA` alokačních jednotek je průměrná velikost záznamu v celé alokační jednotce. při režimu .
`forwarded_record_count`	bigint	Počet záznamů v haldě, které mají ukazatele vpřed na jiné umístění dat. (K tomuto stavu dochází během aktualizace, pokud není dostatek místa pro uložení nového řádku do původního umístění.) `NULL` pro jakoukoli jinou alokační jednotku než `IN_ROW_DATA` alokační jednotky haldy. `NULL` pro haldy, když režim je `LIMITED`.
`compressed_page_count`	bigint	Počet komprimovaných stránek. U hald se nově přidělené stránky `PAGE` komprimovat. Haldu se `PAGE` komprimovat za dvou zvláštních podmínek: při hromadném importu dat nebo při vytvoření haldy. Typické operace DML, které způsobují, že přidělení stránek nejsou `PAGE` komprimované. Znovu sestavte haldu, když `compressed_page_count` hodnota roste větší než požadovaná prahová hodnota. U tabulek, které mají clusterovaný index, hodnota `compressed_page_count` označuje účinnost komprese `PAGE`.
`hobt_id`	bigint	ID haldy nebo B-stromu indexu nebo oddílu. U indexů columnstore se jedná o ID sady řádků, která sleduje interní data columnstore pro oddíl. Sady řádků se ukládají jako haldy dat nebo stromy B. Mají stejné ID indexu jako nadřazený index columnstore. Další informace najdete v tématu sys.internal_partitions.
`columnstore_delete_buffer_state`	tinyint	`0` = `NOT_APPLICABLE` `1` = `OPEN` `2` = `DRAINING` `3` = `FLUSHING` `4` = `RETIRING` `5` = `READY` platí pro: SQL Server 2016 (13.x) a novější verze, Azure SQL Database a Azure SQL Managed Instance
`columnstore_delete_buffer_state_desc`	nvarchar(60)	`NOT VALID` – nadřazený index není index columnstore. `OPEN` – odstraňovače a skenery to používají. `DRAINING` – odstraňovače se vyprázdní, ale skenery ho stále používají. `FLUSHING` – vyrovnávací paměť je uzavřena a řádky v vyrovnávací paměti se zapisují do rastrového obrázku pro odstranění. `RETIRING` – řádky v uzavřené vyrovnávací paměti pro odstranění byly zapsány do rastrového obrázku pro odstranění, ale vyrovnávací paměť nebyla zkrácena, protože skenery ji stále používají. Nové skenery nemusí používat vyřazující vyrovnávací paměť, protože otevřená vyrovnávací paměť je dostatečná. `READY` – tato vyrovnávací paměť pro odstranění je připravená k použití. platí pro: SQL Server 2016 (13.x) a novější verze, Azure SQL Database a Azure SQL Managed Instance
`version_record_count`	bigint	Toto je počet záznamů o verzích řádků, které se v tomto indexu spravují. Tyto verze řádků jsou udržovány funkcí zrychleného obnovení databáze. platí pro: SQL Server 2019 (15.x) a novější verze a Azure SQL Database
`inrow_version_record_count`	bigint	Počet záznamů verze ADR uložených v řádku dat pro rychlé načtení platí pro: SQL Server 2019 (15.x) a novější verze a Azure SQL Database
`inrow_diff_version_record_count`	bigint	Počet záznamů o verzích ADR uložených ve formě rozdílů od základní verze platí pro: SQL Server 2019 (15.x) a novější verze a Azure SQL Database
`total_inrow_version_payload_size_in_bytes`	bigint	Celková velikost v bajtech záznamů verze v řádku pro tento index platí pro: SQL Server 2019 (15.x) a novější verze a Azure SQL Database
`offrow_regular_version_record_count`	bigint	Počet záznamů verzí, které se uchovávají mimo původní řádek dat platí pro: SQL Server 2019 (15.x) a novější verze a Azure SQL Database
`offrow_long_term_version_record_count`	bigint	Počet záznamů verzí, které se považují za dlouhodobé. platí pro: SQL Server 2019 (15.x) a novější verze a Azure SQL Database

Poznámka

Poznámky

Funkce sys.dm_db_index_physical_stats dynamické správy nahrazuje příkaz DBCC SHOWCONTIG.

Režimy skenování

Režim, ve kterém je funkce spuštěna, určuje úroveň skenování provedenou k získání statistických dat, která funkce používá. režim je určen jako LIMITED, SAMPLEDnebo DETAILED. Funkce prochází řetězy stránek pro alokační jednotky, které tvoří zadané oddíly tabulky nebo indexu. sys.dm_db_index_physical_stats vyžaduje pouze zámek tabulky Intent-Shared (IS), a to bez ohledu na režim, ve kterém běží.

Režim LIMITED je nejrychlejší režim a prohledá nejmenší počet stránek. V případě indexu se prohledávají pouze stránky nadřazené úrovně stromu B (tj. stránky nad úrovní listu). U haldy se přidružené stránky PFS a IAM prověřují a datové stránky haldy se kontrolují v režimu LIMITED.

V režimu LIMITED je compressed_page_countNULL, protože databázový stroj prohledává pouze neleaf stránky stromu B a IAM a PFS haldy. Pomocí SAMPLED režimu získejte odhadovanou hodnotu pro compressed_page_counta pomocí režimu DETAILED získejte skutečnou hodnotu pro compressed_page_count. Režim SAMPLED vrátí statistiku na základě 1% vzorku všech stránek v indexu nebo haldě. Výsledky v režimu SAMPLED by měly být považovány za přibližné. Pokud má index nebo haldu méně než 10 000 stránek, použije se místo SAMPLEDrežim DETAILED .

Režim DETAILED prohledá všechny stránky a vrátí všechny statistiky.

Režimy jsou postupně pomalejší od LIMITED do DETAILED, protože v každém režimu se provádí více práce. K rychlému měření velikosti nebo úrovně fragmentace tabulky nebo indexu použijte režim LIMITED. Je to nejrychlejší a nevrací řádek pro každou nelechotnou úroveň v IN_ROW_DATA alokační jednotce indexu.

Určení hodnot parametrů pomocí systémových funkcí

Pomocí funkcí Transact-SQL DB_ID a OBJECT_ID můžete zadat hodnotu parametrů database_id a object_id. Předání hodnot, které nejsou platné pro tyto funkce, ale může způsobit nezamýšlené výsledky. Pokud například nelze najít název databáze nebo objektu, protože neexistují nebo jsou nesprávně napsané, vrátí obě funkce NULL. Funkce sys.dm_db_index_physical_stats interpretuje NULL jako hodnotu se zástupným znakem určující všechny databáze nebo všechny objekty.

Kromě toho se funkce OBJECT_ID zpracuje před zavolání funkce sys.dm_db_index_physical_stats a proto se vyhodnocuje v kontextu aktuální databáze, nikoli v databázi zadanou v database_id. Toto chování může způsobit, že funkce OBJECT_ID vrátí hodnotu NULL; nebo pokud název objektu existuje v aktuálním kontextu databáze i v zadané databázi, vrátí se chybová zpráva. Následující příklady ukazují tyto nezamýšlené výsledky.

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

Osvědčených

Vždy se ujistěte, že je vráceno platné ID při použití DB_ID nebo OBJECT_ID. Pokud například použijete OBJECT_ID, zadejte třídílný název, například OBJECT_ID(N'AdventureWorks2022.Person.Address'), nebo otestujte hodnotu vrácenou funkcemi předtím, než je použijete ve funkci sys.dm_db_index_physical_stats. Příklady A a B, které následují, ukazují bezpečný způsob určení ID databáze a objektů.

Detekce fragmentace

Fragmentace probíhá prostřednictvím procesu úprav dat (INSERT, UPDATEa DELETE příkazů), které jsou provedeny v tabulce, a proto indexy definované v tabulce. Vzhledem k tomu, že tyto úpravy nejsou obvykle distribuovány rovnoměrně mezi řádky tabulky a indexů, může se plná hodnota každé stránky v průběhu času lišit. U dotazů, které kontrolují část nebo všechny indexy tabulky, může tento druh fragmentace způsobit více čtení stránek, což brání paralelní kontrole dat.

Úroveň fragmentace indexu nebo haldy se zobrazuje ve sloupci avg_fragmentation_in_percent. U hald představuje hodnota rozsah fragmentace haldy. U indexů představuje hodnota logickou fragmentaci indexu. Na rozdíl od DBCC SHOWCONTIGzvažují výpočetní algoritmy fragmentace v obou případech úložiště, které zahrnuje více souborů, a proto jsou přesné.

Logická fragmentace

Toto je procento stránek mimo pořadí na listových stránkách indexu. Stránka mimo pořadí je stránka, na kterou další fyzická stránka přidělená indexu není stránkou, na kterou odkazuje další stránka ukazatel na aktuální stránce listu.

Fragmentace rozsahu

Toto je procento rozsahů mimo pořadí na listových stránkách haldy. Rozsah mimo pořadí je takový, pro který rozsah obsahující aktuální stránku haldy není fyzicky další rozsah za rozsahem, který obsahuje předchozí stránku.

Hodnota avg_fragmentation_in_percent by měla být co nejblíže nule pro maximální výkon. Hodnoty od 0 do 10 procent ale mohou být přijatelné. Ke snížení těchto hodnot je možné použít všechny metody snížení fragmentace, jako je opětovné sestavení, změna uspořádání nebo opětovné vytvoření. Další informace o tom, jak analyzovat stupeň fragmentace indexu, najdete v tématu Optimalizace údržby indexů za účelem zlepšení výkonu dotazů a snížení spotřeby prostředků.

Snížení fragmentace v indexu

Pokud je index fragmentován způsobem, který fragmentaci ovlivňuje výkon dotazů, existují tři možnosti pro snížení fragmentace:

Zahoďte a znovu vytvořte clusterovaný index.

Opětovné vytvoření clusterovaného indexu redistribuuje data a výsledky na úplných datových stránkách. Úroveň úplnosti lze nakonfigurovat pomocí možnosti FILLFACTOR v CREATE INDEX. Nevýhodou této metody je, že index je offline během poklesu a opětovného vytvoření cyklu a že operace je atomická. Pokud se vytváření indexu přeruší, index se znovu nevytvoří. Další informace naleznete v tématu CREATE INDEX.
Pokud chcete změnit pořadí stránek na úrovni listu indexu v logickém pořadí, použijte ALTER INDEX REORGANIZE, nahrazení DBCC INDEXDEFRAG. Vzhledem k tomu, že se jedná o online operaci, je index během spuštění příkazu k dispozici. Operace může být také přerušena, aniž by došlo ke ztrátě dokončené práce. Nevýhodou této metody je, že nedělá tak dobrou úlohu, jak změnit uspořádání dat jako operaci opětovného sestavení indexu a neaktualizuje statistiky.
K opětovnému sestavení indexu online nebo offline použijte ALTER INDEX REBUILD, nahrazení DBCC DBREINDEX. Další informace naleznete v tématuALTER INDEX (Transact-SQL).

Fragmentace sama o sobě není dostatečným důvodem k opětovnému uspořádání nebo opětovnému sestavení indexu. Hlavním účinkem fragmentace je, že během kontrol indexu zpomalí propustnost před čtením stránky. To způsobuje pomalejší doby odezvy. Pokud úloha dotazu v fragmentované tabulce nebo indexu nezahrnuje prohledávání, protože úloha je primárně jednoúčelové vyhledávání, odstranění fragmentace nemusí mít žádný vliv.

Poznámka

Spuštění DBCC SHRINKFILE nebo DBCC SHRINKDATABASE může zavádět fragmentaci, pokud se index během operace zmenšení částečně nebo úplně přesune. Proto pokud je nutné provést operaci zmenšení, měli byste to udělat před odstraněním fragmentace.

Snížení fragmentace haldy

Pokud chcete snížit míru fragmentace haldy, vytvořte v tabulce clusterovaný index a pak index vypusťte. Tím se redistribuují data při vytváření clusterovaného indexu. Díky tomu je také co nejoptimálnější vzhledem k distribuci volného místa dostupného v databázi. Po vyřazení clusterovaného indexu pro opětovné vytvoření haldy se data nepřesunou a zůstanou optimálně v pozici. Informace o provádění těchto operací naleznete v tématu CREATE INDEX a DROP INDEX.

Opatrnost

Vytvoření a vyřazení clusterovaného indexu v tabulce znovu sestaví všechny neclusterované indexy v této tabulce dvakrát.

Kompaktní data velkých objektů

Ve výchozím nastavení příkaz ALTER INDEX REORGANIZE komprimuje stránky, které obsahují velká data objektu (LOB). Protože obchodní stránky nejsou uvolněny, když jsou prázdná, komprimace těchto dat může zlepšit místo na disku, pokud je odstraněno velké množství obchodních dat nebo se odstraní sloupec LOB.

Změna uspořádání zadaného clusterovaného indexu zkomprimuje všechny sloupce LOB obsažené v clusterovaném indexu. Změna uspořádání neclusterovaného indexu zkomprimuje všechny sloupce LOB, které jsou v indexu bezklíčové (zahrnuté) sloupce. Pokud je v příkazu zadán ALL, všechny indexy přidružené k zadané tabulce nebo zobrazení jsou přeuspořádané. Kromě toho se komprimují všechny sloupce LOB přidružené ke clusterovému indexu, podkladové tabulce nebo neclusterovému indexu se zahrnutými sloupci.

Vyhodnocení využití místa na disku

Sloupec avg_page_space_used_in_percent označuje plný počet stránek. Aby se dosáhlo optimálního místa na disku, měla by být tato hodnota u indexu, který nemá mnoho náhodných vložení, blízko 100 %. Index, který má mnoho náhodných vložení a má velmi celé stránky, ale větší počet rozdělení stránek. To způsobuje větší fragmentaci. Proto aby bylo možné zmenšit rozdělení stránek, měla by být hodnota menší než 100 procent. Opětovné sestavení indexu se zadanou možností FILLFACTOR umožňuje změnit úplnou stránku tak, aby odpovídala vzoru dotazu na index. Další informace o faktoru vyplnění naleznete v tématu Určení faktoru vyplnění indexu. Také ALTER INDEX REORGANIZE zkomprimuje index tím, že se pokusí vyplnit stránky do FILLFACTOR, který byl naposledy zadán. Tím se zvýší hodnota v avg_space_used_in_percent. ALTER INDEX REORGANIZE nemůže zmenšit plnou stránku. Místo toho je nutné provést opětovné sestavení indexu.

Vyhodnocení fragmentů indexu

Fragment se skládá z fyzicky po sobě jdoucích listových stránek ve stejném souboru pro alokační jednotku. Index má alespoň jeden fragment. Maximální počet fragmentů, které index může mít, se rovná počtu stránek na úrovni listu indexu. Větší fragmenty znamenají, že ke čtení stejného počtu stránek se vyžaduje méně vstupně-výstupních operací disku. Čím větší je hodnota avg_fragment_size_in_pages, tím lepší je výkon prohledávání rozsahu. Hodnoty avg_fragment_size_in_pages a avg_fragmentation_in_percent jsou vzájemně inverzní. Proto by opětovné sestavení nebo změna uspořádání indexu mělo snížit velikost fragmentace a zvětšit velikost fragmentu.

Omezení

Nevrací data pro clusterované indexy columnstore.

Dovolení

Vyžaduje následující oprávnění:

CONTROL oprávnění k zadanému objektu v databázi.
VIEW DATABASE STATE nebo VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) oprávnění k vrácení informací o všech objektech v zadané databázi pomocí zástupných znaků objektu @object_id = NULL.
VIEW SERVER STATE nebo VIEW SERVER PERFORMANCE STATE (SQL Server 2022) oprávnění k vrácení informací o všech databázích pomocí @database_id = NULLse zástupným znakem databáze .

Udělení VIEW DATABASE STATE umožňuje vrátit všechny objekty v databázi bez ohledu na to, jaká oprávnění CONTROL u konkrétních objektů byla odepřena.

Odepření VIEW DATABASE STATE zakáže vrácení všech objektů v databázi bez ohledu na to, CONTROL oprávnění udělená pro konkrétní objekty. Pokud je zadán zástupný znak databáze @database_id = NULL, je databáze vynechána.

Další informace naleznete v tématu Zobrazení dynamické správy systému.

Příklady

Ukázky kódu Transact-SQL v tomto článku používají AdventureWorks2022 nebo AdventureWorksDW2022 ukázkovou databázi, kterou si můžete stáhnout z domovské stránky ukázky Microsoft SQL Serveru a projekty komunity.

A. Vrácení informací o zadané tabulce

Následující příklad vrátí statistiku velikosti a fragmentace pro všechny indexy a oddíly tabulky Person.Address. Režim kontroly je nastavený na LIMITED pro dosažení nejlepšího výkonu a omezení vrácených statistik. Provedení tohoto dotazu vyžaduje minimálně oprávnění CONTROL tabulky 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. Vrácení informací o haldě

Následující příklad vrátí všechny statistiky haldy dbo.DatabaseLog v databázi AdventureWorks2022. Vzhledem k tomu, že tabulka obsahuje obchodní data, vrátí se řádek pro jednotku přidělení LOB_DATA kromě řádku vráceného pro IN_ROW_ALLOCATION_UNIT, který ukládá datové stránky haldy. Provedení tohoto dotazu vyžaduje minimálně oprávnění CONTROL tabulky 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. Vrácení informací pro všechny databáze

Následující příklad vrátí všechny statistiky pro všechny tabulky a indexy v instanci SQL Serveru zadáním zástupného znaku NULL pro všechny parametry. Provedení tohoto dotazu vyžaduje oprávnění VIEW SERVER STATE.

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

D. Opětovné sestavení nebo změna uspořádání indexů pomocí sys.dm_db_index_physical_stats ve skriptu

Následující příklad automaticky reorganizuje nebo znovu sestaví všechny oddíly v databázi, které mají průměrnou fragmentaci nad 10 procent. Provedení tohoto dotazu vyžaduje oprávnění VIEW DATABASE STATE. Tento příklad určuje DB_ID jako první parametr bez zadání názvu databáze.

-- 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. Použití sys.dm_db_index_physical_stats k zobrazení počtu stránek komprimovaných stránek

Následující příklad ukazuje, jak zobrazit a porovnat celkový počet stránek s stránkami, které jsou řádky a stránky komprimované. Tyto informace lze použít k určení výhody, kterou komprese poskytuje pro index nebo tabulku.

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. Použití sys.dm_db_index_physical_stats v režimu SAMPLED

Následující příklad ukazuje, jak SAMPLED režim vrátí přibližnou hodnotu, která se liší od výsledků režimu 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. Dotazování na fronty zprostředkovatele služeb kvůli fragmentaci indexu

platí pro: SQL Server 2016 (13.x) a novější verze

Následující příklad ukazuje, jak dotazovat fronty zprostředkovatele serveru za účelem fragmentace.

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

zobrazení dynamické správy systému
zobrazení a funkce související s dynamickým řízením indexů (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)
referenční Transact-SQL (databázový stroj)

Sdílet prostřednictvím

sys.dm_db_index_physical_stats (Transact-SQL)

Syntax

Argumenty

database_id | NULL | 0 | VÝCHOZÍ

object_id | NULL | 0 | VÝCHOZÍ

index_id | 0 | NULL | -1 | VÝCHOZÍ

partition_number | NULL | 0 | VÝCHOZÍ

režimu | NULL | VÝCHOZÍ

Vrácená tabulka

Poznámky

Režimy skenování

Určení hodnot parametrů pomocí systémových funkcí

Osvědčených

Detekce fragmentace

Logická fragmentace

Fragmentace rozsahu

Snížení fragmentace v indexu

Snížení fragmentace haldy

Kompaktní data velkých objektů

Vyhodnocení využití místa na disku

Vyhodnocení fragmentů indexu

Omezení

Dovolení

Příklady

A. Vrácení informací o zadané tabulce

B. Vrácení informací o haldě

C. Vrácení informací pro všechny databáze

D. Opětovné sestavení nebo změna uspořádání indexů pomocí sys.dm_db_index_physical_stats ve skriptu

E. Použití sys.dm_db_index_physical_stats k zobrazení počtu stránek komprimovaných stránek

F. Použití sys.dm_db_index_physical_stats v režimu SAMPLED

G. Dotazování na fronty zprostředkovatele služeb kvůli fragmentaci indexu

Váš názor

Další materiály

Sdílet prostřednictvím

sys.dm_db_index_physical_stats (Transact-SQL)

Syntax

Argumenty

database_id | NULL | 0 | VÝCHOZÍ

object_id | NULL | 0 | VÝCHOZÍ

index_id | 0 | NULL | -1 | VÝCHOZÍ

partition_number | NULL | 0 | VÝCHOZÍ

režimu | NULL | VÝCHOZÍ

Vrácená tabulka

Poznámky

Režimy skenování

Určení hodnot parametrů pomocí systémových funkcí

Osvědčených

Detekce fragmentace

Logická fragmentace

Fragmentace rozsahu

Snížení fragmentace v indexu

Snížení fragmentace haldy

Kompaktní data velkých objektů

Vyhodnocení využití místa na disku

Vyhodnocení fragmentů indexu

Omezení

Dovolení

Příklady

A. Vrácení informací o zadané tabulce

B. Vrácení informací o haldě

C. Vrácení informací pro všechny databáze

D. Opětovné sestavení nebo změna uspořádání indexů pomocí sys.dm_db_index_physical_stats ve skriptu

E. Použití sys.dm_db_index_physical_stats k zobrazení počtu stránek komprimovaných stránek

F. Použití sys.dm_db_index_physical_stats v režimu SAMPLED

G. Dotazování na fronty zprostředkovatele služeb kvůli fragmentaci indexu

Související obsah

Váš názor

Další materiály