sys.dm_db_index_physical_stats (Transact-SQL)sys.dm_db_index_physical_stats (Transact-SQL)

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL AzureнетХранилище данных SQL AzureнетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Возвращает сведения о размере и фрагментации данных и индексов указанной таблицы или представления в SQL ServerSQL Server.Returns size and fragmentation information for the data and indexes of the specified table or view in SQL ServerSQL Server. Для индекса возвращается одна строка для каждого уровня сбалансированного дерева в каждой секции.For an index, one row is returned for each level of the B-tree in each partition. Для кучи возвращается одна строка для единицы распределения IN_ROW_DATA каждой секции.For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. Для данных больших объектов (LOB) возвращается одна строка для единицы распределения LOB_DATA каждой секции.For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. Если в таблице существуют данные с переполнением строки, то возвращается одна строка для единицы распределения ROW_OVERFLOW_DATA в каждой секции.If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition. Не возвращает информацию об индексах columnstore с оптимизированной памятью xVelocity.Does not return information about xVelocity memory optimized columnstore indexes.

Важно!

При запросе представления sys. DM _db_index_physical_stats на экземпляре сервера, на котором размещена Always On вторичная реплика для чтения, может возникнуть ошибка блокировки повтора.If you query sys.dm_db_index_physical_stats on a server instance that is hosting an Always On readable secondary replica, you might encounter a REDO blocking issue. Это связано с тем, что данное динамическое административное представление получает блокировку (IS) в указанной пользовательской таблице либо в представлении, которые могут блокировать запросы посредством потока REDO для монопольной блокировки (X) этой пользовательской таблицы или представления.This is because this dynamic management view acquires an IS lock on the specified user table or view that can block requests by a REDO thread for an X lock on that user table or view.

sys. DM _db_index_physical_stats не возвращает сведения о индексах, оптимизированных для памяти.sys.dm_db_index_physical_stats does not return information about memory-optimized indexes. Сведения об использовании индексов, оптимизированных для памяти, см. в разделе sys. DM _db_xtp_index_stats (Transact-SQL).For information about memory-optimized index use, see sys.dm_db_xtp_index_stats (Transact-SQL).

Значок ссылки на раздел Синтаксические обозначения в Transact-SQLTopic link icon Transact-SQL Syntax Conventions

Синтаксис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 }  
)  

АргументыArguments

database_id | NULL | 0 | ПАРАМЕТРЫdatabase_id | NULL | 0 | DEFAULT
Идентификатор базы данных.Is the ID of the database. database_id имеет smallint.database_id is smallint. Допустимыми входными значениями являются идентификатор базы данных, NULL, 0 или DEFAULT.Valid inputs are the ID number of a database, NULL, 0, or DEFAULT. Значение по умолчанию равно 0.The default is 0. В данном контексте значения NULL, 0 и DEFAULT эквивалентны.NULL, 0, and DEFAULT are equivalent values in this context.

Укажите значение NULL, чтобы вернуть сведения для всех баз данных в экземпляре SQL ServerSQL Server.Specify NULL to return information for all databases in the instance of SQL ServerSQL Server. Если для database_idуказано значение null, необходимо также указать значение NULL для object_id, index_idи partition_number.If you specify NULL for database_id, you must also specify NULL for object_id, index_id, and partition_number.

Можно указать встроенную функцию DB_ID .The built-in function DB_ID can be specified. Если функция DB_ID используется без указания имени базы данных, то уровень совместимости текущей базы данных должен быть равен 90 или выше.When using DB_ID without specifying a database name, the compatibility level of the current database must be 90 or greater.

object_id | NULL | 0 | ПАРАМЕТРЫobject_id | NULL | 0 | DEFAULT
Идентификатор объекта таблицы или представления, имеющего индекс.Is the object ID of the table or view the index is on. object_id имеет тип int.object_id is int.

Допустимыми входными значениями являются идентификатор таблицы, NULL, 0 или DEFAULT.Valid inputs are the ID number of a table and view, NULL, 0, or DEFAULT. Значение по умолчанию равно 0.The default is 0. В данном контексте значения NULL, 0 и DEFAULT эквивалентны.NULL, 0, and DEFAULT are equivalent values in this context. SQL Server 2016 (13.x)SQL Server 2016 (13.x)В допустимые входные данные также включают имя очереди компонента Service Broker или имя внутренней таблицы очереди.As of SQL Server 2016 (13.x)SQL Server 2016 (13.x), valid inputs also include the service broker queue name or the queue internal table name. Когда применяются параметры по умолчанию (т. е. все объекты, все индексы и т. д.), сведения о фрагментации для всех очередей включаются в результирующий набор.When default parameters are applied (i.e. all objects, all indexes, etc), fragmentation information for all queues are included in the result set.

Укажите значение NULL, чтобы вернуть данные для всех таблиц и представлений в указанной базе данных.Specify NULL to return information for all tables and views in the specified database. Если для object_idуказано значение null, необходимо также указать значение NULL для index_id и partition_number.If you specify NULL for object_id, you must also specify NULL for index_id and partition_number.

index_id | 0 | NULL | -1 | ПАРАМЕТРЫindex_id | 0 | NULL | -1 | DEFAULT
Идентификатор индекса.Is the ID of the index. Аргумент index_id имеет тип int. Допустимые входные данные — это ИДЕНТИФИКАЦИОНный номер индекса, 0, если object_id — куча, null,-1 или значение по умолчанию.index_id is int. Valid inputs are the ID number of an index, 0 if object_id is a heap, NULL, -1, or DEFAULT. Значение по умолчанию —-1.The default is -1. Значения NULL,-1 и DEFAULT являются эквивалентными значениями в этом контексте.NULL, -1, and DEFAULT are equivalent values in this context.

Укажите значение NULL, чтобы вернуть данные для всех индексов базовой таблицы или представления.Specify NULL to return information for all indexes for a base table or view. Если для аргумента index_idзадано значение null, необходимо также указать значение NULL для partition_number.If you specify NULL for index_id, you must also specify NULL for partition_number.

