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

SE APLICA A: síSQL Server síAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Devuelve información de tamaño y fragmentación de los datos y los índices de la tabla o vista especificada en SQL ServerSQL Server.Returns size and fragmentation information for the data and indexes of the specified table or view in SQL ServerSQL Server. En el caso de un índice, se devuelve una fila por cada nivel de árbol b en cada partición.For an index, one row is returned for each level of the B-tree in each partition. En el caso de un montón, se devuelve una fila para la unidad de asignación IN_ROW_DATA en cada partición.For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. En el caso de datos de objetos grandes (LOB), se devuelve una fila para la unidad de asignación LOB_DATA en cada partición.For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. Si en la tabla hay datos de desbordamiento de fila, se devuelve una fila para la unidad de asignación ROW_OVERFLOW_DATA en cada partición.If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition. No devuelve información sobre los índices de almacén de columnas optimizadas en memoria xVelocity.Does not return information about xVelocity memory optimized columnstore indexes.

Importante

Si consulta Sys. DM _ db_index_physical_stats en una instancia del servidor que hospeda una Always on réplica secundaria legible, puede producirse un problema de bloqueo de puesta al día.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. Esto se debe a que esta vista de administración dinámica adquiere un bloqueo IS en la tabla de usuario especificada o la vista que puede bloquear las solicitudes de un subproceso de REDO durante un bloqueo X en esa tabla o vista de usuario.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 no devuelve información acerca de los índices optimizados para memoria.sys.dm_db_index_physical_stats does not return information about memory-optimized indexes. Para obtener información sobre el uso de índices con optimización para memoria, vea Sys. DM _ (_DB_XTP_INDEX_STATS Transact-SQL).For information about memory-optimized index use, see sys.dm_db_xtp_index_stats (Transact-SQL).

Icono de vínculo de tema Convenciones de sintaxis de Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxisSyntax

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

ArgumentosArguments

database_id | NULL | 0 | DEFAULTdatabase_id | NULL | 0 | DEFAULT
Es el ID. de la base de datos.Is the ID of the database. database_id es smallint.database_id is smallint. Las entradas válidas son el número de identificador de una base de datos, NULL, 0 y DEFAULT.Valid inputs are the ID number of a database, NULL, 0, or DEFAULT. El valor predeterminado es 0.The default is 0. NULL, 0 y DEFAULT son valores equivalentes en este contexto.NULL, 0, and DEFAULT are equivalent values in this context.

Especifique NULL para devolver información de todas las bases de datos en la instancia de SQL ServerSQL Server.Specify NULL to return information for all databases in the instance of SQL ServerSQL Server. Si especifica NULL para database_id, también debe especificar null para object_id, debajo dey partition_number.If you specify NULL for database_id, you must also specify NULL for object_id, index_id, and partition_number.

Se puede especificar la función integrada DB_ID .The built-in function DB_ID can be specified. Al usar DB_ID sin especificar ningún nombre de base de datos, el nivel de compatibilidad de la base de datos actual debe ser 90 o superior.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 | PREDETERMINADAobject_id | NULL | 0 | DEFAULT
Es el identificador de objeto de la tabla o vista donde está activado el índice.Is the object ID of the table or view the index is on. object_id es int.object_id is int.

Las entradas válidas son el número de identificador de una tabla o vista, NULL, 0 y DEFAULT.Valid inputs are the ID number of a table and view, NULL, 0, or DEFAULT. El valor predeterminado es 0.The default is 0. NULL, 0 y DEFAULT son valores equivalentes en este contexto.NULL, 0, and DEFAULT are equivalent values in this context. A partir SQL Server 2016 (13.x)SQL Server 2016 (13.x)de, las entradas válidas también incluyen el nombre de la cola de Service Broker o el nombre de la tabla interna de la cola.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. Cuando se aplican los parámetros predeterminados (es decir, todos los objetos, todos los índices, etc.), la información de fragmentación de todas las colas se incluye en el conjunto de resultados.When default parameters are applied (i.e. all objects, all indexes, etc), fragmentation information for all queues are included in the result set.

Especifique NULL para devolver información de todas las tablas y vistas de la base de datos especificada.Specify NULL to return information for all tables and views in the specified database. Si especifica NULL para object_id, también debe especificar null para el valor de partition_number.If you specify NULL for object_id, you must also specify NULL for index_id and partition_number.

por debajo | 0 | NULL | -1 | PREDETERMINADAindex_id | 0 | NULL | -1 | DEFAULT
Es el identificador del índice.Is the ID of the index. el valor de no es int. Las entradas válidas son el número de identificación de un índice, 0 si object_id es un montón, null,-1 o default.index_id is int. Valid inputs are the ID number of an index, 0 if object_id is a heap, NULL, -1, or DEFAULT. El valor predeterminado es-1.The default is -1. NULL,-1 y DEFAULT son valores equivalentes en este contexto.NULL, -1, and DEFAULT are equivalent values in this context.

Especifique NULL para devolver información de todos los índices de una tabla o vista base.Specify NULL to return information for all indexes for a base table or view. Si especifica NULL para elvalor de escriba, también debe especificar null para partition_number.If you specify NULL for index_id, you must also specify NULL for partition_number.

