En este artículo, aprenderemos a identificar y resolver la fragmentación de índices en SQL Server. La identificación de la fragmentación del índice y el mantenimiento del índice son partes importantes de la tarea de mantenimiento de la base de datos. Microsoft SQL Server sigue actualizando las estadísticas del índice con la actividad de Inserción, Actualización o Eliminación sobre la tabla. La fragmentación del índice es el valor de rendimiento del índice en porcentaje, que puede ser obtenido por SQL Server DMV. De acuerdo con el valor de rendimiento del índice, los usuarios pueden tomar los índices en el mantenimiento mediante la revisión del porcentaje de fragmentación con la ayuda de la operación Rebuild o Reorganizar.
¿Por qué el porcentaje de fragmentación del índice varía?
El porcentaje de fragmentación del índice varía cuando las órdenes de la página lógica no coordinan con el orden de la página física en la asignación de la página de un índice. Con la modificación de datos en la tabla, la información puede cambiar de tamaño en la página de datos. La página estaba llena hasta arriba antes de la operación de actualización de la tabla. Sin embargo, se puede encontrar espacio libre en la página de datos con una operación de actualización en la tabla. Los usuarios pueden observar la alteración del orden de la página con la operación de borrado masivo en la tabla. Junto con las operaciones de actualización y eliminación, la página de datos no será una página llena o vacía. Por lo tanto, el espacio libre no utilizado aumenta el desajuste de orden entre la página lógica y la página física con el aumento de la fragmentación, y eso puede causar el peor rendimiento de la consulta y consume más recursos del servidor también.
Más esencial para explicar que la fragmentación del índice afecta el rendimiento de la consulta sólo con la exploración de la página. En estos casos, aumenta las posibilidades de un mal rendimiento de otras peticiones SQL también, porque la consulta con el índice altamente fragmentado sobre la tabla tarda más tiempo en ejecutarse y consume más recursos como la caché, la CPU y el IO. Por lo tanto, el resto de las peticiones SQL tienen dificultades para terminar la operación con los recursos inconsistentes del servidor. Incluso puede producirse un bloqueo por la operación de Update y Delete porque el optimizador no recogerá la información de la fragmentación del índice mientras genera el plan de ejecución de la consulta.
Puede haber un número de índices creados en una misma tabla con la combinación de varias columnas, y cada índice puede tener un porcentaje de fragmentación diferente. Ahora, antes de hacerlo apropiado o tomar un índice en mantenimiento, los usuarios tienen que encontrar ese valor de umbral de la base de datos. La siguiente sentencia T-SQL es una forma eficiente de encontrarlo con los detalles del objeto.
Encuentra el estado de fragmentación del índice usando la sentencia T-SQL statement
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECT S.name as ‘Schema’,
T.name as ‘Table’,
I.name as ‘Index’,
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc
|
Aquí, podemos ver que el máximo porcentaje de fragmentación media se nota como el 99%, que debe ser comprometido con una acción para reducir la fragmentación con las opciones de REBUILD o REORGANIZE. REBUILD o REORGANIZE es el comando de mantenimiento del índice que puede ser ejecutado con la sentencia ALTER INDEX. Los usuarios pueden realizar este comando usando SSMS también.
Reconstruir y Reorganizar Índice usando SQL Server Management Studio (SSMS)
Encuentre y expanda la tabla en el Explorador de Objetos >> Abra los Índices >> Haga clic con el botón derecho del ratón en el índice de destino >> Reconstruir o Reorganizar.
Como se puede ver en la imagen anterior, RECONSTRUIR y REORGANIZAR son las dos opciones disponibles para realizar la operación de recorte sobre la página. Lo ideal es que esta operación se realice en las horas de menor actividad para evitar su impacto en otras transacciones y usuarios. Microsoft SQL Server Enterprise Edition admite las funciones de índice en línea y fuera de línea con el índice REBUILD.
REBUILD INDEX
INDEX REBUILD siempre deja caer el índice y lo reproduce con nuevas páginas de índice. Esta actividad puede ejecutarse en paralelo utilizando una opción en línea (Enterprise Edition) con el comando ALTER INDEX, que no afecta a las peticiones y tareas en ejecución de una tabla similar.
REBUILD El índice se puede establecer en línea o fuera de línea utilizando los siguientes comandos SQL:
|
1
2
3
4
5
|
–Comando básico de reconstrucción
ALTER INDEX Index_Name ON Table_Name REBUILD
–REBUILD Index with ONLINE OPTION
ALTER INDEX Index_Name ON Table_Name REBUILD WITH(ONLINE=ON) | WITH(ONLINE=ON)
|
Si un usuario realiza el REBUILD INDEX offline, entonces el recurso objeto (Tabla) del índice no será accesible hasta el final del proceso de REBUILD. También afecta a otras numerosas transacciones asociadas a este objeto. La operación de reconstrucción del índice recrea el índice. Por lo tanto, genera nuevas estadísticas y anexa los registros del índice en el archivo de registro de transacciones de la base de datos también.
Por ejemplo, antes de reconstruir el índice, tomemos la asignación actual de páginas para el índice de la base de datos AdventureWorks, la tabla Sales.OrderTracking y el índice con nombre IX_OrderTracking_CarrierTrackingNumber.
|
1
2
3
4
5
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id, previous_page_page_id, next_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Aquí, existen 1961 páginas en el archivo de la base de datos para este índice, y las primeras 5 páginas son las 861, 862, 1627, 1628 y 1904 en orden del número de página. Ahora, vamos a reconstruir el índice utilizando SSMS.
La operación de REBUILD del índice se completa con éxito y tomar referencias de asignación de páginas para el mismo índice con la ayuda de la misma consulta T-SQL de nuevo.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Después de reconstruir el índice, el recuento de páginas actualizado es de 1457, que antes era de 1961. Si compruebas las 5 primeras páginas asignadas del mismo índice, se ha modificado con las nuevas referencias de página. Se supone que el índice se ha caído y se ha hecho de nuevo. Debemos comprobar el porcentaje de fragmentación refrescado para el mismo índice, y como se puede ver a continuación, ahora es del 0,1%.
REBUILD clustered index sobre la tabla afecta a otros índices de la tabla también porque el REBUILD clustered index reconstruye el índice no clustered de la tabla también. Realiza la operación de reconstrucción en todos los índices de la tabla o base de datos juntos; un usuario puede utilizar el comando DBCC DBREINDEX().
|
1
|
DBCC DBREINDEX (‘DatabaseName’, ‘TableName’);
|
REORGANIZAR ÍNDICE
El comando REORGANIZAR ÍNDICE reordena la página del índice expulsando el espacio libre o no utilizado en la página. Idealmente, las páginas de índice se reordenan físicamente en el archivo de datos. REORGANIZE no expulsa y crea el índice, sino que simplemente reestructura la información de la página. REORGANIZE no tiene ninguna opción offline, y REORGANIZE no afecta a las estadísticas en comparación con la opción REBUILD. REORGANIZE realiza siempre en línea.
Por ejemplo, antes de realizar el REORGANIZE sobre el índice, tomemos la lectura de la fragmentación para la base de datos ‘AdventureWorks’, la tabla ‘Sales.OrderTracking’ y el índice llamado ‘IX_OrderTracking_SalesOrderID’.
Aquí, el porcentaje de fragmentación del índice es 98,39 antes de REORGANIZE. La siguiente lista en la imagen son las páginas de asignación al índice.
|
1
2
3
4
5
6
|
SELECT OBJECT_NAME(IX.object_id) as db_name, si.name, extent_page_id, allocated_page_page_id,
previous_page_page_id, next_page_page_id
FROM sys.dm_db_database_page_allocations(DB_ID(‘AdventureWorks’), OBJECT_ID(‘Sales.OrderTracking’),NULL, NULL, ‘DETAILED’) IX
INNER JOIN sys.indexes si on IX.object_id = si.object_id AND IX.index_id = si.index_id
WHERE si.name = ‘IX_OrderTracking_CarrierTrackingNumber’
ORDER BY allocated_page_page_id
|
Aquí, un total de 459 páginas se enumeran en la imagen anterior, y las cinco primeras páginas son 1065, 1068, 1069, 1944 y 1945. Ahora, realicemos el comando REORGANIZE en el índice utilizando la siguiente sentencia T-SQL y veamos de nuevo la asignación de páginas.
|
1
|
ALTER INDEX IX_OrderTracking_SalesOrderID ON Sales.OrderTracking REORGANIZE
|
Aquí, el recuento total de páginas se reduce a 331, que antes era de 459. Además, no vemos páginas nuevas en la lista de las cinco primeras páginas, lo que implica que los datos sólo se reestructuran, no se rellenan de nuevo. Incluso podría ver nuevas páginas también, sucede en la situación cuando el índice grande está muy fragmentado, y la reorganización sobre los datos utilizan una nueva página.
Para realizar la operación de REORGANIZAR índice en todos los índices de la tabla o base de datos juntos, el usuario puede utilizar el comando DBCC INDEXDEFRAG():
|
1
|
DBCC INDEXDEFRAG(‘NombreBase’, ‘NombreTabla’);
|
Como se ha visto, hay una diferencia sustancial entre el REBUILD de índices y el REORGANIZE. Aquí los usuarios pueden elegir una de las alternativas según el porcentaje de fragmentación del índice. Podemos entender que no hay estándares documentados; sin embargo, el administrador de la base de datos sigue la ecuación estándar según el requerimiento del tamaño del Índice y el tipo de información.
Determinación habitual del uso de la ecuación :
- Cuando el porcentaje de Fragmentación está entre 15-30: REORGANIZAR
- Cuando la Fragmentación es mayor de 30: REBUILD
La opción REBUILD es más útil con la opción ONLINE cuando la base de datos no está disponible para tomar el mantenimiento de los índices en horas no pico.
Conclusión
La Fragmentación del Índice es una fragmentación interna en el archivo de datos. Los parámetros fundamentales para un rendimiento rápido de su base de datos son la arquitectura de la base de datos, el diseño de la base de datos y la escritura de consultas. Un buen diseño de índices con mantenimiento siempre aumenta el rendimiento de las consultas en el motor de la base de datos.
- Autor
- Postes Recientes
Ver todos los comentarios de Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Cómo automatizar el particionamiento de tablas en SQL Server – 7 de julio de 2020
- Configuración de grupos de disponibilidad Always On de SQL Server en AWS EC2 – 6 de julio de 2020