Nuevamente le muestro un resumen traducido del artículo "Stairway to SQL Server Indexes: Level 11, Index Fragmentation" de David Durant, 2012/02/01 SQL Server Central y por supuesto información que agrego de mis experiencias con la herramienta ^_^
¿Qué es la fragmentación?
La fragmentación del índice se presenta en dos variedades: la fragmentación interna y fragmentación externa. La mejor herramienta para la determinación de la fragmentación, ya sea interna o externa de un índice es la función de administración dinámica sys.dm_db_index_physical_stats. Debido a que esta función muestra el identificador de índice pero no el nombre del índice, las consultas a menudo se unen a la vista sys.indexes, a fin de incluir el nombre del índice en la salida.
Fragmentación Interna
Cada página puede contener un número determinado de entradas. Eso no significa que una página siempre tiene el número máximo de entradas. Por lo general, una página de índice no está completamente lleno. Cuando decimos índice de fragmentación interna, nos referimos a que las páginas no están completamente llenas. La cantidad promedio de espacio ocupado por página en un índice es una medida de la fragmentación interna del índice. Tenga en cuenta que cuanto mayor sea la medición, menor será la fragmentación interna, una página que es de 100% de su capacidad no tiene fragmentación interna.
La fragmentación interna es normalmente expresada como un porcentaje, e indica la plenitud de bytes, no en las entradas. Por lo tanto, una página de índice que tiene una fragmentación interna de 96% podría estar completamente lleno. Es decir, un 4% de la página podría no ser suficiente espacio para una nueva entrada que se añade. Cuando el espacio ocupado por la información de encabezado de página y la página de consejos de compensación se explica, una página cuyo índice de entradas individuales son relativamente grandes puede ser "completo" en un 90%, 80% o incluso menos ..
La función sys.dm_db_index_physical_stats muestra la fragmentación interna en su columna de salida avg_page_space_used_in_percent. Ejemplo:
¿Qué es la fragmentación?
La fragmentación del índice se presenta en dos variedades: la fragmentación interna y fragmentación externa. La mejor herramienta para la determinación de la fragmentación, ya sea interna o externa de un índice es la función de administración dinámica sys.dm_db_index_physical_stats. Debido a que esta función muestra el identificador de índice pero no el nombre del índice, las consultas a menudo se unen a la vista sys.indexes, a fin de incluir el nombre del índice en la salida.
Fragmentación Interna
Cada página puede contener un número determinado de entradas. Eso no significa que una página siempre tiene el número máximo de entradas. Por lo general, una página de índice no está completamente lleno. Cuando decimos índice de fragmentación interna, nos referimos a que las páginas no están completamente llenas. La cantidad promedio de espacio ocupado por página en un índice es una medida de la fragmentación interna del índice. Tenga en cuenta que cuanto mayor sea la medición, menor será la fragmentación interna, una página que es de 100% de su capacidad no tiene fragmentación interna.
La fragmentación interna es normalmente expresada como un porcentaje, e indica la plenitud de bytes, no en las entradas. Por lo tanto, una página de índice que tiene una fragmentación interna de 96% podría estar completamente lleno. Es decir, un 4% de la página podría no ser suficiente espacio para una nueva entrada que se añade. Cuando el espacio ocupado por la información de encabezado de página y la página de consejos de compensación se explica, una página cuyo índice de entradas individuales son relativamente grandes puede ser "completo" en un 90%, 80% o incluso menos ..
La función sys.dm_db_index_physical_stats muestra la fragmentación interna en su columna de salida avg_page_space_used_in_percent. Ejemplo:
SELECT IX.name AS 'Name'
, PS.index_level AS 'Level'
, PS.page_count AS 'Pages'
, PS.avg_page_space_used_in_percent AS 'Page Fullness (%)'
FROM sys.dm_db_index_physical_stats(
DB_ID(),
OBJECT_ID('Sales.SalesOrderDetail'),
DEFAULT, DEFAULT, 'DETAILED') PS
JOIN sys.indexes IX
ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id
WHERE IX.name = 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID';
GO
, PS.index_level AS 'Level'
, PS.page_count AS 'Pages'
, PS.avg_page_space_used_in_percent AS 'Page Fullness (%)'
FROM sys.dm_db_index_physical_stats(
DB_ID(),
OBJECT_ID('Sales.SalesOrderDetail'),
DEFAULT, DEFAULT, 'DETAILED') PS
JOIN sys.indexes IX
ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id
WHERE IX.name = 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID';
GO
La primera fila de la salida anterior, nos dice que las filas de la tabla se reparten en 1.234 páginas que son, en promedio, el 99% de su capacidad. En otras palabras, esta tabla tiene un mínimo de fragmentación interna.
Fragmentación Externa
En contraste con la fragmentación interna, la fragmentación externa se refiere a la falta de correlación entre la secuencia lógica de un índice y su secuencia física. También se expresa en porcentajes. Para citar a Microsoft Technet, es "el porcentaje de páginas fuera de orden (out-of-order pages) en las páginas de la hoja de un índice. Una página fuera de orden es una página donde la siguiente página física asignada al índice no es la página a la que apunta el puntero de página-siguiente (next-page) en la página de la hoja actual". Aunque la definición se limita solo a nivel de hoja, se dará cuenta de que sys.dm_db_index_physical_stats puede devolver información de todos los niveles de fragmentación de un índice.
Tenga en cuenta que, a diferencia de la fragmentación interna, un número más alto significa una mayor cantidad de fragmentación externa, por lo que un índice con una fragmentación externa de 100% esta completamente fragmentada externamente. Es decir, no hay correlación alguna entre su secuencia lógica y física de las páginas.
En contraste con la fragmentación interna, la fragmentación externa se refiere a la falta de correlación entre la secuencia lógica de un índice y su secuencia física. También se expresa en porcentajes. Para citar a Microsoft Technet, es "el porcentaje de páginas fuera de orden (out-of-order pages) en las páginas de la hoja de un índice. Una página fuera de orden es una página donde la siguiente página física asignada al índice no es la página a la que apunta el puntero de página-siguiente (next-page) en la página de la hoja actual". Aunque la definición se limita solo a nivel de hoja, se dará cuenta de que sys.dm_db_index_physical_stats puede devolver información de todos los niveles de fragmentación de un índice.
Tenga en cuenta que, a diferencia de la fragmentación interna, un número más alto significa una mayor cantidad de fragmentación externa, por lo que un índice con una fragmentación externa de 100% esta completamente fragmentada externamente. Es decir, no hay correlación alguna entre su secuencia lógica y física de las páginas.
SELECT IX.name AS 'Name',
PS.index_level AS 'Level'
PS.page_count AS 'Pages',
PS.avg_fragmentation_in_percent AS 'External Fragmentation (%)',
PS.fragment_count AS 'Fragments',
PS.avg_fragment_size_in_pages AS 'Avg Fragment Size'
FROM sys.dm_db_index_physical_stats(
DB_ID(),
OBJECT_ID('Sales.SalesOrderDetail'),
DEFAULT, DEFAULT, 'LIMITED') PS
JOIN sys.indexes IX
ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id
WHERE IX.name = 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID';
GO
PS.index_level AS 'Level'
PS.page_count AS 'Pages',
PS.avg_fragmentation_in_percent AS 'External Fragmentation (%)',
PS.fragment_count AS 'Fragments',
PS.avg_fragment_size_in_pages AS 'Avg Fragment Size'
FROM sys.dm_db_index_physical_stats(
DB_ID(),
OBJECT_ID('Sales.SalesOrderDetail'),
DEFAULT, DEFAULT, 'LIMITED') PS
JOIN sys.indexes IX
ON IX.OBJECT_ID = PS.OBJECT_ID AND IX.index_id = PS.index_id
WHERE IX.name = 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID';
GO
Como se especificó 'LIMITED' para el último parámetro, tendremos sólo una fila de salida:
Prevención y Cura para la fragmentación y la división de página
Hay dos opciones para el mantenimiento de índices, la reconstrucción del índice, lo que elimina la fragmentación externa y le da el control sobre el llenado de la página del índice en reconstrucción, y la reorganización del índice, lo que reduce la fragmentación externa y puede aumentar la plenitud de la página.
ALTER INDEX AK_SalesOrderHeader_rowguid
ON Sales.SalesOrderHeader
REBUILD
WITH ( FILLFACTOR = 7 );
GO
Hay dos opciones para el mantenimiento de índices, la reconstrucción del índice, lo que elimina la fragmentación externa y le da el control sobre el llenado de la página del índice en reconstrucción, y la reorganización del índice, lo que reduce la fragmentación externa y puede aumentar la plenitud de la página.
ALTER INDEX AK_SalesOrderHeader_rowguid
ON Sales.SalesOrderHeader
REBUILD
WITH ( FILLFACTOR = 7 );
GO
FILLFACTOR (Factor de Relleno): se utiliza para afinar almacenamiento de datos para los índices y el rendimiento de éste. Cuando se crea un índice o este es reconstruido, el valor del FILLFACTOR determina el porcentaje de espacio en cada página a nivel de hoja para ser llenada con los datos, reservando el resto en cada página como espacio libre para un futuro crecimiento. Por ejemplo, especificando un FILLFACTOR de 80 significa que el 20 por ciento de todas las páginas se deja vacío, proporcionando un espacio para la expansión del índice cuando se agregan datos a la tabla subyacente. El espacio vacío se reserva entre las filas del índice en lugar de al final del índice.
El valor del FILLFACTOR es un porcentaje de 1 a 100, y el valor por defecto a nivel de servidor es 0, lo que significa que las páginas se llenan a capacidad.
Este valor lo debemos utilizar cuando tenemos mucho conocimientos del comportamiento de muestra tabla, por ejemplo, la cantidad de inserciones, actualizaciones, etc. Si tenemos una tabla a la cual solo realizaremos operaciones de INSERT y/o SELECT, el FILLFACTOR podría ser de 100% para llenar completamente las páginas y evitar la fragmentación.
El valor del FILLFACTOR es un porcentaje de 1 a 100, y el valor por defecto a nivel de servidor es 0, lo que significa que las páginas se llenan a capacidad.
Este valor lo debemos utilizar cuando tenemos mucho conocimientos del comportamiento de muestra tabla, por ejemplo, la cantidad de inserciones, actualizaciones, etc. Si tenemos una tabla a la cual solo realizaremos operaciones de INSERT y/o SELECT, el FILLFACTOR podría ser de 100% para llenar completamente las páginas y evitar la fragmentación.
Para información más detallada ingresa al link original, espero les sea de ayuda.