partition_number | NULL | 0 | ПАРАМЕТРЫpartition_number | NULL | 0 | DEFAULT
Номер секции в объекте.Is the partition number in the object. partition_number имеет тип int. Допустимыми входными значениями являются partion_number индекса, КУЧИ, null, 0 или Default.partition_number is int. Valid inputs are the partion_number of an index or heap, NULL, 0, or DEFAULT. Значение по умолчанию равно 0.The default is 0. В данном контексте значения NULL, 0 и DEFAULT эквивалентны.NULL, 0, and DEFAULT are equivalent values in this context.

Чтобы получить сведения обо всех секциях объекта, укажите значение NULL.Specify NULL to return information for all partitions of the owning object.

partition_number основан на 1.partition_number is 1-based. Несекционированный индекс или куча имеет значение partition_number , равное 1.A nonpartitioned index or heap has partition_number set to 1.

режим | NULL | ПАРАМЕТРЫmode | NULL | DEFAULT
Имя режима.Is the name of the mode. режим задает уровень сканирования, используемый для получения статистики.mode specifies the scan level that is used to obtain statistics. режим имеет тип sysname.mode is sysname. Допустимыми входными данными являются значения DEFAULT, NULL, LIMITED, SAMPLED и DETAILED.Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. Значение по умолчанию (NULL) соответствует значению LIMITED.The default (NULL) is LIMITED.

Возвращаемая таблицаTable Returned

Имя столбцаColumn name Тип данныхData type ОписаниеDescription
database_iddatabase_id smallintsmallint Идентификатор базы данных таблицы или представления.Database ID of the table or view.
object_idobject_id intint Идентификатор объекта таблицы или представления, для которых создан индекс.Object ID of the table or view that the index is on.
index_idindex_id intint Идентификатор индекса.Index ID of an index.

0 = куча.0 = Heap.
partition_numberpartition_number intint Номер секции объекта, значения начинаются с 1; для таблицы, представления или индекса.1-based partition number within the owning object; a table, view, or index.

1 = несекционированный индекс или куча.1 = Nonpartitioned index or heap.
index_type_descindex_type_desc nvarchar(60)nvarchar(60) Описание типа индекса:Description of the index type:

HEAPHEAP

CLUSTERED INDEXCLUSTERED INDEX

NONCLUSTERED INDEXNONCLUSTERED INDEX

PRIMARY XML INDEXPRIMARY XML INDEX

EXTENDED INDEXEXTENDED INDEX

XML INDEXXML INDEX

Индекс СОПОСТАВЛЕНИЯ COLUMNSTORE (внутренний)COLUMNSTORE MAPPING INDEX (internal)

Индекс COLUMNSTORE ДЕЛЕТЕБУФФЕР (внутренний)COLUMNSTORE DELETEBUFFER INDEX (internal)

Индекс COLUMNSTORE ДЕЛЕТЕБИТМАП (внутренний)COLUMNSTORE DELETEBITMAP INDEX (internal)
hobt_idhobt_id bigintbigint Идентификатор кучи или сбалансированного дерева индекса или секции.Heap or B-Tree ID of the index or partition.

Помимо возврата hobt_id для определяемых пользователем индексов, это также возвращает hobt_id внутренних индексов columnstore.Besides returning the hobt_id of user-defined indexes, this also returns the hobt_id of the internal columnstore indexes.
alloc_unit_type_descalloc_unit_type_desc nvarchar(60)nvarchar(60) Описание типа единицы распределения:Description of the allocation unit type:

IN_ROW_DATAIN_ROW_DATA

LOB_DATALOB_DATA

ROW_OVERFLOW_DATAROW_OVERFLOW_DATA

Единица распределения LOB_DATA содержит данные, которые хранятся в столбцах типа Text, ntext, Image, varchar (max) , nvarchar (max) , varbinary (max) и XML.The LOB_DATA allocation unit contains the data that is stored in columns of type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml. Дополнительные сведения см. в разделе Типы данных (Transact-SQL).For more information, see Data Types (Transact-SQL).

Единица распределения ROW_OVERFLOW_DATA содержит данные, которые хранятся в столбцах типа varchar (n) , nvarchar (n) , varbinary (n) и sql_variant , которые были переданы вне строки.The ROW_OVERFLOW_DATA allocation unit contains the data that is stored in columns of type varchar(n), nvarchar(n), varbinary(n), and sql_variant that have been pushed off-row.
index_depthindex_depth tinyinttinyint Количество уровней индекса.Number of index levels.

1 = куча или единица распределения LOB_DATA или ROW_OVERFLOW_DATA.1 = Heap, or LOB_DATA or ROW_OVERFLOW_DATA allocation unit.
index_levelindex_level tinyinttinyint Текущий уровень индекса.Current level of the index.

0 для конечного уровня индекса, для кучи и единиц распределения LOB_DATA или ROW_OVERFLOW_DATA.0 for index leaf levels, heaps, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

Значения больше 0 соответствуют неконечным уровням индекса.Greater than 0 for nonleaf index levels. index_level будет самым высоким на корневом уровне индекса.index_level will be the highest at the root level of an index.

Неконечные уровни индексов обрабатываются только в том случае, если mode = Detailed.The nonleaf levels of indexes are only processed when mode = DETAILED.
avg_fragmentation_in_percentavg_fragmentation_in_percent floatfloat Логическая фрагментация для индексов или фрагментация экстентов для куч в единице распределения IN_ROW_DATA.Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

Значение измеряется в процентах и учитывает несколько файлов.The value is measured as a percentage and takes into account multiple files. Определения логической фрагментации и фрагментации экстентов см. в разделе «Замечания».For definitions of logical and extent fragmentation, see Remarks.

0 для единиц распределения LOB_DATA и ROW_OVERFLOW_DATA.0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units.

