sys.dm_db_index_physical_stats

Actualizado: 15 de septiembre de 2007

Devuelve información de tamaño y fragmentación de los datos y los índices de la tabla o vista especificada. En el caso de un índice, se devuelve una fila por cada nivel de árbol b en cada partición. En el caso de un montón, se devuelve una fila para la unidad de asignación IN_ROW_DATA en cada partición. 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. 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. Para obtener información acerca de particiones y unidades de asignación, vea Arquitectura de tablas e índices.

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

• database_id | NULL | 0 | DEFAULT
  Es el Id. de la base de datos. database_id es de tipo smallint. Las entradas válidas son el número de identificador de una base de datos, NULL, 0 y DEFAULT. El valor predeterminado es 0. NULL, 0 y DEFAULT son valores equivalentes en este contexto.

  Especifique NULL para devolver información de todas las bases de datos de la instancia de SQL Server. Si especifica NULL para database_id, también debe especificar NULL para object_id, index_id y partition_number.

  Se puede especificar la función integrada DB_ID. 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.

• object_id | NULL | 0 | DEFAULT
  Es el Id. de objeto de la tabla o vista en la que está el índice. object_id es de tipo int.

  Las entradas válidas son el número de identificador de una tabla o vista, NULL, 0 y DEFAULT. El valor predeterminado es 0. NULL, 0 y DEFAULT son valores equivalentes en este contexto.

  Especifique NULL para obtener información sobre todas las tablas y vistas en la base de datos especificada. Si especifica NULL para object_id, también debe especificar NULL para index_id y partition_number.

• index_id | 0 | NULL |-1 | DEFAULT
  Es el Id. del índice. index_id es de tipo int. Las entradas válidas son el número de Id. de un índice, 0 si object_id es un montón, NULL, -1 o DEFAULT. El valor predeterminado es -1. NULL, -1 y DEFAULT son valores equivalentes en este contexto.

  Especifique NULL para obtener información sobre todos los índices de una tabla o vista. Si especifica NULL para index_id, también debe especificar NULL para partition_number.

• partition_number | NULL | 0 | DEFAULT
  Es el número de particiones del objeto. partition_number es de tipo int. Las entradas válidas son el número de partición (partion_number) de un índice o montón, NULL, 0 o DEFAULT. El valor predeterminado es 0. NULL, 0 y DEFAULT son valores equivalentes en este contexto.

  Especifique NULL para obtener información sobre todas las particiones del objeto propietario.

  partition_number se basa en 1. Un montón o índice sin particiones tiene el valor de partition_number establecido en 1.

• mode | NULL | DEFAULT
  Es el nombre del modo. mode especifica el nivel de recorrido utilizado para obtener datos estadísticos. mode es de tipo sysname. Las entradas válidas son DEFAULT, NULL, LIMITED, SAMPLED o DETAILED. El valor predeterminado (NULL) es LIMITED.

Tabla devuelta

Nombre de columna

Tipo de datos

Descripción

database_id

smallint

Id. de base de datos de la tabla o vista.

object_id

int

Id. de objeto de la tabla o vista en la que está el índice.

index_id

int

Id. de índice.

0 = Montón.

partition_number

int

Número de partición basado en 1 en el objeto propietario, una tabla, vista o índice.

1 = Montón o índice sin particiones.

index_type_desc

nvarchar(60)

Descripción del tipo de índice:

HEAP

CLUSTERED INDEX

NONCLUSTERED INDEX

PRIMARY XML INDEX

XML INDEX

alloc_unit_type_desc

nvarchar(60)

Descripción del tipo de unidad de asignación:

IN_ROW_DATA

LOB_DATA

ROW_OVERFLOW_DATA

La unidad de asignación LOB_DATA contiene los datos almacenados en columnas de tipo text, ntext, image, varchar(max), nvarchar(max), varbinary(max) y xml. Para obtener más información, vea Tipos de datos (Transact-SQL).

La unidad de asignación ROW_OVERFLOW_DATA contiene los datos almacenados en columnas de tipo varchar(n), nvarchar(n), varbinary(n) y sql_variant que se han insertado de manera no consecutiva. Para obtener más información, vea Datos de desbordamiento de fila superiores a 8 KB.

index_depth

tinyint

Número de niveles de índice.