partition_number | NULL | 0 | PREDETERMINADApartition_number | NULL | 0 | DEFAULT
Es el número de partición en el objeto.Is the partition number in the object. partition_number es de tipo int. Las entradas válidas son el partion_number de un índice o montón, null, 0 o default.partition_number is int. Valid inputs are the partion_number of an index or heap, NULL, 0, or DEFAULT. El valor predeterminado es 0.The default is 0. NULL, 0 y DEFAULT son valores equivalentes en este contexto.NULL, 0, and DEFAULT are equivalent values in this context.

Especifique NULL para obtener información sobre todas las particiones del objeto propietario.Specify NULL to return information for all partitions of the owning object.

partition_number se basa en 1.partition_number is 1-based. Un índice o montón sin particiones tiene partition_number establecido en 1.A nonpartitioned index or heap has partition_number set to 1.

modo | NULL | PREDETERMINADAmode | NULL | DEFAULT
Es el nombre del modo.Is the name of the mode. el modo especifica el nivel de examen que se utiliza para obtener las estadísticas.mode specifies the scan level that is used to obtain statistics. mode es sysname.mode is sysname. Las entradas válidas son DEFAULT, NULL, LIMITED, SAMPLED o DETAILED.Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. El valor predeterminado (NULL) es LIMITED.The default (NULL) is LIMITED.

Tabla devueltaTable Returned

Nombre de columnaColumn name Tipo de datosData type DescripciónDescription
database_iddatabase_id smallintsmallint Identificador de base de datos de la tabla o vista.Database ID of the table or view.
object_idobject_id intint Identificador de objeto de la tabla o vista en la que se encuentra el índice.Object ID of the table or view that the index is on.
index_idindex_id intint Identificador de índice.Index ID of an index.

0 = Montón.0 = Heap.
partition_numberpartition_number intint Número de partición de base 1 en el objeto propietario, una tabla, vista o índice.1-based partition number within the owning object; a table, view, or index.

1 = Montón o índice sin particiones.1 = Nonpartitioned index or heap.
index_type_descindex_type_desc nvarchar(60)nvarchar(60) Descripción del tipo de índice:Description of the index type:

HEAPHEAP

CLUSTERED INDEXCLUSTERED INDEX

NONCLUSTERED INDEXNONCLUSTERED INDEX

PRIMARY XML INDEXPRIMARY XML INDEX

EXTENDED INDEXEXTENDED INDEX

XML INDEXXML INDEX

Índice de asignación de almacén de columnas (interno)COLUMNSTORE MAPPING INDEX (internal)

Índice DELETEBUFFER de almacén de columnas (interno)COLUMNSTORE DELETEBUFFER INDEX (internal)

Índice DELETEBITMAP de almacén de columnas (interno)COLUMNSTORE DELETEBITMAP INDEX (internal)
hobt_idhobt_id bigintbigint IDENTIFICADOR de árbol B o de montón del índice o de la partición.Heap or B-Tree ID of the index or partition.

Además de devolver el hobt_id de los índices definidos por el usuario, también devuelve el hobt_id de los índices de almacén de columnas internos.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) Descripción del tipo de unidad de asignación:Description of the allocation unit type:

IN_ROW_DATAIN_ROW_DATA

LOB_DATALOB_DATA

ROW_OVERFLOW_DATAROW_OVERFLOW_DATA

La unidad de asignación LOB_DATA contiene los datos que se almacenan en columnas de tipo Text, ntext, Image, VARCHAR (Max) , nvarchar (Max) , varbinary (Max) y 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. Para obtener más información, vea Tipos de datos (Transact-SQL).For more information, see Data Types (Transact-SQL).

La unidad de asignación ROW_OVERFLOW_DATA contiene los datos que se almacenan en columnas de tipo VARCHAR (n) , nvarchar (n) , varbinary (n) y sql_variant que se han insertado de forma no consecutiva.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 Número de niveles del índice.Number of index levels.

1 = Montón, o unidad de asignación LOB_DATA o ROW_OVERFLOW_DATA.1 = Heap, or LOB_DATA or ROW_OVERFLOW_DATA allocation unit.
index_levelindex_level tinyinttinyint Nivel actual del índice.Current level of the index.

0 para niveles hoja del índice, montones y unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA.0 for index leaf levels, heaps, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

Mayor que 0 para niveles de índice no hoja.Greater than 0 for nonleaf index levels. index_level será el valor más alto en el nivel raíz de un índice.index_level will be the highest at the root level of an index.

Los niveles no hoja de los índices solo se procesan cuando mode = Detailed.The nonleaf levels of indexes are only processed when mode = DETAILED.
avg_fragmentation_in_percentavg_fragmentation_in_percent floatfloat Fragmentación lógica para índices o fragmentación de extensión para montones en la unidad de asignación IN_ROW_DATA.Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit.

El valor se mide como porcentaje y tiene en cuenta varios archivos.The value is measured as a percentage and takes into account multiple files. Para obtener definiciones de fragmentación lógica y de extensión, vea la sección Comentarios.For definitions of logical and extent fragmentation, see Remarks.

0 para unidades de asignación LOB_DATA y ROW_OVERFLOW_DATA.0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units.

NULL para montones cuando mode = sampled.NULL for heaps when mode = SAMPLED.
fragment_countfragment_count bigintbigint Número de fragmentos en el nivel hoja de una unidad de asignación IN_ROW_DATA.Number of fragments in the leaf level of an IN_ROW_DATA allocation unit. Para obtener más información sobre los fragmentos, vea la sección Comentarios.For more information about fragments, see Remarks.

