Planes típicos de mantenimiento de SQL Server - Parte 1: Desfragmentación de índices automatizada

Es incorrecto considerar la base de datos como una unidad estable, ya que pueden surgir varias situaciones no deseadas con el tiempo: deterioro del rendimiento de la base de datos, mal funcionamiento, etc.

Para minimizar la ocurrencia de este tipo de situaciones, se crean planes de mantenimiento que aseguran la estabilidad y el óptimo funcionamiento de cualquier base de datos.

Entre las tareas del plan de mantenimiento se encuentran las siguientes:

  1. Desfragmentación de índice
  2. Actualizar estadísticas
  3. Realice una copia de seguridad y verifique la integridad lógica y física de todos los objetos en la base de datos

Consideremos la automatización de cada tarea.

Así que el primer punto…

Además de la fragmentación del sistema de archivos y la fragmentación de los archivos de registro, la fragmentación de los archivos de datos tiene un impacto significativo en el rendimiento de la base de datos:

Índice

    1. Fragmentación dentro de páginas de índice individuales

    Los espacios en blanco aparecen inevitablemente en las páginas después de insertar, actualizar o eliminar entradas. No hay nada de malo en eso, ya que la situación es bastante regular, pero hay una pequeña carga de trabajo...

    La longitud de la cuerda juega un papel muy importante. Por ejemplo, si una línea tiene más de media página, no se utilizará la mitad libre de la página. Como resultado, a medida que aumenta el número de filas, aumentará el espacio no utilizado en la base de datos.

    Con tal fragmentación es necesario luchar en la etapa de diseño del esquema, ed escoger tales tipos de los datos, que tendrían lugar compactamente en las páginas.

    2. Fragmentación dentro de estructuras de índice

    La razón principal de este tipo de fragmentación son las operaciones de división de páginas. Por ejemplo, según la estructura de la clave principal, se debe insertar una nueva fila en una página de índice específica, pero esta página no tiene suficiente espacio para acomodar los datos insertados.

    En este caso, se crea una nueva página, a la que se moverá aproximadamente la mitad de las entradas de la página anterior. La nueva página a menudo no es físicamente adyacente a la anterior y, por lo tanto, el sistema la denomina fragmentada.

    En cualquier caso, la fragmentación provoca un aumento del número de páginas que contienen la misma cantidad de información. Esto aumenta automáticamente el tamaño de la base de datos y el espacio no utilizado.

    Las consultas que acceden a índices fragmentados requieren más operaciones de E/S. Además, la fragmentación impone costos adicionales en la memoria del propio servidor, que debe almacenar en caché páginas adicionales.

    Para evitar la fragmentación, SQL Server proporciona comandos para reorganizar y recuperar índices.

    Reconstruir el índice implica eliminar el anterior y crear una nueva instancia del índice en la que los datos de las páginas se comprimen y organizan de la manera más continua posible. Es importante tener en cuenta que la operación para restaurar el índice es bastante costosa.

    Por lo tanto, en caso de que la fragmentación sea insignificante, es recomendable reorganizar el índice existente. Esta operación requiere menos recursos del sistema que la reindexación y es una reorganización de páginas a nivel de hoja. Además, la reorganización, si es posible, comprime las páginas de índice.

    El nivel de fragmentación del índice se puede encontrar en la vista dinámica del sistema: sys.dm_db_index_physical_stats:

    SELECT *
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
    WHERE avg_fragmentation_in_percent > 0

    En esta consulta, el último parámetro especifica un modo cuyo valor le permite determinar rápidamente, pero no exactamente, el nivel de fragmentación del índice (modos LIMITADO/NULO). Por lo tanto, se recomienda configurar los modos SAMPLED / DETALLE.

    Sabemos dónde obtener una lista de índices fragmentados. Ahora necesitamos crear un operador ALTER INDEX apropiado para cada uno de ellos. Tradicionalmente, el cursor se usa para esto:

    DECLARE @SQL NVARCHAR(MAX)
    
    DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
    	SELECT '
    	ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
    		CASE WHEN s.avg_fragmentation_in_percent > 30
    			THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON'
    				-- Enterprise, Developer
    				+ CASE WHEN SERVERPROPERTY('EditionID') IN (1804890536, -2117995310)
    						THEN ', ONLINE = ON'
    						ELSE ''
    				  END + ')'
    			ELSE 'REORGANIZE'
    		END + ';'
    	FROM (
    		SELECT 
    			  s.[object_id]
    			, s.index_id
    			, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
    		FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') s
    		WHERE s.page_count > 128 -- > 1 MB
    			AND s.index_id > 0 -- <> HEAP
    			AND s.avg_fragmentation_in_percent > 5
    		GROUP BY s.[object_id], s.index_id
    	) s
    	JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
    	JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id]
    
    OPEN cur
    
    FETCH NEXT FROM cur INTO @SQL
    
    WHILE @@FETCH_STATUS = 0 BEGIN
    
    	EXEC sys.sp_executesql @SQL
    
    	FETCH NEXT FROM cur INTO @SQL
    
    END 
    
    CLOSE cur 
    DEALLOCATE cur

    Para acelerar el proceso de recuperación del índice, recomendamos especificar adicionalmente SORT_IN_TEMPDB. Además, la opción ONLINE ralentiza la recuperación del índice. Sin embargo, en algunos casos esto puede ser útil. Por ejemplo, la lectura de un índice agrupado puede llevar algún tiempo. Creamos un índice de cobertura y resolvimos el problema de rendimiento. A continuación, restauramos el índice no agrupado y debemos aplicarlo al índice del clúster, lo que afecta el rendimiento.

    El primero le permite recuperar índices en la base de datos tempdb, lo que puede ser especialmente útil para índices grandes en caso de poca memoria; en otros casos, la opción se ignora. Además, si la base de datos tempdb está ubicada en otra unidad, reducirá significativamente el tiempo para crear un índice.
    La segunda opción le permite restaurar el índice sin bloquear las consultas al objeto para el que se creó el índice.

    Como ha demostrado la práctica, la desfragmentación de índices con un bajo nivel de fragmentación o con un número reducido de páginas no aporta grandes mejoras que contribuyan a mejorar el rendimiento a la hora de trabajar con ellos.

    Alternativamente, la consulta anterior se puede reescribir sin usar el cursor:

    DECLARE @IsDetailedScan BIT
    SELECT @IsDetailedScan = 1
    
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = (
    	SELECT '
    	ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
    		CASE WHEN s.avg_fragmentation_in_percent > 30
    			THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON'
    				-- Enterprise, Developer
    				+ CASE WHEN SERVERPROPERTY('EditionID') IN (1804890536, -2117995310)
    						THEN ', ONLINE = ON'
    						ELSE ''
    				  END + ')'
    			ELSE 'REORGANIZE'
    		END + ';
    	'
    	FROM (
    		SELECT 
    			  s.[object_id]
    			, s.index_id
    			, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
    		FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 
    								CASE WHEN @IsDetailedScan = 1 
    									THEN 'DETAILED'
    									ELSE 'LIMITED'
    								END) s
    		WHERE s.page_count > 128 -- > 1 MB
    			AND s.index_id > 0 -- <> HEAP
    			AND s.avg_fragmentation_in_percent > 5
    		GROUP BY s.[object_id], s.index_id
    	) s
    	JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
    	JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id]
    	FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
    	OPTION (OPTIMIZE FOR (@IsDetailedScan = 1))
    
    --PRINT @SQL
    EXEC sys.sp_executesql @SQL

    Como resultado, ambas consultas en tiempo de ejecución generarán consultas para desfragmentar índices de problemas:

    ALTER INDEX [IX_TransactionHistory_ProductID] 
    ON [Production].[TransactionHistory] REORGANIZE;
    
    ALTER INDEX [IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID] 
    	ON [Production].[TransactionHistory] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON);
    
    ALTER INDEX [IX_TransactionHistoryArchive_ProductID] 
    	ON [Production].[TransactionHistoryArchive] REORGANIZE;
    ...

    El tiempo de ejecución de la consulta proporcionado se puede comparar utilizando el perfil de consulta SQL integrado en dbForge Studio para SQL Server:

    Analizador de SQL

    Como puede ver, usar el cursor para ejecutar la consulta lleva más tiempo.

    De hecho, la primera parte de la creación de un plan de mantenimiento de la base de datos está completa.

    En la siguiente parte escribiremos una solicitud de actualización automática de estadísticas.

    Artículos de interés

    Subir