Значение NULL для куч, если mode = SAMPLED.NULL for heaps when mode = SAMPLED.
fragment_countfragment_count bigintbigint Количество фрагментов на конечном уровне единицы распределения IN_ROW_DATA.Number of fragments in the leaf level of an IN_ROW_DATA allocation unit. Дополнительные сведения о фрагментах см. в разделе «Замечания».For more information about fragments, see Remarks.

NULL для неконечных уровней индекса и единиц распределения LOB_DATA или ROW_OVERFLOW_DATA.NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

Значение NULL для куч, если mode = SAMPLED.NULL for heaps when mode = SAMPLED.
avg_fragment_size_in_pagesavg_fragment_size_in_pages floatfloat Среднее количество страниц в одном фрагменте на конечном уровне единицы распределения IN_ROW_DATA.Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit.

NULL для неконечных уровней индекса и единиц распределения LOB_DATA или ROW_OVERFLOW_DATA.NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

Значение NULL для куч, если mode = SAMPLED.NULL for heaps when mode = SAMPLED.
page_countpage_count bigintbigint Общее количество страниц индекса или данных.Total number of index or data pages.

Для индекса — общее количество страниц индекса на текущем уровне сбалансированного дерева в единице распределения IN_ROW_DATA.For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit.

Для кучи — общее количество страниц данных в единице распределения IN_ROW_DATA.For a heap, the total number of data pages in the IN_ROW_DATA allocation unit.

Для единиц распределения LOB_DATA или ROW_OVERFLOW_DATA — общее количество страниц в единице распределения.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, total number of pages in the allocation unit.
avg_page_space_used_in_percentavg_page_space_used_in_percent floatfloat Средний процент доступного места для хранения данных, используемого всеми страницами.Average percentage of available data storage space used in all pages.

Для индекса усреднение применяется к текущему уровню сбалансированного дерева в единице распределения IN_ROW_DATA.For an index, average applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

Для кучи — среднее значение для всех страниц данных в единице распределения IN_ROW_DATA.For a heap, the average of all data pages in the IN_ROW_DATA allocation unit.

Для единиц распределения LOB_DATA или ROW_OVERFLOW_DATA — среднее значение для всех страниц в единице распределения.For LOB_DATA or ROW_OVERFLOW DATA allocation units, the average of all pages in the allocation unit.

NULL, если mode = Limited.NULL when mode = LIMITED.
record_countrecord_count bigintbigint Общее количество записей.Total number of records.

Для индекса общее количество записей применяется к текущему уровню сбалансированного дерева в единице распределения IN_ROW_DATA.For an index, total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

Для кучи — общее количество записей в единице распределения IN_ROW_DATA.For a heap, the total number of records in the IN_ROW_DATA allocation unit.

Примечание. Для кучи число записей, возвращаемых этой функцией, может не совпадать с количеством строк, возвращаемых при выполнении SELECT COUNT (*) в куче.Note: For a heap, the number of records returned from this function might not match the number of rows that are returned by running a SELECT COUNT(*) against the heap. Это происходит потому, что строка может содержать несколько записей.This is because a row may contain multiple records. Например, при обновлении одна строка кучи может иметь указывающую запись и перенаправленную запись как результат операции обновления.For example, under some update situations, a single heap row may have a forwarding record and a forwarded record as a result of the update operation. Также большинство больших LOB-строк разбиты на различные записи в хранилище LOB_DATA.Also, most large LOB rows are split into multiple records in LOB_DATA storage.

Для единиц распределения LOB_DATA или ROW_OVERFLOW_DATA — общее количество записей во всей единице распределения.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the total number of records in the complete allocation unit.

NULL, если mode = Limited.NULL when mode = LIMITED.
ghost_record_countghost_record_count bigintbigint Количество фантомных записей в единице распределения, готовых к удалению задачей очистки фантомных записей.Number of ghost records ready for removal by the ghost cleanup task in the allocation unit.

0 для неконечных уровней индекса в единице распределения IN_ROW_DATA.0 for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

NULL, если mode = Limited.NULL when mode = LIMITED.
version_ghost_record_countversion_ghost_record_count bigintbigint Количество фантомных записей, сохраняемых в единице распределения необработанной транзакцией изоляции моментального снимка.Number of ghost records retained by an outstanding snapshot isolation transaction in an allocation unit.

0 для неконечных уровней индекса в единице распределения IN_ROW_DATA.0 for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

NULL, если mode = Limited.NULL when mode = LIMITED.
min_record_size_in_bytesmin_record_size_in_bytes intint Минимальный размер записи в байтах.Minimum record size in bytes.

Для индекса минимальный размер записи применяется к текущему уровню сбалансированного дерева в единице распределения IN_ROW_DATA.For an index, minimum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

Для кучи — минимальный размер записи в единице распределения IN_ROW_DATA.For a heap, the minimum record size in the IN_ROW_DATA allocation unit.

Для единиц распределения LOB_DATA или ROW_OVERFLOW_DATA — минимальный размер записи во всей единице распределения.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the minimum record size in the complete allocation unit.

NULL, если mode = Limited.NULL when mode = LIMITED.
max_record_size_in_bytesmax_record_size_in_bytes intint Максимальный размер записи в байтах.Maximum record size in bytes.

Для индекса максимальный размер записи применяется к текущему уровню сбалансированного дерева в единице распределения IN_ROW_DATA.For an index, the maximum record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

Для кучи — максимальный размер записи в единице распределения IN_ROW_DATA.For a heap, the maximum record size in the IN_ROW_DATA allocation unit.

Для единиц распределения LOB_DATA или ROW_OVERFLOW_DATA — максимальный размер записи во всей единице распределения.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the maximum record size in the complete allocation unit.

NULL, если mode = Limited.NULL when mode = LIMITED.
avg_record_size_in_bytesavg_record_size_in_bytes floatfloat Средний размер записи в байтах.Average record size in bytes.