NULL para niveles no hoja de un índice y unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA.NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

NULL para montones cuando mode = sampled.NULL for heaps when mode = SAMPLED.
avg_fragment_size_in_pagesavg_fragment_size_in_pages floatfloat Promedio de páginas en un fragmento en el nivel hoja de una unidad de asignación IN_ROW_DATA.Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit.

NULL para niveles no hoja de un índice y unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA.NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units.

NULL para montones cuando mode = sampled.NULL for heaps when mode = SAMPLED.
page_countpage_count bigintbigint Número total de páginas de datos o de índice.Total number of index or data pages.

En el caso de un índice, el número total de páginas de índice en el nivel actual de un árbol b en la unidad de asignación 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.

En el caso de un montón, el número total de páginas de datos en la unidad de asignación IN_ROW_DATA.For a heap, the total number of data pages in the IN_ROW_DATA allocation unit.

En el caso de unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA, el número total de páginas en la unidad de asignación.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 Porcentaje medio del espacio de almacenamiento de datos disponible utilizado en todas las páginas.Average percentage of available data storage space used in all pages.

En el caso de un índice, el promedio se aplica al nivel actual del árbol b en la unidad de asignación IN_ROW_DATA.For an index, average applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

En el caso de un montón, se trata del promedio de todas las páginas de datos en la unidad de asignación IN_ROW_DATA.For a heap, the average of all data pages in the IN_ROW_DATA allocation unit.

En el caso de unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA, se trata del promedio de todas las páginas en la unidad de asignación.For LOB_DATA or ROW_OVERFLOW DATA allocation units, the average of all pages in the allocation unit.

NULL cuando mode = Limited.NULL when mode = LIMITED.
record_countrecord_count bigintbigint Número total de registros.Total number of records.

En el caso de un índice, el número total de registros se aplica al nivel actual del árbol b en la unidad de asignación 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.

En el caso de un montón, el número total de registros en la unidad de asignación IN_ROW_DATA.For a heap, the total number of records in the IN_ROW_DATA allocation unit.

Nota: En el caso de un montón, es posible que el número de registros devueltos por esta función no coincida con el número de filas que*se devuelven al ejecutar Select Count () en el montón.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. Esto es debido a que una fila puede contener varios registros.This is because a row may contain multiple records. Por ejemplo, en algunas situaciones de una actualización, una única fila del montón puede tener un registro de reenvío y un registro reenviado como resultado de la actualización.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. Asimismo, la mayoría de las filas LOB de gran tamaño se dividen en varios registros en almacenamiento de LOB_DATA.Also, most large LOB rows are split into multiple records in LOB_DATA storage.

En el caso de unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA, el número total de registros en toda la unidad de asignación.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the total number of records in the complete allocation unit.

NULL cuando mode = Limited.NULL when mode = LIMITED.
ghost_record_countghost_record_count bigintbigint Número de registros fantasma preparados para su eliminación mediante la tarea de limpieza de registros fantasma en la unidad de asignación.Number of ghost records ready for removal by the ghost cleanup task in the allocation unit.

0 para niveles no hoja de un índice en la unidad de asignación IN_ROW_DATA.0 for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

NULL cuando mode = Limited.NULL when mode = LIMITED.
version_ghost_record_countversion_ghost_record_count bigintbigint Número de registros fantasma retenidos por una transacción de aislamiento de instantánea pendiente en una unidad de asignación.Number of ghost records retained by an outstanding snapshot isolation transaction in an allocation unit.

0 para niveles no hoja de un índice en la unidad de asignación IN_ROW_DATA.0 for nonleaf levels of an index in the IN_ROW_DATA allocation unit.

NULL cuando mode = Limited.NULL when mode = LIMITED.
min_record_size_in_bytesmin_record_size_in_bytes intint Tamaño mínimo del registro en bytes.Minimum record size in bytes.

En el caso de un índice, el tamaño mínimo del registro se aplica al nivel actual del árbol b en la unidad de asignación 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.

En el caso de un montón, el tamaño mínimo del registro en la unidad de asignación IN_ROW_DATA.For a heap, the minimum record size in the IN_ROW_DATA allocation unit.

En el caso de unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA, el tamaño mínimo del registro en toda la unidad de asignación.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the minimum record size in the complete allocation unit.

NULL cuando mode = Limited.NULL when mode = LIMITED.
max_record_size_in_bytesmax_record_size_in_bytes intint Tamaño máximo del registro en bytes.Maximum record size in bytes.

En el caso de un índice, el tamaño máximo del registro se aplica al nivel actual del árbol b en la unidad de asignación 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.

En el caso de un montón, el tamaño máximo del registro en la unidad de asignación IN_ROW_DATA.For a heap, the maximum record size in the IN_ROW_DATA allocation unit.

En el caso de unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA, el tamaño máximo del registro en toda la unidad de asignación.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the maximum record size in the complete allocation unit.

NULL cuando mode = Limited.NULL when mode = LIMITED.
avg_record_size_in_bytesavg_record_size_in_bytes floatfloat Promedio de tamaño del registro en bytes.Average record size in bytes.

En el caso de un índice, el promedio de tamaño del registro se aplica al nivel actual del árbol b en la unidad de asignación 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.

En el caso de un montón, el promedio de tamaño del registro en la unidad de asignación IN_ROW_DATA.For a heap, the average record size in the IN_ROW_DATA allocation unit.