1 = Montón, o unidad de asignación LOB_DATA o ROW_OVERFLOW_DATA.

index_level

tinyint

Nivel actual del índice.

0 para niveles de hoja del índice, montones y unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA.

Mayor que 0 para niveles de índice no hoja. index_level será el nivel más alto en el nivel raíz de un índice.

Los niveles no hoja de los índices sólo se procesan cuando mode = DETAILED.

avg_fragmentation_in_percent

float

Fragmentación lógica para índices o fragmentación de extensión para montones en la unidad de asignación IN_ROW_DATA.

El valor se mide como un porcentaje y tiene en cuenta varios archivos. Para obtener definiciones de fragmentación lógica y de extensión, vea la sección Notas.

0 para unidades de asignación LOB_DATA y ROW_OVERFLOW_DATA.

NULL para montones cuando mode = SAMPLED.

fragment_count

bigint

Número de fragmentos en el nivel hoja de una unidad de asignación IN_ROW_DATA. Para obtener más información sobre los fragmentos, vea la sección Notas.

NULL para niveles no hoja de un índice y unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA.

NULL para montones cuando mode = SAMPLED.

avg_fragment_size_in_pages

float

Promedio de páginas en un fragmento en el nivel hoja de una unidad de asignación IN_ROW_DATA.

NULL para niveles no hoja de un índice y unidades de asignación LOB_DATA o ROW_OVERFLOW_DATA.

NULL para montones cuando mode = SAMPLED.

page_count

bigint

Número total de páginas de datos o de índice.

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.

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.

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.

avg_page_space_used_in_percent

float

Porcentaje medio del espacio de almacenamiento de datos disponible utilizado en todas las páginas.

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.

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.

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.

NULL cuando mode = LIMITED.

record_count

bigint

Número total de registros.

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.

En el caso de un montón, el número total de registros en la unidad de asignación IN_ROW_DATA.

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.

NULL cuando mode = LIMITED.

Nota:

Para un montón, el número de registros devuelto por esta función podría no coincidir con el número de filas devuelto al ejecutar una instrucción SELECT COUNT(*) en dicho montón. Esto es así porque una fila puede contener varios registros. Por ejemplo, bajo algunas situaciones de actualización, una única fila del montón puede tener un registro de reenvío y un registro reenviado como resultado de la operación de actualización. Asimismo, las filas LOB más grandes se dividen en varios registros de almacenamiento de tipo LOB_DATA.

ghost_record_count

bigint

Número de registros fantasma listos para que la tarea de limpieza de registros fantasma los elimine de la unidad de asignación.

0 para niveles no hoja de un índice en la unidad de asignación IN_ROW_DATA.

NULL cuando mode = LIMITED.

version_ghost_record_count

bigint

Número de registros fantasma retenidos por una transacción de aislamiento de instantánea pendiente en una unidad de asignación.

0 para niveles no hoja de un índice en la unidad de asignación IN_ROW_DATA.

NULL cuando mode = LIMITED.

min_record_size_in_bytes

int

Tamaño mínimo del registro en 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.

En el caso de un montón, el tamaño mínimo del registro en la unidad de asignación IN_ROW_DATA.

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.

NULL cuando mode = LIMITED.

max_record_size_in_bytes

int

Tamaño máximo del registro en 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.

En el caso de un montón, el tamaño máximo del registro en la unidad de asignación IN_ROW_DATA.

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.

NULL cuando mode = LIMITED.

avg_record_size_in_bytes

float

Promedio de tamaño del registro en 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.

En el caso de un montón, el promedio de tamaño del registro en la unidad de asignación IN_ROW_DATA.

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.

NULL cuando mode = LIMITED.

forwarded_record_count

bigint

Número de registros de un montón que tiene punteros de reenvío que señalan otra ubicación de datos. Este estado se produce durante una actualización, cuando no existe suficiente espacio para almacenar la nueva fila en la ubicación original.

NULL para todas las unidades de asignación salvo IN_ROW_DATA para un montón.

NULL para montones cuando mode = LIMITED.

Notas

La función de administración dinámica sys.dm_db_index_physical_stats sustituye a la instrucción DBCC SHOWCONTIG. Esta función de administración dinámica no acepta parámetros correlacionados de CROSS APPLY y OUTER APPLY.