Для индекса средний размер записи применяется к текущему уровню сбалансированного дерева в единице распределения IN_ROW_DATA.For an index, the average record size applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

Для кучи — средний размер записи в единице распределения IN_ROW_DATA.For a heap, the average record size in the IN_ROW_DATA allocation unit.

Для единиц распределения LOB_DATA или ROW_OVERFLOW_DATA — средний размер записи во всей единице распределения.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the average record size in the complete allocation unit.

NULL, если mode = Limited.NULL when mode = LIMITED.
forwarded_record_countforwarded_record_count bigintbigint Количество записей в куче, содержащих указатели на данные в других местах.Number of records in a heap that have forward pointers to another data location. (Такое состояние возникает во время обновления, когда не хватает места для сохранения новой строки в исходном расположении.)(This state occurs during an update, when there is not enough room to store the new row in the original location.)

NULL для любой единицы распределения, отличающейся от единиц распределения IN_ROW_DATA для кучи.NULL for any allocation unit other than the IN_ROW_DATA allocation units for a heap.

Значение NULL для куч, если mode = Limited.NULL for heaps when mode = LIMITED.
compressed_page_countcompressed_page_count bigintbigint Количество сжатых страниц.The number of compressed pages.

Вновь выделенные для куч страницы не сжаты с использованием сжатия PAGE.For heaps, newly allocated pages are not PAGE compressed. Куча — это СТРАНИЦА, сжимаемая при наступлении двух особых условий: при массовом импорте данных или при перестройке кучи.A heap is PAGE compressed under two special conditions: when data is bulk imported or when a heap is rebuilt. Типичные операции DML, которые вызывают выделение страниц, не связаны со сжатием PAGE.Typical DML operations that cause page allocations will not be PAGE compressed. Перестройте кучу, если значение compressed_page_count увеличивается сверх желательного порога.Rebuild a heap when the compressed_page_count value grows larger than the threshold you want.

Для таблиц с кластеризованным индексом значение compressed_page_count указывает эффективность сжатия страниц.For tables that have a clustered index, the compressed_page_count value indicates the effectiveness of PAGE compression.
hobt_idhobt_id BIGINTbigint Область применения: SQL ServerSQL Server (начиная сSQL Server 2016 (13.x)SQL Server 2016 (13.x) до текущей версии), База данных SQL AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), База данных SQL AzureAzure SQL Database.

Только для индексов columnstore это идентификатор набора строк, который отслеживает внутренние данные columnstore для секции.For columnstore indexes only, this is the ID for a rowset that tracks internal columnstore data for a partition. Наборы строк хранятся в виде куч данных или двоичных деревьев.The rowsets are stored as data heaps or binary trees. Они имеют тот же идентификатор индекса, что и родительский индекс columnstore.They have the same index ID as the parent columnstore index. Дополнительные сведения см. в разделе sys. (INTERNAL_PARTITIONS Transact-)SQL.For more information, see sys.internal_partitions (Transact-SQL).

NULL, еслиNULL if
column_store_delete_buffer_statecolumn_store_delete_buffer_state tinyinttinyint Область применения: SQL ServerSQL Server (начиная сSQL Server 2016 (13.x)SQL Server 2016 (13.x) до текущей версии), База данных SQL AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), База данных SQL AzureAzure SQL Database.

0 = NOT_APPLICABLE0 = NOT_APPLICABLE

1 = OPEN;1 = OPEN

2 = СТОК2 = DRAINING

3 = ОЧИСТКА3 = FLUSHING

4 = СНЯТИЕ С УЧЕТА4 = RETIRING

5 = ГОТОВО5 = READY
column_store_delete_buff_state_desccolumn_store_delete_buff_state_desc Область применения: SQL ServerSQL Server (начиная сSQL Server 2016 (13.x)SQL Server 2016 (13.x) до текущей версии), База данных SQL AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), База данных SQL AzureAzure SQL Database.

Недопустимый — родительский индекс не является индексом columnstore.NOT VALID -the parent index is not a columnstore index.

Это используется с помощью открытых и удаляемых сканеров.OPEN - deleters and scanners use this.

Сток — удаляются, но сканеры все еще используют его.DRAINING - deleters are draining out but scanners still use it.

Очистка буфера закрывается, а строки в буфере записываются в точечный рисунок удаления.FLUSHING - buffer is closed and rows in the buffer are being written to the delete bitmap.

Снятие с учета-строки в закрытом буфере удаления записываются в битовую карту удаления, но буфер не был усечен, так как сканеры все еще используют его.RETIRING - rows in the closed delete buffer have been written to the delete bitmap, but the buffer has not been truncated because scanners are still using it. Новым сканерам не нужно использовать буфер снятия с учета, поскольку достаточно свободного места в открытом буфере.New scanners don't need to use the retiring buffer because the open buffer is enough.

Готово — этот буфер удаления готов к использованию.READY - This delete buffer is ready for use.

ПримечанияRemarks

Функция динамического управления sys.dm_db_index_physical_stats заменяет инструкцию DBCC SHOWCONTIG.The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement.

Режимы просмотраScanning Modes

Режим, в котором выполняется функция, определяет уровень просмотра для получения статистических данных, используемых функцией.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 требуется только блокировка с намерением общего доступа (—), независимо от режима, в котором она выполняется.sys.dm_db_index_physical_stats requires only an Intent-Shared (IS) table lock, regardless of the mode that it runs in.

Режим LIMITED является самым быстрым, в нем производится наименьшее число просмотров страниц.The LIMITED mode is the fastest mode and scans the smallest number of pages. Для индекса просматриваются только страницы родительского уровня в сбалансированном дереве (то есть страницы, расположенные выше конечного уровня).For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. Для кучи просматриваются только связанные PFS- и IAM-страницы. Страницы данных в куче просматриваются в режиме LIMITED.For a heap, the associated PFS and IAM pages are examined and the data pages of a heap are scanned in LIMITED mode.