En el caso de unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA, el promedio de tamaño del registro en toda la unidad de asignación.For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the average record size in the complete allocation unit.

NULL cuando mode = Limited.NULL when mode = LIMITED.
forwarded_record_countforwarded_record_count bigintbigint Número de registros de un montón que tienen punteros hacia delante a otra ubicación de datos.Number of records in a heap that have forward pointers to another data location. Este estado se produce durante una actualización, cuando no existe suficiente espacio para almacenar la nueva fila en la ubicación original.(This state occurs during an update, when there is not enough room to store the new row in the original location.)

NULL para todas las unidades de asignación salvo IN_ROW_DATA para un montón.NULL for any allocation unit other than the IN_ROW_DATA allocation units for a heap.

NULL para montones cuando mode = Limited.NULL for heaps when mode = LIMITED.
compressed_page_countcompressed_page_count bigintbigint Número de páginas comprimidas.The number of compressed pages.

En el caso de los montones, las nuevas páginas asignadas no usan la compresión de página.For heaps, newly allocated pages are not PAGE compressed. Un montón usa la compresión de página bajo dos condiciones especiales: cuando los datos se importan de forma masiva o cuando vuelve a generarse un montón.A heap is PAGE compressed under two special conditions: when data is bulk imported or when a heap is rebuilt. Las operaciones DML que producen las asignaciones de página no usarán la compresión de página.Typical DML operations that cause page allocations will not be PAGE compressed. Vuelva a generar un montón cuando el valor compressed_page_count sea mayor que el umbral que desea.Rebuild a heap when the compressed_page_count value grows larger than the threshold you want.

En las tablas que tienen un índice clúster, el valor compressed_page_count indica la eficacia de la compresión de página.For tables that have a clustered index, the compressed_page_count value indicates the effectiveness of PAGE compression.
hobt_idhobt_id bigintbigint Se aplica a: SQL ServerSQL Server SQL Server 2016 (13.x)SQL Server 2016 (13.x) (hasta la versión actual) Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Base de datos SQL de AzureAzure SQL Database.

Solo para los índices de almacén de columnas, es el identificador de un conjunto de filas que realiza un seguimiento de los datos internos de almacén de columnas para una partición.For columnstore indexes only, this is the ID for a rowset that tracks internal columnstore data for a partition. Los conjuntos de filas se almacenan como montones de datos o árboles binarios.The rowsets are stored as data heaps or binary trees. Tienen el mismo identificador de índice que el índice de almacén de columnas principal.They have the same index ID as the parent columnstore index. Para obtener más información, vea Sys. (INTERNAL_PARTITIONS Transact-)SQL.For more information, see sys.internal_partitions (Transact-SQL).

NULL siNULL if
column_store_delete_buffer_statecolumn_store_delete_buffer_state tinyinttinyint Se aplica a: SQL ServerSQL Server SQL Server 2016 (13.x)SQL Server 2016 (13.x) (hasta la versión actual) Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Base de datos SQL de AzureAzure SQL Database.

0 = NOT_APPLICABLE0 = NOT_APPLICABLE

1 = OPEN1 = OPEN

2 = PURGAR2 = DRAINING

3 = VACIADO3 = FLUSHING

4 = RETIRADA4 = RETIRING

5 = LISTO5 = READY
column_store_delete_buff_state_desccolumn_store_delete_buff_state_desc Se aplica a: SQL ServerSQL Server SQL Server 2016 (13.x)SQL Server 2016 (13.x) (hasta la versión actual) Base de datos SQL de AzureAzure SQL Database.Applies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), Base de datos SQL de AzureAzure SQL Database.

NO válido: el índice primario no es un índice de almacén de columnas.NOT VALID -the parent index is not a columnstore index.

Los analizadores y los eliminadores abiertos utilizan este.OPEN - deleters and scanners use this.

PURGAr: los eliminadores se están purgando pero los analizadores aún lo usan.DRAINING - deleters are draining out but scanners still use it.

VACIAdo: el búfer está cerrado y las filas del búfer se escriben en el mapa de bits de eliminación.FLUSHING - buffer is closed and rows in the buffer are being written to the delete bitmap.

RETIRADA: las filas del búfer de eliminación cerrado se han escrito en el mapa de bits de eliminación, pero el búfer no se ha truncado porque los escáneres aún lo están usando.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. No es necesario que los nuevos escáneres usen el búfer de retirada porque el búfer abierto es suficiente.New scanners don't need to use the retiring buffer because the open buffer is enough.

LISTO: este búfer de eliminación está listo para su uso.READY - This delete buffer is ready for use.

ComentariosRemarks

La función de administración dinámica sys.dm_db_index_physical_stats sustituye a la instrucción DBCC SHOWCONTIG.The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement.

Modos de recorridoScanning Modes

El modo en que se ejecuta la función determina el nivel de recorrido realizado para obtener los datos estadísticos que utiliza la función.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. el modo se especifica como Limited, sampled o Detailed.mode is specified as LIMITED, SAMPLED, or DETAILED. La función recorre las cadenas de páginas buscando las unidades de asignación que producen las particiones especificadas de la tabla o el índice.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 solo requiere un bloqueo de tabla con intención compartida (IS), independientemente del modo en que se ejecute.sys.dm_db_index_physical_stats requires only an Intent-Shared (IS) table lock, regardless of the mode that it runs in.