Modos de recorrido

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. mode se especifica como LIMITED, SAMPLED o DETAILED. La función recorre las cadenas de páginas buscando las unidades de asignación que conforman las particiones especificadas de la tabla o índice. sys.dm_db_index_physical_stats sólo requiere un bloqueo de tabla de intención compartida (IS), independientemente del modo en que se ejecute. Para obtener más información acerca de los bloqueos, vea Modos de bloqueo.

El modo LIMITED es el más rápido y recorre el menor número de páginas. Recorre todas las páginas para un montón, pero sólo las páginas del nivel primario para un índice, que son las páginas por encima del nivel hoja.

El modo SAMPLED devuelve estadísticas basadas en un ejemplo de 1% de todas las páginas en el índice o montón. Si el índice o montón tiene menos de 10.000 páginas, se utiliza el modo DETAILED en lugar del modo SAMPLED.

El modo DETAILED recorre todas las páginas y devuelve todas las estadísticas.

Los modos son progresivamente más lentos desde LIMITED a DETAILED, ya que se va realizando más trabajo en cada nodo. Para analizar rápidamente el nivel de fragmentación o tamaño de una tabla o índice, utilice el modo LIMITED. 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.

Usar funciones del sistema para especificar valores de parámetros

Puede utilizar las funciones de Transact-SQL DB_ID y OBJECT_ID para especificar un valor para los parámetros database_id y object_id. Sin embargo, el envío de valores no válidos a estas funciones puede provocar resultados no deseados. 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. La función sys.dm_db_index_physical_stats interpreta NULL como un valor comodín que especifica todas las bases de datos y todos los objetos.

Además, la función OBJECT_ID se procesa antes de que se llame a sys.dm_db_index_physical_stats y, por tanto, se evalúa en el contexto de la base de datos actual, no el de la base de datos especificada en 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. En los siguientes ejemplos se ilustran estos resultados no deseados.

USE master;
GO
-- In this example, OBJECT_ID is evaluated in the context of the master database. 
-- Because Person.Address does not exist in master, the function returns NULL.
-- When NULL is specified as an object_id, all objects in the database are returned.
-- The same results are returned when an object that is not valid is specified.
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- This example demonstrates the results of specifying a valid object name
-- that exists in both the current database context and
-- in the database specified in the database_id parameter of the 
-- sys.dm_db_index_physical_stats function.
-- An error is returned because the ID value returned by OBJECT_ID does not
-- match the ID value of the object in the specified database.
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- Clean up temporary database.
DROP DATABASE Test;
GO

Práctica recomendada

Asegúrese siempre de que se devuelve un identificador válido al utilizar DB_ID u OBJECT_ID. Por ejemplo, al utilizar OBJECT_ID, especifique un nombre de tres partes, como OBJECT_ID(N'AdventureWorks.Person.Address'), o bien pruebe el valor devuelto por las funciones antes de utilizarlo en la función sys.dm_db_index_physical_stats. En los ejemplos A y B siguientes se ilustra una forma segura de especificar identificadores de objetos y bases de datos.

Detectar la fragmentación

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. 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. Para las consultas que recorren parcial o totalmente los índices de una tabla, este tipo de fragmentación puede producir lecturas de páginas adicionales. Esto impide el recorrido paralelo de los datos.

El algoritmo para calcular la fragmentación es más preciso en SQL Server 2005 que en SQL Server 2000. En consecuencia, los valores de fragmentación serán aparentemente mayores. Por ejemplo, en SQL Server 2000, una tabla no se considera fragmentada si las páginas 11 y 13, pero no la página 12, están en la misma extensión. No obstante, tener acceso a estas dos páginas requeriría dos operaciones de E/S físicas, por ello se cuenta como fragmentación en SQL Server 2005.

El nivel de fragmentación de un índice o montón aparece en la columna avg_fragmentation_in_percent. En el caso de montones, el valor representa la fragmentación de extensión del montón. En el caso de índices, el valor representa la fragmentación lógica del índice. 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.

Fragmentación lógica

Se trata del porcentaje de páginas sin orden en las páginas hoja de un índice. 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.

Fragmentación de extensión

Se trata del porcentaje de extensiones sin orden en las páginas hoja de un montón. 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.