В режиме LIMITED счетчик compressed_page_count имеет значение NULL, поскольку компонент Компонент Database EngineDatabase Engine просматривает только неконечные страницы сбалансированного дерева, а также IAM- и PFS-страницы кучи.With LIMITED mode, compressed_page_count is NULL because the Компонент Database EngineDatabase Engine only scans non-leaf pages of the B-tree and the IAM and PFS pages of the heap. Используйте режим ВЫБОРки, чтобы получить оценочное значение для compressed_page_count и использовать ПОДРОБНЫй режим для получения фактического значения для compressed_page_count.Use SAMPLED mode to get an estimated value for compressed_page_count, and use DETAILED mode to get the actual value for compressed_page_count. В режиме SAMPLED возвращается статистика на основе 1-процентной выборки всех страниц в индексе или куче.The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. Результаты в режиме SAMPLED следует рассматривать как приблизительные.Results in SAMPLED mode should be regarded as approximate. Если в индексе или куче менее 10 000 страниц, вместо режима SAMPLED используется режим DETAILED.If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.

В режиме DETAILED проводится просмотр всех страниц и возвращается вся статистика.The DETAILED mode scans all pages and returns all statistics.

Режимы характеризуются снижением скорости, начиная с LIMITED и заканчивая DETAILED, т. к. в каждом последующем режиме этой последовательности выполняется все больший объем работы.The modes are progressively slower from LIMITED to DETAILED, because more work is performed in each mode. Для быстрого измерения уровня фрагментации таблицы или индекса используйте режим LIMITED.To quickly gauge the size or fragmentation level of a table or index, use the LIMITED mode. Это самый быстрый режим, и для неконечных уровней индекса в единице распределения IN_ROW_DATA строки в нем не возвращаются.It is the fastest and will not return a row for each nonleaf level in the IN_ROW_DATA allocation unit of the index.

Использование системных функций для указания значений параметровUsing System Functions to Specify Parameter Values

Transact-SQLTransact-SQL Можно использовать функции DB_ID и object_id , чтобы указать значения для параметров database_id и object_id .You can use the Transact-SQLTransact-SQL functions DB_ID and OBJECT_ID to specify a value for the database_id and object_id parameters. Однако передавая значения, которые не допустимы для этих функций, можно получить неожиданные результаты.However, passing values that are not valid to these functions may cause unintended results. Например, если имя базы данных или объекта не могут быть найдены из-за того, что объект или база данных не существуют или соответствующее имя указано неверно, обе функции возвращают NULL.For example, if the database or object name cannot be found because they do not exist or are spelled incorrectly, both functions will return NULL. Функция sys.dm_db_index_physical_stats интерпретирует NULL как значение шаблона, задающее все базы данных или все объекты.The sys.dm_db_index_physical_stats function interprets NULL as a wildcard value specifying all databases or all objects.

Кроме того, функция OBJECT_ID обрабатывается до вызова функции sys. DM _db_index_physical_stats и, следовательно, вычисляется в контексте текущей базы данных, а не в базе данных, указанной в таблице database_id.Additionally, the OBJECT_ID function is processed before the sys.dm_db_index_physical_stats function is called and is therefore evaluated in the context of the current database, not the database specified in database_id. Это поведение может привести к тому, что функция OBJECT_ID возвратит значение NULL или, если имя объекта существует в контексте как текущей, так и указанной базы данных, возвратит сообщение об ошибке.This behavior may cause the OBJECT_ID function to return a NULL value; or, if the object name exists in both the current database context and the specified database, an error message may be returned. В следующих примерах демонстрируются эти неожиданные результаты.The following examples demonstrate these unintended results.

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

РекомендацииBest Practice

Всегда следует проверять, что функции DB_ID и OBJECT_ID возвращают допустимый идентификатор.Always make sure that a valid ID is returned when you use DB_ID or OBJECT_ID. Например, при использовании object_id укажите имя из трех частей OBJECT_ID(N'AdventureWorks2012.Person.Address'), например, или проверьте значение, возвращаемое функциями, прежде чем использовать их в функции sys. DM _db_index_physical_stats.For example, when you use OBJECT_ID, specify a three-part name such as OBJECT_ID(N'AdventureWorks2012.Person.Address'), or test the value returned by the functions before you use them in the sys.dm_db_index_physical_stats function. В примерах а и B, приведенных ниже, демонстрируется надежный способ указания идентификаторов базы данных и объекта.Examples A and B that follow demonstrate a safe way to specify database and object IDs.

Выявление фрагментацииDetecting Fragmentation

Фрагментация возникает в процессе изменений данных (инструкциями INSERT, UPDATE и DELETE), выполняемых на таблице и, следовательно, в индексах, определенных для таблицы.Fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) that are made against the table and, therefore, to the indexes defined on the table. Так как эти изменения обычно не распределяются равномерно по строкам таблицы и индекса, заполненность каждой страницы со временем может меняться.Because these modifications are not ordinarily distributed equally among the rows of the table and indexes, the fullness of each page can vary over time. Для запросов, выполняющих просмотр части или всех индексов таблицы, этот вид фрагментации может приводить к чтению дополнительных страниц.For queries that scan part or all of the indexes of a table, this kind of fragmentation can cause additional page reads. Это затрудняет параллельный просмотр данных.This hinders parallel scanning of data.

Уровень фрагментации индекса или кучи показан в столбце avg_fragmentation_in_percent.The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. Для куч это значение соответствует фрагментации экстентов.For heaps, the value represents the extent fragmentation of the heap. Для индексов это значение соответствует логической фрагментации.For indexes, the value represents the logical fragmentation of the index. В отличие от инструкции DBCC SHOWCONTIG, алгоритмы вычисления фрагментации в обоих случаях учитывают место для хранения нескольких файлов и поэтому являются точными.Unlike DBCC SHOWCONTIG, the fragmentation calculation algorithms in both cases consider storage that spans multiple files and, therefore, are accurate.