El modo LIMITED es el modo más rápido y examina el menor número de páginas.The LIMITED mode is the fastest mode and scans the smallest number of pages. Para un índice, solamente se examinan las páginas del nivel primario del árbol b (es decir, las páginas sobre el nivel hoja).For an index, only the parent-level pages of the B-tree (that is, the pages above the leaf level) are scanned. Para un montón, se examinan las páginas PFS e IAM asociadas y las páginas de datos de un montón se exploran en modo LIMITED.For a heap, the associated PFS and IAM pages are examined and the data pages of a heap are scanned in LIMITED mode.

Con el modo LIMITED, compressed_page_count es NULL porque el Motor de base de datosDatabase Engine solamente examina las páginas no hoja del árbol b y las páginas IAM y PFS del montón.With LIMITED mode, compressed_page_count is NULL because the Motor de base de datosDatabase Engine only scans non-leaf pages of the B-tree and the IAM and PFS pages of the heap. Use el modo SAMPLEd para obtener un valor estimado para compressed_page_count y use el modo Detailed para obtener el valor real de 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. El modo SAMPLED devuelve estadísticas basadas en una muestra de un uno por ciento de todas las páginas del índice o montón.The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. Los resultados en el modo SAMPLED se deben considerar como aproximados.Results in SAMPLED mode should be regarded as approximate. Si el índice o montón tiene menos de 10.000 páginas, se utiliza el modo DETAILED en lugar del modo SAMPLED.If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.

El modo DETAILED recorre todas las páginas y devuelve todas las estadísticas.The DETAILED mode scans all pages and returns all statistics.

Los modos son progresivamente más lentos de LIMITED a DETAILED, ya que se va realizando más trabajo en cada nodo.The modes are progressively slower from LIMITED to DETAILED, because more work is performed in each mode. Para analizar rápidamente el nivel de fragmentación o tamaño de una tabla o índice, utilice el modo LIMITED.To quickly gauge the size or fragmentation level of a table or index, use the LIMITED mode. Es el modo más rápido y no obtendrá una fila por cada nivel no hoja en la unidad de asignación IN_ROW_DATA del índice.It is the fastest and will not return a row for each nonleaf level in the IN_ROW_DATA allocation unit of the index.

Usar funciones del sistema para especificar valores de parámetrosUsing System Functions to Specify Parameter Values

Puede Transact-SQLTransact-SQL usar las funciones DB_ID y object_id para especificar un valor para los parámetros database_id y 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. Sin embargo, el envío de valores no válidos a estas funciones puede provocar resultados no deseados.However, passing values that are not valid to these functions may cause unintended results. Por ejemplo, si no se encuentra un nombre de objeto o base de datos, porque no existe o se ha escrito incorrectamente, ambas funciones devolverán 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. La función sys.dm_db_index_physical_stats interpreta NULL como un valor comodín que especifica todas las bases de datos o todos los objetos.The sys.dm_db_index_physical_stats function interprets NULL as a wildcard value specifying all databases or all objects.

Además, la función OBJECT_ID se procesa antes de que se llame a la función sys. DM _ db_index_physical_stats y, por tanto, se evalúa en el contexto de la base de datos actual, no en la base de datos especificada en 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. Este comportamiento puede hacer que la función OBJECT_ID devuelva un valor NULL; o bien, si el nombre de objeto existe en el contexto de la base de datos actual y en el de la base de datos especificada, podría devolverse un mensaje de error.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. En los siguientes ejemplos se ilustran estos resultados no deseados.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  

Práctica recomendadaBest Practice

Asegúrese de que se devuelva un identificador válido cuando utilice DB_ID u OBJECT_ID.Always make sure that a valid ID is returned when you use DB_ID or OBJECT_ID. Por ejemplo, si usa OBJECT_ID, especifique un nombre de tres partes como OBJECT_ID(N'AdventureWorks2012.Person.Address'), o bien Pruebe el valor devuelto por las funciones antes de usarlos en la función 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. En los ejemplos A y B siguientes se ilustra una forma segura de especificar identificadores de objetos y bases de datos.Examples A and B that follow demonstrate a safe way to specify database and object IDs.

Detectar la fragmentaciónDetecting Fragmentation

La fragmentación es consecuencia de los procesos de modificación de los datos (instrucciones INSERT, UPDATE y DELETE) efectuados en la tabla y en los índices definidos en la tabla.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. Como dichas modificaciones no suelen estar distribuidas de forma equilibrada entre las filas de la tabla y los índices, el llenado de cada página puede variar con el paso del tiempo.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. Para las consultas que examinan parcial o totalmente los índices de una tabla, este tipo de fragmentación puede producir lecturas de páginas adicionales.For queries that scan part or all of the indexes of a table, this kind of fragmentation can cause additional page reads. Esto impide el examen paralelo de los datos.This hinders parallel scanning of data.

El nivel de fragmentación de un índice o montón aparece en la columna avg_fragmentation_in_percent.The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. En el caso de montones, el valor representa la fragmentación de extensión del montón.For heaps, the value represents the extent fragmentation of the heap. En el caso de índices, el valor representa la fragmentación lógica del índice.For indexes, the value represents the logical fragmentation of the index. A diferencia de DBCC SHOWCONTIG, los algoritmos de cálculo de fragmentación en ambos casos tienen en cuenta un almacenamiento que abarca varios archivos y, por lo tanto, son precisos.Unlike DBCC SHOWCONTIG, the fragmentation calculation algorithms in both cases consider storage that spans multiple files and, therefore, are accurate.