El valor de avg_fragmentation_in_percent debe ser lo más cercano posible a cero para obtener un rendimiento máximo. No obstante, los valores de 0% a 10% son aceptables. Puede utilizar todos los métodos de reducción de la fragmentación (por ejemplo, reconstruir, reorganizar o volver a crear) para disminuir estos valores. Para obtener más información acerca de cómo analizar el grado de fragmentación en un índice, vea Reorganizar y volver a generar índices.

Reducir la fragmentación en un índice

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:

• Quite y vuelva a crear el índice agrupado.
  La reconstrucción de un índice agrupado redistribuye los datos, lo que ocasiona que las páginas de datos se llenen. El grado de llenado se puede configurar mediante la opción FILLFACTOR en 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. Si se interrumpe la creación del índice, éste no se vuelve a crear. Para obtener más información, vea 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. Se trata de una operación en línea, por lo que el índice está disponible mientras se ejecuta la instrucción. La operación también se puede interrumpir sin perder el trabajo que ya se ha completado. 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.
• Utilice ALTER INDEX REBUILD, el sustituto de DBCC DBREINDEX, para volver a generar el índice con conexión o sin conexión. Para obtener más información, vea ALTER INDEX (Transact-SQL).

La fragmentación sola no es una razón suficiente para reorganizar o volver a generar un índice. 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. Esto se traduce en tiempos de respuesta más lentos. 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. Para obtener más información, vea el sitio Web de Microsoft.

[!NOTA] Si ejecuta DBCC SHRINKFILE o DBCC SHRINKDATABASE, puede dar lugar a una fragmentación si un índice se mueve parcial o totalmente durante la operación de reducción. Por esta razón, si debe realizar una operación de reducción, no debe hacerlo después de eliminar la fragmentación.

Reducir la fragmentación en un montón

Para reducir la fragmentación de extensión de un montón, cree un índice agrupado en la tabla y, a continuación, quítelo. Con esta acción se redistribuyen los datos mientras se crea el índice agrupado. Esto también optimiza la distribución del espacio libre disponible en la base de datos. Cuando el índice agrupado se quita para volver a crear el montón, los datos no se mueven y permanecen de forma óptima en su posición. Para obtener información acerca de cómo realizar estas operaciones, vea CREATE INDEX y DROP INDEX.

Compactar datos de objetos grandes

De forma predeterminada, la instrucción ALTER INDEX REORGANIZE compacta las páginas que contienen datos de objetos grandes (LOB). 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.

Si reorganiza un índice agrupado específico, se compactan todas las columnas LOB incluidas en el índice agrupado. Si reorganiza un índice no agrupado, se compactan todas las columnas LOB sin clave incluidas en el índice. Cuando se especifica ALL en la instrucción, todos los índices asociados a la tabla o vista especificada se reorganizan. Asimismo, se compactan todas las columnas LOB asociadas al índice agrupado, la tabla subyacente o el índice no agrupado que contiene columnas.

Evaluar el uso del espacio en disco

La columna avg_page_space_used_in_percent indica el llenado de la página. 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. No obstante, un índice que tenga muchas inserciones aleatorias y páginas muy llenas tendrá más divisiones de páginas. Esto causa más fragmentación. Por lo tanto, para reducir las divisiones de páginas, el valor debe ser inferior a 100%. Si reconstruye 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. Para obtener más información acerca del factor de relleno, vea Factor de relleno. Asimismo, ALTER INDEX REORGANIZE compactará un índice intentando rellenar las páginas según el último valor FILLFACTOR especificado. Esto aumenta el valor en avg_space_used_in_percent. Tenga en cuenta que ALTER INDEX REORGANIZE no puede reducir el llenado de página. Para ello, deberá volver a generar el índice.

Evaluar fragmentos de índice

Un fragmento se compone de páginas hoja consecutivas físicamente en el mismo archivo para una unidad de asignación. Un índice tiene al menos un fragmento. 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. El uso de fragmentos mayores indica que se necesita menos E/S de disco para leer el mismo número de páginas. Por lo tanto, cuanto mayor sea el valor de avg_fragment_size_in_pages, mejor será el rendimiento del recorrido de intervalos. Los valores avg_fragment_size_in_pages y avg_fragmentation_in_percent son inversamente proporcionales entre sí. Por lo tanto, si vuelve a generar o reorganiza un índice, se debe reducir la cantidad de fragmentación y aumentar el tamaño de los fragmentos.