Логическая фрагментацияLogical Fragmentation

Это процент неупорядоченных страниц конечного уровня индекса.This is the percentage of out-of-order pages in the leaf pages of an index. Неупорядоченной называется страница, для которой следующая физическая страница, выделенная для индекса, не является страницей, на которую ссылается указатель следующей страницы в текущей конечной странице.An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.

Фрагментация экстентовExtent Fragmentation

Это процент неупорядоченных экстентов на конечном уровне кучи.This is the percentage of out-of-order extents in the leaf pages of a heap. Неупорядоченным называется такой экстент, для которого экстент, содержащий текущую страницу кучи, не расположен физически непосредственно за кластером, содержащим предыдущую страницу.An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

Для обеспечения наибольшей производительности значение аргумента avg_fragmentation_in_percent должно быть как можно более близким к нулю.The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. Но могут быть приемлемыми значения от 0 до 10 процентов.However, values from 0 percent through 10 percent may be acceptable. Для снижения этих значений могут использоваться любые методы снижения фрагментации, такие как перестройка, реорганизация или повторное создание.All methods of reducing fragmentation, such as rebuilding, reorganizing, or re-creating, can be used to reduce these values. Дополнительные сведения о том, как анализировать степень фрагментации в индексе, см. в разделе реорганизация и перестроение индексов.For more information about how to analyze the degree of fragmentation in an index, see Reorganize and Rebuild Indexes.

Снижение фрагментации в индексеReducing Fragmentation in an Index

Если индекс становится фрагментирован настолько, что это влияет на производительность запросов, для снижения фрагментации есть три возможности.When an index is fragmented in a way that the fragmentation is affecting query performance, there are three choices for reducing fragmentation:

  • Удаление и повторное создание кластеризованного индекса.Drop and re-create the clustered index.

    Повторное создание кластеризованного индекса перераспределяет данные и приводит к полному заполнению страниц данных.Re-creating a clustered index redistributes the data and results in full data pages. Уровень заполнения можно настроить с помощью параметра FILLFACTOR инструкции CREATE INDEX.The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX. Недостатком этого метода является то, что в цикле удаления и повторного создания индекс находится в автономном режиме, а также то, что эта операция является атомарной.The drawbacks in this method are that the index is offline during the drop and re-create cycle, and that the operation is atomic. Если создание индекса прервано, он не создается заново.If the index creation is interrupted, the index is not re-created. Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL).For more information, see CREATE INDEX (Transact-SQL).

  • Использование инструкции ALTER INDEX REORGANIZE, заменившей DBCC INDEXDEFRAG, для переупорядочения страниц индекса конечного уровня в логическом порядке.Use ALTER INDEX REORGANIZE, the replacement for DBCC INDEXDEFRAG, to reorder the leaf level pages of the index in a logical order. Так как эта операция выполняется в режиме «в сети», во время выполнения инструкции индекс доступен.Because this is an online operation, the index is available while the statement is running. Кроме того, операция может быть прервана без потери уже выполненной работы.The operation can also be interrupted without losing work already completed. Недостатком этого метода является то, что он не так хорошо выполняет реорганизацию данных, как операция перестроения индекса, и не обновляет статистику.The drawback in this method is that it does not do as good a job of reorganizing the data as an index rebuild operation, and it does not update statistics.

  • Использование инструкции ALTER INDEX REBUILD, заменившей DBCC DBREINDEX, для перестроения индекса, как «в сети», так и в режиме «вне сети».Use ALTER INDEX REBUILD, the replacement for DBCC DBREINDEX, to rebuild the index online or offline. Дополнительные сведения см. в разделе ALTER INDEX (Transact-SQL).For more information, see ALTER INDEX (Transact-SQL).

Фрагментация сама по себе не является достаточной причиной реорганизации или перестроения индекса.Fragmentation alone is not a sufficient reason to reorganize or rebuild an index. Основной эффект фрагментации заключается в том, что она замедляет упреждающее чтение во время просмотра индекса.The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. В результате этого увеличивается время ответа.This causes slower response times. Если запрос к фрагментированным таблице или индексу не предусматривает просмотра, потому что в основном выполняются единичные уточняющие запросы, устранение фрагментации может не дать никакого эффекта.If the query workload on a fragmented table or index does not involve scans, because the workload is primarily singleton lookups, removing fragmentation may have no effect.

Примечание

Выполнение инструкции DBCC SHRINKFILE или DBCC SHRINKDATABASE может вызвать фрагментацию, если индекс частично или полностью перемещается во время операции сжатия.Running DBCC SHRINKFILE or DBCC SHRINKDATABASE may introduce fragmentation if an index is partly or completely moved during the shrink operation. Поэтому, если необходимо выполнить операцию сжатия, нужно выполнить ее до устранения фрагментации.Therefore, if a shrink operation must be performed, you should do it before fragmentation is removed.

Снижение фрагментации в кучеReducing Fragmentation in a Heap

Для снижения фрагментации экстентов кучи создайте кластеризованный индекс таблицы, а затем удалите его.To reduce the extent fragmentation of a heap, create a clustered index on the table and then drop the index. Во время создания кластеризованного индекса данные перераспределяются.This redistributes the data while the clustered index is created. Также эта операция выполняется наиболее оптимальным способом, учитывая распределение свободного места, доступного базе данных.This also makes it as optimal as possible, considering the distribution of free space available in the database. Когда затем кластеризованный индекс удаляется для повторного создания кучи, данные не перемещаются и их распределение остается оптимальным.When the clustered index is then dropped to re-create the heap, the data is not moved and remains optimally in position. Дополнительные сведения о выполнении этих операций см. в разделе Создание индекса и DROP INDEX.For information about how to perform these operations, see CREATE INDEX and DROP INDEX.

Внимание!

Создавая и удаляя кластеризованный индекс для таблицы, перестраивает все некластеризованные индексы в этой таблице дважды.Creating and dropping a clustered index on a table, rebuilds all nonclustered indexes on that table twice.