Fragmentación lógicaLogical Fragmentation

Se trata del porcentaje de páginas sin orden en las páginas hoja de un índice.This is the percentage of out-of-order pages in the leaf pages of an index. Una página no ordenada es aquella en la que la siguiente página física asignada al índice no es la que señala el puntero de página siguiente en la página hoja actual.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.

Fragmentación de extensiónExtent Fragmentation

Se trata del porcentaje de extensiones sin orden en las páginas hoja de un montón.This is the percentage of out-of-order extents in the leaf pages of a heap. Una extensión sin orden es aquella para la que la extensión que contiene la página actual de un montón no es físicamente la extensión siguiente a la que contiene la página anterior.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.

El valor de avg_fragmentation_in_percent debe ser lo más cercano posible a cero para obtener un rendimiento máximo.The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. No obstante, los valores de 0% a 10% son aceptables.However, values from 0 percent through 10 percent may be acceptable. Puede utilizar todos los métodos de reducción de la fragmentación (por ejemplo, volver a generar, reorganizar o volver a crear) para disminuir estos valores.All methods of reducing fragmentation, such as rebuilding, reorganizing, or re-creating, can be used to reduce these values. Para obtener más información sobre cómo analizar el grado de fragmentación en un índice, vea reorganizar y volver a generar índices.For more information about how to analyze the degree of fragmentation in an index, see Reorganize and Rebuild Indexes.

Reducir la fragmentación en un índiceReducing Fragmentation in an Index

Cuando un índice está fragmentado de tal forma que el rendimiento de las consultas se ve afectado, hay tres opciones para reducir la fragmentación:When an index is fragmented in a way that the fragmentation is affecting query performance, there are three choices for reducing fragmentation:

  • Quite y vuelva a crear el índice clúster.Drop and re-create the clustered index.

    La reconstrucción de un índice clúster redistribuye los datos, lo que ocasiona que las páginas de datos se llenen.Re-creating a clustered index redistributes the data and results in full data pages. El grado de llenado puede configurarse con la opción FILLFACTOR de CREATE INDEX.The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX. Los inconvenientes de este método son que el índice está sin conexión durante el proceso de eliminación y creación, y que la operación es atómica.The drawbacks in this method are that the index is offline during the drop and re-create cycle, and that the operation is atomic. Si se interrumpe la creación del índice, no vuelve a crearse.If the index creation is interrupted, the index is not re-created. Para obtener más información, vea CREATE INDEX (Transact-SQL).For more information, see CREATE INDEX (Transact-SQL).

  • Utilice ALTER INDEX REORGANIZE, el sustituto de DBCC INDEXDEFRAG, para volver a ordenar las páginas de nivel hoja del índice en un orden lógico.Use ALTER INDEX REORGANIZE, the replacement for DBCC INDEXDEFRAG, to reorder the leaf level pages of the index in a logical order. Se trata de una operación en línea, por lo que el índice está disponible mientras se ejecuta la instrucción.Because this is an online operation, the index is available while the statement is running. La operación también puede interrumpirse sin perder el trabajo ya completado.The operation can also be interrupted without losing work already completed. Los inconvenientes de este método son que no es una forma tan buena de reorganizar los datos como la operación de volver a crear un índice, y que no actualiza las estadísticas.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.

  • Utilice ALTER INDEX REBUILD, el sustituto de DBCC DBREINDEX, para volver a generar el índice en línea o sin conexión.Use ALTER INDEX REBUILD, the replacement for DBCC DBREINDEX, to rebuild the index online or offline. Para obtener más información, vea ALTER INDEX (Transact-SQL).For more information, see ALTER INDEX (Transact-SQL).

La fragmentación por sí sola no es una razón suficiente para reorganizar o volver a generar un índice.Fragmentation alone is not a sufficient reason to reorganize or rebuild an index. El principal efecto de la fragmentación es una disminución del rendimiento de la lectura anticipada de páginas durante los recorridos de índice.The main effect of fragmentation is that it slows down page read-ahead throughput during index scans. Esto se traduce en tiempos de respuesta más lentos.This causes slower response times. Si la carga de consultas en un índice o tabla fragmentado no implica recorridos, debido a que la carga incluye principalmente búsquedas singleton, es posible que la eliminación de la fragmentación no tenga ninguna consecuencia.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.

Nota

Al ejecutar DBCC SHRINKFILE o DBCC SHRINKDATABASE, puede producirse una fragmentación si un índice se mueve parcial o totalmente durante la operación de reducción.Running DBCC SHRINKFILE or DBCC SHRINKDATABASE may introduce fragmentation if an index is partly or completely moved during the shrink operation. Por esta razón, si tiene que realizar una operación de reducción, debe realizarla antes de quitar la fragmentación.Therefore, if a shrink operation must be performed, you should do it before fragmentation is removed.

Reducir la fragmentación en un montónReducing Fragmentation in a Heap

