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 examen
El modo en que se ejecuta la función determina el nivel de examen 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 producen las particiones especificadas de la tabla o el índice. sys.dm_db_index_physical_stats requiere únicamente un bloqueo de tabla con 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 modo más rápido y examina el menor número de páginas. Para un índice, solamente se examinan las páginas del nivel primario del árbol b (es decir, las páginas sobre el nivel hoja). Para un montón, solo se examinan las páginas PFS e IAM asociadas; no se examinan las páginas de datos del montón. En SQL Server 2005, se examinan todas las páginas de un montón en el modo LIMITED.
Con el modo LIMITED, compressed_page_count es NULL porque el Database Engine (Motor de base de datos) solamente examina las páginas no hoja del árbol b y las páginas IAM y PFS del montón. 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. 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 examina todas las páginas y devuelve todas las estadísticas.
Los modos son progresivamente más lentos de 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 a fin de 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 o todos los objetos.
Además, la función OBJECT_ID se procesa antes de llamar a la función sys.dm_db_index_physical_stats y, por lo tanto, se evalúa en el contexto de la base de datos actual, no en 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 de que se devuelva un identificador válido cuando utilice DB_ID u OBJECT_ID. Por ejemplo, cuando utilice 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 utilizarlos 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 examinan parcial o totalmente los índices de una tabla, este tipo de fragmentación puede producir lecturas de páginas adicionales. Esto impide el examen paralelo de los datos.
El algoritmo para calcular la fragmentación es más preciso en SQL Server 2008 que en SQL Server 2000. En consecuencia, los valores de fragmentación parecerán mayores. Por ejemplo, en SQL Server 2000, no se considera que una tabla está fragmentada si tiene las páginas 11 y 13 en la misma extensión y no la página 12. Sin embargo, el acceso a estas dos páginas requeriría dos operaciones de E/S físicas, por lo que en SQL Server 2008 esto se considera fragmentación.
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, volver a generar, 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 clúster.
La reconstrucción de un índice clúster redistribuye los datos, lo que ocasiona que las páginas de datos se llenen. El grado de llenado puede configurarse con la opción FILLFACTOR de 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, no vuelve a crearse. 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 puede interrumpirse sin perder el trabajo ya 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 generar un índice, y que no actualiza las estadísticas.
-
Utilice ALTER INDEX REBUILD, el sustituto de DBCC DBREINDEX, para volver a generar el índice en línea o sin conexión. Para obtener más información, vea ALTER INDEX (Transact-SQL).
La fragmentación por sí 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 exámenes de índice. Esto se traduce en tiempos de respuesta más lentos. Si la carga de trabajo de consultas en un índice o tabla fragmentados no implica procesos de examen, debido a que la carga de trabajo incluye principalmente búsquedas singleton, es posible que la operación de quitar la fragmentación no tenga ninguna consecuencia. Para obtener más información, vea el sitio web de Microsoft.
.gif) 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. Por esta razón, si tiene que realizar una operación de reducción, no debe realizarla después de quitar 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 clúster en la tabla y, a continuación, quítelo. Con esta acción se redistribuyen los datos mientras se crea el índice clúster. Esto también optimiza la distribución del espacio disponible en la base de datos. 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. 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 clúster específico, se compactan todas las columnas LOB incluidas en el índice clúster. Si reorganiza un índice no clúster, 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 clúster, la tabla subyacente o el índice no clúster 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 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. 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 de FILLFACTOR especificado. Esto aumenta el valor de 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 examen 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, debe reducir la cantidad de fragmentación y aumentar el tamaño de los fragmentos.