Сжатие данных больших объектовCompacting Large Object Data

По умолчанию инструкция ALTER INDEX REORGANIZE делает более компактными страницы, содержащие данные больших объектов (LOB).By default, the ALTER INDEX REORGANIZE statement compacts pages that contain large object (LOB) data. Так как страницы LOB не освобождаются, когда становятся пустыми, сжатие этих данных может оптимизировать использование места на диске, если удаляются в больших объемах данные LOB или же столбцы LOB.Because LOB pages are not deallocated when empty, compacting this data can improve disk space use if lots of LOB data have been deleted, or a LOB column is dropped.

Изменение указанного кластеризованного индекса сжимает все столбцы LOB, которые содержатся в кластеризованном индексе.Reorganizing a specified clustered index compacts all LOB columns that are contained in the clustered index. Изменение некластеризованного индекса сжимает все столбцы LOB, являющиеся неключевыми столбцами, включенными в индекс.Reorganizing a nonclustered index compacts all LOB columns that are nonkey (included) columns in the index. При использовании в инструкции аргумента ALL реорганизуются все индексы, связанные с указанной таблицей или представлением.When ALL is specified in the statement, all indexes that are associated with the specified table or view are reorganized. Кроме того, сжимаются все столбцы LOB, связанные с кластеризованным индексом, базовой таблицей или некластеризованным индексом с включенными столбцами.Additionally, all LOB columns that are associated with the clustered index, underlying table, or nonclustered index with included columns are compacted.

Оценка использования места на дискеEvaluating Disk Space Use

Столбец avg_page_space_used_in_percent указывает заполненность страниц.The avg_page_space_used_in_percent column indicates page fullness. Для достижения оптимального использования места на диске это значение должно быть близким к 100 процентам для индексов, где операции случайных вставок выполняются нечасто.To achieve optimal disk space use, this value should be close to 100 percent for an index that will not have many random inserts. Однако в индексе с множеством случайных вставок, имеющем очень заполненные страницы, будет расти число разбиений страниц.However, an index that has many random inserts and has very full pages will have an increased number of page splits. Это приводит к увеличению фрагментации.This causes more fragmentation. Поэтому для снижения числа разбиений страниц это значение должно быть меньше 100 процентов.Therefore, in order to reduce page splits, the value should be less than 100 percent. Перестроение индекса с параметром FILLFACTOR позволяет изменять степень заполнения страницы для обеспечения соответствия индекса шаблону запроса.Rebuilding an index with the FILLFACTOR option specified allows the page fullness to be changed to fit the query pattern on the index. Дополнительные сведения о коэффициенте заполнения см. в разделе Указание коэффициента заполнения для индекса.For more information about fill factor, see Specify Fill Factor for an Index. Кроме того, инструкция ALTER INDEX REORGANIZE сжимает индекс, пытаясь заполнять страницы до последнего заданного значения аргумента FILLFACTOR.Also, ALTER INDEX REORGANIZE will compact an index by trying to fill pages to the FILLFACTOR that was last specified. Благодаря этому увеличивается значение avg_space_used_in_percent.This increases the value in avg_space_used_in_percent. Обратите внимание, что инструкция ALTER INDEX REORGANIZE не может снизить степень заполнения страницы.Note that ALTER INDEX REORGANIZE cannot reduce page fullness. Для этого необходимо выполнить перестроение индекса.Instead, an index rebuild must be performed.

Оценка фрагментов индексаEvaluating Index Fragments

Фрагмент состоит из физически последовательных конечных страниц в одном файле единицы распределения.A fragment is made up of physically consecutive leaf pages in the same file for an allocation unit. Индекс состоит, по крайней мере, из одного фрагмента.An index has at least one fragment. Максимальное число фрагментов, которое может иметь индекс, равно числу страниц на конечном уровне индекса.The maximum fragments an index can have are equal to the number of pages in the leaf level of the index. Увеличение размера фрагментов означает, что для считывания того же количества страниц понадобится меньшее количество обращений к диску.Larger fragments mean that less disk I/O is required to read the same number of pages. Следовательно, чем больше значение avg_fragment_size_in_pages, тем выше производительность при просмотре диапазона.Therefore, the larger the avg_fragment_size_in_pages value, the better the range scan performance. Значения avg_fragment_size_in_pages и avg_fragmentation_in_percent обратно пропорциональны друг другу.The avg_fragment_size_in_pages and avg_fragmentation_in_percent values are inversely proportional to each other. То есть перестройка или реорганизация индекса уменьшают степень фрагментации и увеличивают размер фрагментов.Therefore, rebuilding or reorganizing an index should reduce the amount of fragmentation and increase the fragment size.

ОграниченияLimitations and Restrictions

Не возвращает данные для кластеризованных индексов columnstore.Does not return data for clustered columnstore indexes.

РазрешенияPermissions

Необходимы следующие разрешения:Requires the following permissions:

  • разрешение CONTROL на указанный объект в базе данных;CONTROL permission on the specified object within the database.

  • Разрешение Просмотр состояния базы данных для получения сведений обо всех объектах в указанной базе данных с помощью шаблона объекта @object_id= null.VIEW DATABASE STATE permission to return information about all objects within the specified database, by using the object wildcard @object_id=NULL.

  • Разрешение Просмотр состояния сервера для возврата сведений обо всех базах данных с помощью подстановочного знака @database_id = null.VIEW SERVER STATE permission to return information about all databases, by using the database wildcard @database_id = NULL.

Предоставление разрешения VIEW DATABASE STATE позволяет всем объектам в базе данных быть возвращаемыми, независимо от любых разрешений CONTROL, запрещенных для определенных объектов.Granting VIEW DATABASE STATE allows all objects in the database to be returned, regardless of any CONTROL permissions denied on specific objects.