Para reducir la fragmentación de extensión de un montón, cree un índice clúster en la tabla y, a continuación, quítelo.To reduce the extent fragmentation of a heap, create a clustered index on the table and then drop the index. Con esta acción se redistribuyen los datos mientras se crea el índice clúster.This redistributes the data while the clustered index is created. Esto también optimiza la distribución del espacio disponible en la base de datos.This also makes it as optimal as possible, considering the distribution of free space available in the database. Cuando el índice clúster se quita para volver a crear el montón, los datos no se mueven y permanecen de forma óptima en su posición.When the clustered index is then dropped to re-create the heap, the data is not moved and remains optimally in position. Para obtener información sobre cómo realizar estas operaciones, vea Create index y Drop index.For information about how to perform these operations, see CREATE INDEX and DROP INDEX.

Precaución

Al crear y quitar un índice clúster en una tabla, se recompilan dos veces todos los índices no clúster de esa tabla.Creating and dropping a clustered index on a table, rebuilds all nonclustered indexes on that table twice.

Compactar datos de objetos grandesCompacting Large Object Data

De forma predeterminada, la instrucción ALTER INDEX REORGANIZE compacta las páginas que contienen datos de objetos grandes (LOB).By default, the ALTER INDEX REORGANIZE statement compacts pages that contain large object (LOB) data. Dado que las páginas LOB no cancelan su asignación cuando están vacías, el hecho de compactar estos datos puede mejorar el uso del espacio en disco si se eliminan muchos datos LOB o si se quita una columna 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.

Si reorganiza un índice clúster específico, se compactan todas las columnas LOB incluidas en el índice clúster.Reorganizing a specified clustered index compacts all LOB columns that are contained in the clustered index. Si reorganiza un índice no clúster, se compactan todas las columnas LOB sin clave incluidas en el índice.Reorganizing a nonclustered index compacts all LOB columns that are nonkey (included) columns in the index. Cuando se especifica ALL en la instrucción, todos los índices asociados a la tabla o vista especificada se reorganizan.When ALL is specified in the statement, all indexes that are associated with the specified table or view are reorganized. Asimismo, se compactan todas las columnas LOB asociadas al índice clúster, la tabla subyacente o el índice no clúster que contiene columnas.Additionally, all LOB columns that are associated with the clustered index, underlying table, or nonclustered index with included columns are compacted.

Evaluar el uso del espacio en discoEvaluating Disk Space Use

La columna avg_page_space_used_in_percent indica el llenado de la página.The avg_page_space_used_in_percent column indicates page fullness. Para lograr un uso óptimo del espacio en disco, este valor debe estar próximo al 100% para un índice que no tenga muchas inserciones aleatorias.To achieve optimal disk space use, this value should be close to 100 percent for an index that will not have many random inserts. No obstante, un índice que tenga muchas inserciones aleatorias y páginas muy llenas tendrá más divisiones de páginas.However, an index that has many random inserts and has very full pages will have an increased number of page splits. Esto causa más fragmentación.This causes more fragmentation. Por lo tanto, para reducir las divisiones de páginas, el valor debe ser inferior a 100%.Therefore, in order to reduce page splits, the value should be less than 100 percent. Si vuelve a generar un índice con la opción FILLFACTOR especificada, podrá cambiar el llenado de la página para ajustarse al patrón de consulta en el índice.Rebuilding an index with the FILLFACTOR option specified allows the page fullness to be changed to fit the query pattern on the index. Para obtener más información acerca del factor de relleno, vea especificar el factor de relleno para un índice.For more information about fill factor, see Specify Fill Factor for an Index. Asimismo, ALTER INDEX REORGANIZE compactará un índice intentando rellenar las páginas según el último valor de FILLFACTOR especificado.Also, ALTER INDEX REORGANIZE will compact an index by trying to fill pages to the FILLFACTOR that was last specified. Esto aumenta el valor de avg_space_used_in_percent.This increases the value in avg_space_used_in_percent. Tenga en cuenta que ALTER INDEX REORGANIZE no puede reducir el llenado de página.Note that ALTER INDEX REORGANIZE cannot reduce page fullness. Para ello, deberá volver a generar el índice.Instead, an index rebuild must be performed.

Evaluar fragmentos de índiceEvaluating Index Fragments

Un fragmento se compone de páginas hoja consecutivas físicamente en el mismo archivo para una unidad de asignación.A fragment is made up of physically consecutive leaf pages in the same file for an allocation unit. Un índice tiene al menos un fragmento.An index has at least one fragment. El número máximo de fragmentos que puede tener un índice es igual al número de páginas en el nivel hoja de un índice.The maximum fragments an index can have are equal to the number of pages in the leaf level of the index. El uso de fragmentos mayores indica que se necesita menos E/S de disco para leer el mismo número de páginas.Larger fragments mean that less disk I/O is required to read the same number of pages. Por lo tanto, cuanto mayor sea el valor de avg_fragment_size_in_pages, mejor será el rendimiento del recorrido de intervalos.Therefore, the larger the avg_fragment_size_in_pages value, the better the range scan performance. Los valores avg_fragment_size_in_pages y avg_fragmentation_in_percent son inversamente proporcionales entre sí.The avg_fragment_size_in_pages and avg_fragmentation_in_percent values are inversely proportional to each other. Por lo tanto, si vuelve a generar o reorganiza un índice, debe reducir la cantidad de fragmentación y aumentar el tamaño de los fragmentos.Therefore, rebuilding or reorganizing an index should reduce the amount of fragmentation and increase the fragment size.

Limitaciones y restriccionesLimitations and Restrictions

No devuelve datos para los índices de almacén de columnas en clúster.Does not return data for clustered columnstore indexes.

PermisosPermissions