Permisos

Necesita los siguientes permisos:

• El permiso CONTROL sobre el objeto especificado en la base de datos.
• El permiso VIEW DATABASE STATE para devolver información acerca de todos los objetos de la base de datos especificada, mediante el uso del comodín de objeto @object_id=NULL.
• El permiso VIEW SERVER STATE para devolver información acerca de todas las bases de datos, mediante el uso del comodín de base de datos @database_id=NULL.

El permiso VIEW DATABASE STATE permite devolver todos los objetos de la base de datos, independientemente de los permisos CONTROL denegados para objetos específicos.

Si se deniega el permiso VIEW DATABASE STATE, no se puede devolver ningún objeto de la base de datos, independientemente de los permisos CONTROL concedidos a objetos específicos. Además, si se especifica el carácter comodín de base de datos @database_id=NULL, la base de datos se omite.

Para obtener más información, vea Funciones y vistas de administración dinámica.

Ejemplos

A. Se devuelve información acerca de una tabla especificada

El ejemplo siguiente devuelve estadísticas de fragmentación y tamaño de todos los índices y particiones de la tabla Person.Address en la base de datos AdventureWorks. El modo de recorrido se establece en 'LIMITED' para obtener el mejor rendimiento y limitar las estadísticas devueltas. Para ejecutar esta consulta, es necesario, como mínimo, el permiso CONTROL sobre la tabla Person.Address.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;

SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.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. Se devuelve información acerca de un montón

El ejemplo siguiente devuelve todas las estadísticas para el montón dbo.DatabaseLog en la base de datos AdventureWorks. 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. Para ejecutar esta consulta, es necesario, como mínimo, el permiso CONTROL sobre la tabla dbo.DatabaseLog.

DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'AdventureWorks');
SET @object_id = OBJECT_ID(N'AdventureWorks.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. Se devuelve información acerca de todas las bases de datos

En el ejemplo siguiente se devuelven todas las estadísticas de todas las tablas e índices de la instancia de SQL Server; para ello, se especifica el comodín NULL en todos los parámetros. Para ejecutar esta consulta necesita el permiso VIEW SERVER STATE.

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

D. Usar sys.dm_db_index_physical_stats en una secuencia de comandos para volver a generar o reorganizar índices

El ejemplo siguiente reorganiza o vuelve a generar automáticamente todas las particiones de una base de datos que tengan un promedio de fragmentación superior al 10%. Para ejecutar esta consulta necesita el permiso VIEW DATABASE STATE. Este ejemplo especifica DB_ID como el primer parámetro sin especificar un nombre de base de datos. Se generará un error si la base de datos actual tiene un nivel de compatibilidad de 80 o inferior. Para solucionar el error, reemplace DB_ID() con un nombre de base de datos válido. Para obtener más información acerca de los niveles de compatibilidad de base de datos, vea sp_dbcmptlevel (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

Vea también

Referencia

Funciones y vistas de administración dinámica
Funciones y vistas de administración dinámica relacionadas con índices
sys.dm_db_index_operational_stats
sys.dm_db_index_usage_stats
sys.dm_db_partition_stats
sys.allocation_units (Transact-SQL)
Vistas del sistema (Transact-SQL)

Otros recursos

Organización de tablas e índices

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios

Versión	Historial
15 de septiembre de 2007	Contenido modificado: Se agregó una nota en record_count que explica la posibilidad de que el número de registros devuelto no coincida con el número de filas de un montón. Se mejoró la definición de forwarded_record_count.
17 de julio de 2006	Contenido nuevo: Se ha agregado información sobre el algoritmo para calcular la fragmentación en la sección "Detectar la fragmentación". Se han aclarado los requisitos de nivel de compatibilidad de base de datos para usar use DB_ID() en el primer parámetro.
14 de abril de 2006	Contenido modificado: Se agregaron los valores de entrada 0 y DEFAULT a la sintaxis de database_id, object_id, y partition_number. Se agregaron los valores de entrada -1 y DEFAULT a la sintaxis de index_id. Se corrigió el ejemplo D.
5 de diciembre de 2005	Contenido nuevo: Se ha agregado la sección "Utilizar funciones del sistema para especificar valores de parámetros". Contenido modificado: Se ha modificado la sección Permisos. Se ha corregido la descripción de la fragmentación lógica.