Запрет разрешения VIEW DATABASE STATE запрещает всем объектам в базе данных быть возвращаемыми, независимо от любых разрешений CONTROL, предоставленных на определенные объекты.Denying VIEW DATABASE STATE disallows all objects in the database to be returned, regardless of any CONTROL permissions granted on specific objects. Кроме того, если указан шаблон базы данных @database_id= null, то база данных опускается.Also, when the database wildcard @database_id=NULL is specified, the database is omitted.

Дополнительные сведения см. в разделе динамические административные представления и (функции Transact-)SQL.For more information, see Dynamic Management Views and Functions (Transact-SQL).

ПримерыExamples

A.A. Возврат сведений об указанной таблицеReturning information about a specified table

В следующем примере возвращаются размер и статистика фрагментации для всех индексов и секций таблицы Person.Address.The following example returns size and fragmentation statistics for all indexes and partitions of the Person.Address table. Для повышения производительности и ограничения возвращаемой статистики используется режим просмотра 'LIMITED'.The scan mode is set to 'LIMITED' for best performance and to limit the statistics that are returned. Для выполнения этого запроса необходимо по крайней мере разрешение CONTROL на таблицу Person.Address.Executing this query requires, at a minimum, CONTROL permission on the Person.Address table.

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  
  
SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.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. Возврат сведений о кучеReturning information about a heap

В следующем примере возвращается вся статистика для кучи dbo.DatabaseLog в базе данных AdventureWorks2012AdventureWorks2012.The following example returns all statistics for the heap dbo.DatabaseLog in the AdventureWorks2012AdventureWorks2012 database. Так как таблица содержит данные LOB, возвращается строка для LOB_DATA единицы распределения в дополнение к строке IN_ROW_ALLOCATION_UNIT , возвращаемой для, в которой хранятся страницы данных кучи.Because the table contains LOB data, a row is returned for the LOB_DATA allocation unit in addition to the row returned for the IN_ROW_ALLOCATION_UNIT that is storing the data pages of the heap. Для выполнения этого запроса необходимо по крайней мере разрешение CONTROL на таблицу dbo.DatabaseLog.Executing this query requires, at a minimum, CONTROL permission on the dbo.DatabaseLog table.

DECLARE @db_id SMALLINT;  
DECLARE @object_id INT;  
SET @db_id = DB_ID(N'AdventureWorks2012');  
SET @object_id = OBJECT_ID(N'AdventureWorks2012.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. Возврат сведений обо всех базах данныхReturning information for all databases

В следующем примере возвращается вся статистика для всех таблиц и индексов экземпляра SQL ServerSQL Server. Для этого всем параметрам задается символ-шаблон NULL.The following example returns all statistics for all tables and indexes within the instance of SQL ServerSQL Server by specifying the wildcard NULL for all parameters. Для выполнения этого запроса требуется разрешение VIEW SERVER STATE.Executing this query requires the VIEW SERVER STATE permission.

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

Г.D. Использование представления sys.dm_db_index_physical_stats в скрипте для перестроения или реорганизации индексовUsing sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes

В следующем примере автоматически реорганизуются или перестраиваются все секции в базе данных со средней степенью фрагментации более 10 процентов.The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent. Для выполнения этого запроса необходимо разрешение VIEW DATABASE STATE.Executing this query requires the VIEW DATABASE STATE permission. В данном примере в качестве первого параметра указывается DB_ID без определения имени базы данных.This example specifies DB_ID as the first parameter without specifying a database name. Если уровень совместимости текущей базы данных составляет 80 или ниже, будет сформирована ошибка.An error will be generated if the current database has a compatibility level of 80 or lower. Чтобы исправить эту ошибку, замените вызов функции DB_ID() действительным именем базы данных.To resolve the error, replace DB_ID() with a valid database name. Дополнительные сведения об уровнях совместимости баз данных см. в статье уровень (совместимости ALTER DATABASE Transact)-SQL.For more information about database compatibility levels, see ALTER DATABASE Compatibility Level (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  
  

Д.E. Использование представления sys.dm_db_index_physical_stats для отображения числа страниц, подвергнутых сжатию на уровне страницUsing sys.dm_db_index_physical_stats to show the number of page-compressed pages

В следующем примере демонстрируется отображение и сравнение общего числа страниц со страницами, подвергнутыми сжатию на уровне страниц и на уровне строк.The following example shows how to display and compare the total number of pages against the pages that are row and page compressed. Эти сведения могут быть использованы для определения полезности сжатия для индекса или таблицы.This information can be used to determine the benefit that compression is providing for an index or table.

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;  

Е.F. Использование sys.dm_db_index_physical_stats в режиме SAMPLEDUsing sys.dm_db_index_physical_stats in SAMPLED mode

В следующем примере показано, как в режиме SAMPLED возвращается примерное значение, отличающееся от результатов в режиме DETAILED.The following example shows how SAMPLED mode returns an approximate that is different than the DETAILED mode results.

CREATE TABLE t3 (col1 int PRIMARY KEY, col2 varchar(500)) WITH(DATA_COMPRESSION = PAGE);  
GO  
BEGIN TRAN  
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. Запрос очередей компонента Service Broker для фрагментации индексаQuerying service broker queues for index fragmentation

Применимо к: с SQL Server 2016 (13.x)SQL Server 2016 (13.x) до SQL ServerSQL Server.Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL ServerSQL Server.

В следующих примерах показано, как выполнять запросы к очередям брокера сервера для фрагментации.The following examples shows how to query server broker queues for fragmentation.

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

См. такжеSee Also

Динамические административные представления и функции (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
Динамические административные представления и функции, связанные с индексами (Transact-SQL) Index Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL) sys.dm_db_index_operational_stats (Transact-SQL)
sys. DM _db_index_usage_stats (, TRANSACT-SQL) sys.dm_db_index_usage_stats (Transact-SQL)
sys. DM _db_partition_stats (, TRANSACT-SQL) sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL) sys.allocation_units (Transact-SQL)
Системные представления (— TRANSACT-SQL)System Views (Transact-SQL)