Necesita los siguientes permisos:Requires the following permissions:

  • El permiso CONTROL en el objeto especificado en la base de datos.CONTROL permission on the specified object within the database.

  • Permiso VIEW DATABASE STATE para devolver información sobre todos los objetos de la base de datos especificada, mediante el uso del comodín de objeto @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.

  • Permiso VIEW SERVER STATE para devolver información sobre todas las bases de datos mediante el carácter comodín de base de datos @database_id = null.VIEW SERVER STATE permission to return information about all databases, by using the database wildcard @database_id = NULL.

El permiso VIEW DATABASE STATE permite devolver todos los objetos de la base de datos, independientemente de los permisos CONTROL denegados en objetos específicos.Granting VIEW DATABASE STATE allows all objects in the database to be returned, regardless of any CONTROL permissions denied on specific objects.

Si se deniega el permiso VIEW DATABASE STATE, no se puede devolver ningún objeto de la base de datos, independientemente de que se hayan concedido permisos CONTROL a objetos específicos.Denying VIEW DATABASE STATE disallows all objects in the database to be returned, regardless of any CONTROL permissions granted on specific objects. Además, cuando se especifica el carácter comodín de base de datos @database_id= null, se omite la base de datos.Also, when the database wildcard @database_id=NULL is specified, the database is omitted.

Para obtener más información, vea funciones (y vistas de administración dinámica de)Transact-SQL.For more information, see Dynamic Management Views and Functions (Transact-SQL).

EjemplosExamples

A.A. Devolver información acerca de una tabla especificadaReturning information about a specified table

El ejemplo siguiente devuelve estadísticas de fragmentación y tamaño de todos los índices y particiones de la tabla Person.Address.The following example returns size and fragmentation statistics for all indexes and partitions of the Person.Address table. El modo de recorrido se establece en 'LIMITED' para obtener el mayor rendimiento posible y limitar las estadísticas devueltas.The scan mode is set to 'LIMITED' for best performance and to limit the statistics that are returned. Para ejecutar esta consulta, es necesario, como mínimo, el permiso CONTROL en la tabla 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.B. Devolver información acerca de un montónReturning information about a heap

El ejemplo siguiente devuelve todas las estadísticas para el montón dbo.DatabaseLog de la base de datos AdventureWorks2012AdventureWorks2012.The following example returns all statistics for the heap dbo.DatabaseLog in the AdventureWorks2012AdventureWorks2012 database. Dado que la tabla contiene datos LOB, se devuelve una fila para la unidad de asignación LOB_DATA y una fila para el valor IN_ROW_ALLOCATION_UNIT que está almacenando las páginas de datos del montón.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. Para ejecutar esta consulta, es necesario, como mínimo, el permiso CONTROL en la tabla 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.C. Devolver información de todas las bases de datosReturning information for all databases

En el ejemplo siguiente se devuelven todas las estadísticas de todas las tablas e índices de la instancia de SQL ServerSQL Server; para ello, se especifica el comodín NULL en todos los parámetros.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. La ejecución de esta consulta requiere el permiso 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.D. Usar sys.dm_db_index_physical_stats en un script para volver a generar o reorganizar índicesUsing sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes

El ejemplo siguiente reorganiza o vuelve a generar automáticamente todas las particiones de una base de datos que tienen un promedio de fragmentación superior al 10%.The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent. Para ejecutar esta consulta necesita el permiso VIEW DATABASE STATE.Executing this query requires the VIEW DATABASE STATE permission. Este ejemplo especifica DB_ID como primer parámetro, sin especificar un nombre de base de datos.This example specifies DB_ID as the first parameter without specifying a database name. Se generará un error si la base de datos actual tiene un nivel de compatibilidad de 80 o inferior.An error will be generated if the current database has a compatibility level of 80 or lower. Para solucionar el error, reemplace DB_ID() por un nombre de base de datos válido.To resolve the error, replace DB_ID() with a valid database name. Para obtener más información sobre los niveles de compatibilidad de bases de datos, vea el nivel (de compatibilidad de 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.E. Utilizar sys.dm_db_index_physical_stats para mostrar el número de páginas que usan la compresión de páginaUsing sys.dm_db_index_physical_stats to show the number of page-compressed pages

En el ejemplo siguiente se muestra cómo mostrar y comparar el número total de páginas con las páginas sometidas a compresión de filas y páginas.The following example shows how to display and compare the total number of pages against the pages that are row and page compressed. Esta información se puede utilizar para determinar el beneficio que aporta la compresión a un índice o una tabla.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.F. Usar sys.dm_db_index_physical_stats en el modo SAMPLEDUsing sys.dm_db_index_physical_stats in SAMPLED mode

El ejemplo siguiente muestra cómo el modo SAMPLED devuelve un valor aproximado diferente a los resultados del modo 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.G. Consultando las colas de Service Broker para la fragmentación de índicesQuerying service broker queues for index fragmentation

Se aplica a: desde SQL Server 2016 (13.x)SQL Server 2016 (13.x) hasta SQL ServerSQL Server.Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL ServerSQL Server.

En los siguientes ejemplos se muestra cómo consultar las colas del agente de servidor para la fragmentación.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)  
  

Vea tambiénSee Also

Funciones y vistas de administración dinámica (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
Funciones y vistas de administración dinámica relacionadas con índices (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)
Vistas (del sistema TRANSACT-SQL)System Views (Transact-SQL)