Cómo automatizar la desfragmentación de SQL Server usando políticas

Introducción

Aparte de numerosos factores, un mantenimiento pobre del índice puede ser una razón para el desempeño disminuido de SQL Server. Si una base de datos contiene tablas con numerosas entradas, y eso es actualizado constantemente, es muy probable que ocurra una alta fragmentación del índice. Para índices más pequeños, la alta fragmentación no necesariamente degrada el desempeño de las consultas que son corridas sobre una tabla. Pero para tablas más grandes, con índices que consisten en 1000 páginas o más, la fragmentación podría causar problemas notorios de desempeño. Afortunadamente, realizar tareas de mantenimiento de índices regularmente puede eliminar el riesgo de degradar el desempeño significativamente. Las formas más efectivas de tratar la fragmentación del índice son las operaciones de reorganizar y reconstruir el índice.

Para bases de datos más pequeñas, las tareas de mantenimiento del índice pueden ser ejecutadas manualmente, cuando la necesidad emerge. Instrucciones detalladas para correr trabajos de reorganización y reconstrucción manualmente pueden ser encontradas en este artículo: Por qué, cuándo y cómo reconstruir y reorganizar índices de SQL Server. Pero las bases de datos más grandes y de alto tráfico requieren mantenimiento de índices de forma regular: semanalmente o, incluso, diariamente. Para estos casos de uso, es prudente configurar una política que correrá automáticamente según el programa establecido por el DBA.

Este artículo muestra tres soluciones para crear una política completamente funcional de desfragmentación de SQL Server:

  1. Configurando la política como un trabajo de SQL Server Agent – esto requiere ciertas habilidades con las consultas y algún conocimiento básico de T-SQL. Aunque un poco compleja, esta opción es la más personalizable. El servicio de SQL Server Agent necesita ser instalado y debe estar corriendo.

  2. Usando un plan de mantenimiento en SQL Server Management Studio – esta opción es un poco más amigable para el usuario, pero no permite al usuario desfragmentar un índice específico. La desfragmentación es permitida sólo al nivel de tablas. Esta opción también requiere que SQL Server Agent esté instalado en el servidor.

  3. Usando políticas de ApexSQL Defrag – ApexSQL Defrag es una solución de terceros simple pero poderosa para la administración de fragmentación de índices en SQL Server. Todas las operaciones están definidas y desplegadas en la interfaz gráfica de usuario y no requieren el uso de T-SQL o SQL Server Agent.

Detectando la fragmentación del índice en SQL Server

Antes de aplicar cualquiera de las soluciones listadas, es necesario correr un análisis de índice primero para detectar índices altamente fragmentados. Correr un trabajo de reorganización o reconstrucción en cada índice de base de datos, esquema o tabla puede sonar como la solución más fácil, pero no es recomendado, dado que puede consumir mucho tiempo y recursos. La mejor manera de verificar la fragmentación del índice en SQL Server es usar la función integrada sys.dm_db_index_physical_stats. Esta función retorna la información de tamaño y fragmentación para todos los índices en un servidor, todos los índices en una base de datos, todos los índices en una tabla, o sólo un índice, dependiendo de los parámetros provistos. Consultar la función en crudo con parámetros básicos debería recolectar toda la información necesaria para el análisis, pero los resultados estarán muy desordenados. Por lo tanto, usamos la consulta modificada: 

--Script 1: Detecting index fragmentation

SELECT dbschemas.[name] AS 'Schema',
dbtables.[name] AS 'Table',
dbindexes.[name] AS 'Index',
indexstats.avg_fragmentation_in_percent AS 'Frag (%)',
indexstats.page_count AS 'Page count'

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent DESC

Correr la consulta en la base de datos AdventureWorks2014 recolecta los siguientes resultados:

El esquema, la tabla, el nombre, el porcentaje de fragmentación y el conteo de páginas son mostrados para cada índice en la base de datos. Los resultados son ordenados descendentemente por porcentaje de fragmentación, de modo que podamos aislar fácilmente índices con la fragmentación más alta.

Dependiendo del valor del porcentaje de fragmentación, la fragmentación puede ser reconocida como:

  1. Alta fragmentación – los valores del porcentaje de fragmentación están encima de 30%. Los índices altamente fragmentados usualmente requieren la operación de reconstrucción.

  2. Fragmentación media – para valores entre 10% y 30%. El mejor método para corregir la fragmentación media es usar la operación de reorganización.

  3. Baja fragmentación – para los valores debajo de 10%. Los índices con baja fragmentación no requieren desfragmentación, dado que tienen mínimo impacto en el desempeño del servidor.

Tan pronto como el análisis está completo, podemos escribir el script de desfragmentación.

Escribiendo un script de desfragmentación

Para escribir un script personalizado, dedica qué índices desea incluir, dependiendo de los resultados de la consulta previa. Generalmente, los índices con niveles de fragmentación entre 10% y 30% necesitan ser reorganizados, mientras que aquellos con alta fragmentación necesitan ser reconstruidos. Otro factor importante cuando se elije qué índices incluir en el script, es el tamaño del índice. Los índices pequeños pueden ser incluidos en el scripts, pero desfragmentarlos no afecta el desempeño del servidor significativamente. En general, los índices más pequeños que 1000 páginas son raramente incluidos en los trabajos de desfragmentación.

Para reorganizar todos los índices en una tabla o sólo un índice específico, use las siguientes sentencias: 

--Script 2: Reorganize script

--2.1 Reorganize single index

ALTER INDEX Index_name ON Table_name 
REORGANIZE ;   

--2.2 Reorganize all indexes in the table

ALTER INDEX ALL ON Table_name
REORGANIZE ;

El script para reconstruir tiene la misma sintaxis, pero usa la sentencia REBUILD: 

--Script 3: Rebuild script

--2.1 Rebuild single index

ALTER INDEX Index_name ON Table_name 
REBUILD ;   

--2.2 Rebuild all indexes in the table

ALTER INDEX ALL ON Table_name
REBUILD ;

Escribir sentencias para reorganizar y reconstruir para cada índice, especialmente para bases de datos grandes, puede ser un trabajo pesado. Por lo tanto, decidimos usar un script dinámico que automáticamente revisa la fragmentación del índice y el tamaño de página del índice, y aplica las operaciones de reorganizar o reconstruir dependiendo de los resultados.

Para usar el script efectivamente, es necesario establecer unas pocas variables de umbral primeramente:

  1. @reorg_frag_thresh – Umbral para reorganizar un índice en %. El valor sugerido para la variable es 10 (todos los índices con fragmentación entre 10% y 30% serán reorganizados).

  2. @rebuild_frag_thresh – Umbral para reconstruir un índice en %. El valor sugerido para la variable es 30 (todos los índices con fragmentación de 30% y superior serán reconstruidos).

  3. @fill_factor – Especifica el valor del factor de llenado para las operaciones de reconstrucción. El valor por defecto es 0 (factor de llenado al 100%). Es altamente recomendado no cambiar este valor, dado que puede resultar en el crecimiento de índices de reconstrucción si el valor está entre 1 y 99.

  4. @page_count_thresh – Umbral de conteo de páginas. Todos los índices igual o más grandes que el número establecido de páginas serán procesados.

-- Script 4: Automatically analyze and defragment indexes

-- Set variables
-- *********************************************************************************************
SET NOCOUNT ON

DECLARE @reorg_frag_thresh   float	SET @reorg_frag_thresh	= 10.0
DECLARE @rebuild_frag_thresh float	SET @rebuild_frag_thresh = 30.0
DECLARE @fill_factor         tinyint	SET @fill_factor = 0
DECLARE @report_only         bit	SET @report_only = 0
DECLARE @page_count_thresh   smallint	SET @page_count_thresh = 1
-- *********************************************************************************************
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 @page_count     int
DECLARE @command        nvarchar(4000)
DECLARE @intentions     nvarchar(4000)
DECLARE @table_var      TABLE(
                          objectid     int,
                          indexid      int,
                          partitionnum int,
                          frag         float,
		    page_count   int
                        )
INSERT INTO
    @table_var
SELECT
    [object_id]                    AS objectid,
    [index_id]                     AS indexid,
    [partition_number]             AS partitionnum,
    [avg_fragmentation_in_percent] AS frag,
    [page_count]		  AS page_count
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE
    [avg_fragmentation_in_percent] > @reorg_frag_thresh 
	AND
	page_count > @page_count_thresh
	AND
    index_id > 0	
 DECLARE partitions CURSOR FOR
    SELECT * FROM @table_var 
OPEN partitions 
WHILE (1=1) BEGIN
    FETCH NEXT
        FROM partitions
        INTO @objectid, @indexid, @partitionnum, @frag, @page_count 
    IF @@FETCH_STATUS < 0 BREAK
    SELECT
        @objectname = QUOTENAME(o.[name]),
        @schemaname = QUOTENAME(s.[name])
    FROM
        sys.objects AS o WITH (NOLOCK)
        JOIN sys.schemas AS s WITH (NOLOCK)
        ON s.[schema_id] = o.[schema_id]
    WHERE
        o.[object_id] = @objectid 
    SELECT
        @indexname = QUOTENAME([name])
    FROM
        sys.indexes WITH (NOLOCK)
    WHERE
        [object_id] = @objectid AND
        [index_id] = @indexid 
    SELECT
        @partitioncount = count (*)
    FROM
        sys.partitions WITH (NOLOCK)
    WHERE
        [object_id] = @objectid AND
        [index_id] = @indexid		
    SET @intentions =
        @schemaname + N'.' +
        @objectname + N'.' +
        @indexname + N':' + CHAR(13) + CHAR(10)
    SET @intentions =
        REPLACE(SPACE(LEN(@intentions)), ' ', '=') + CHAR(13) + CHAR(10) +
        @intentions
    SET @intentions = @intentions +
        N' FRAGMENTATION: ' + CAST(@frag AS nvarchar) + N'%' + CHAR(13) + CHAR(10) +
        N' PAGE COUNT: '    + CAST(@page_count AS nvarchar) + CHAR(13) + CHAR(10) 
    IF @frag < @rebuild_frag_thresh BEGIN
        SET @intentions = @intentions +
            N' OPERATION: REORGANIZE' + CHAR(13) + CHAR(10)
        SET @command =
            N'ALTER INDEX ' + @indexname +
            N' ON ' + @schemaname + N'.' + @objectname +
            N' REORGANIZE; ' + 
            N' UPDATE STATISTICS ' + @schemaname + N'.' + @objectname + 
            N' ' + @indexname + ';'
    END
    IF @frag >= @rebuild_frag_thresh BEGIN
        SET @intentions = @intentions +
            N' OPERATION: REBUILD' + CHAR(13) + CHAR(10)
        SET @command =
            N'ALTER INDEX ' + @indexname +
            N' ON ' + @schemaname + N'.' +     @objectname +
            N' REBUILD'
    END
    IF @partitioncount > 1 BEGIN
        SET @intentions = @intentions +
            N' PARTITION: ' + CAST(@partitionnum AS nvarchar(10)) + CHAR(13) + CHAR(10)
        SET @command = @command +
            N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))
    END
    IF @frag >= @rebuild_frag_thresh AND @fill_factor > 0 AND @fill_factor < 100 BEGIN
        SET @intentions = @intentions +
            N' FILL FACTOR: ' + CAST(@fill_factor AS nvarchar) + CHAR(13) + CHAR(10)
        SET @command = @command +
            N' WITH (FILLFACTOR = ' + CAST(@fill_factor AS nvarchar) + ')'
    END
    IF @report_only = 0 BEGIN
        SET @intentions = @intentions + N' EXECUTING: ' + @command
        PRINT @intentions	    
        EXEC (@command)
    END ELSE BEGIN
        PRINT @intentions
    END
        PRINT @command
END
CLOSE partitions
DEALLOCATE partitions
GO

Establecer políticas de desfragmentación con un trabajo de SQL Server Agent

Antes de comenzar con la configuración del trabajo, asegúrese de que SQL Server Agent esté instalado y corriendo. Para hacer esto, abra SQL Server Management Studio y encuentre SQL Server Agent al fondo de Object Explorer. Haga clic derecho en el agente y clic en el botón Start en el menú contextual. En el caso de que el agente ya esté corriendo, salte este paso.

Para crear un trabajo de SQL Server Agent que desfragmente índices especificados automáticamente, realice los siguientes pasos:

  1. Expanda SQL Server Agent en Object explorer, haga clic derecho en Jobs y seleccione New Job…

  2. En la pestaña General especifique el nombre y la descripción para el trabajo. Asegúrese de seleccionar la casilla Enabled . Proceda a la pestaña Steps .

  3. Hacer clic en el botón New… en la pestaña Steps abre el formulario para la configuración del primer paso del trabajo. Provea cualquier nombre para el paso y deje todos los otros valores por defecto. En la ventana comando, pegue el script creado en el capítulo previo y haga clic en OK.

  4. En la pestaña Schedules , en la ventana New job haga clic en el botón New para crear el programa para el trabajo. Establezca el tipo de programa, la frecuencia y la frecuencia diaria. Ya que las sentencias REBUILD bloquean las tablas durante el proceso de desfragmentación, es mejor establecer el programa a un momento cuando los servidores experimente la menor cantidad de tráfico. En este ejemplo, el programa está establecido para correr cada día a las 2:00 AM. Haga clic en OK para grabar el programa.

  5. Si es necesario, establezca las Alerts y Notifications en las pestañas respectivas. En la pestañaTargets , especifique los objetivos para el trabajo. Para poder apuntar a múltiples servidores, es necesario crear ya sea un Servidor de administración central y un Grupo de Servidores Central Management Server and Server groups o un ambiente Multiservidor Multiserver environment. Haga clic en OK para finalizar la configuración del trabajo.

  6. Para correr el trabajo seleccionado inmediatamente, expanda SQL Server Agent y Jobs en Object Explorer, haga clic derecho en el trabajo creado y seleccione Start Job at Step. Dado que su trabajo sólo tiene un paso, este empezará a ejecutarse automáticamente.

Establezca una política de desfragmentación con planes de mantenimiento

Para crear y correr políticas de desfragmentación con planes de mantenimiento, es necesario realizar los siguientes pasos en SQL Server Management Studio:

  1. Expanda el nodo Management en Object Explorer, haga clic derecho en Maintenance Plans, y seleccione New Maintenance Plan… en el menú contextual. Especifique el nombre para el plan de mantenimiento y haga clic en OK.

  2. Haga doble clic en Subplan_1.

  3. En Subplan Properties, establezca el nombre y la descripción para el subplan y haga clic en el ícono de calendario.

  4. Establezca el programa para el subplan. Cuando termine, haga clic en OK en las ventanas New Job Schedule y Subplan Properties.

  5. Arrastre Rebuild Index Task y Reorganize Index Task desde Toolbox para limpiar el área en Defragmentation plan design. Si no hay una ventana Toolbox visible en la pantalla, enciéndala en la pestaña View de SSMS, o simplemente presione CTRL + ALT + X para activarla. Asegúrese de conectarse a la flecha verde desde Reorganize Index task con la ventana Rebuild Index Task.

  6. Haga doble clic en Reorganize Index Task.

  7. En la ventana abierta, seleccione la base de datos para la tarea desde el menú desplegableDatabase(s)

  8. Desde el menú desplegable Object seleccione la opción Table. Si múltiples bases de datos fueron seleccionadas en el paso previo, esta opción estará deshabilitada y el trabajo reorganizará todos los índices en las bases de datos seleccionadas. Todas las tablas para la base de datos especificada están listados en el menú desplegable Selection . Vea los resultados del análisis de fragmentación de índice y seleccione las tablas que contienen índices con niveles de fragmentación que varían desde 10% a 30%. Haga clic en OK para grabar los cambios para Reorganize Index Task.

  9. Haga doble clic en Rebuild Index Task. Como en los pasos 7 y 8, seleccione la base de datos y las tablas para añadir a la tarea. No hay necesidad de cambiar ningún ajuste en Free space options y Advanced options.

  10. Haga clic derecho en la cabecera de Defragmentation plan design y elija Save Selected Items

  11. El plan de mantenimiento está listo ahora, y correrá de acuerdo al programa establecido. Pero, si es necesario, puede correr inmediatamente. Para correr el plan creado inmediatamente, expanda los nodos Management y Maintenance Plans en Object Explorer. Haga clic derecho en el plan de mantenimiento creado y seleccione Execute

  12. Tan pronto como el plan es ejecutado, un mensaje de éxito es mostrado.

Establecer políticas de desfragmentación en SQL Server con ApexSQL Defrag

Establecer políticas de desfragmentación con ApexSQL Defrag es fácil y sencillo. No hay necesidad de correr un análisis separado de fragmentación de índice, de usar consultas complejas o de memorizar qué índices necesitan ser desfragmentados. Las políticas de ApexSQL Defrag analizan todos los objetos seleccionados primero (bases de datos, tablas o índices individuales). Dependiendo de los resultados y umbrales establecidos, ApexSQL Defrag corre las operaciones de reorganizar o reconstruir automáticamente en los objetos seleccionados. Además, todas las políticas creadas pueden ser habilitadas o deshabilitadas en cualquier momento.

Para crear una política en ApexSQL Defrag, realice los siguientes pasos:

  1. Para poder administrar un servidor con ApexSQL Defrag, añádalo a su lista de servidores. Abra ApexSQL Defrag, vaya a la pestaña Home y haga clic en el botón Add en el grupo Servers group. En la ventana Connection seleccione un servidor desde el menú desplegable. Especifique el tipo de autenticación, el nombre de usuario y la contraseña para el servidor seleccionado. El servidor aparecerá en la lista de servidores si se proveyeron credenciales válidas.

  2. Para crear una nueva política, haga clic en el botón Policies encima de la barra de tareas, y seleccione Create

  3. Establezca el nombre, la descripción y el programa para la nueva política.

  4. En la caja Targets haga clic en el botón navegar (…). Seleccione los objetos que necesitan ser incluidos en la política. No hay necesidad de preocuparse acerca de la fragmentación actual del objeto seleccionado, ya que todos los objetos seleccionados son analizados automáticamente en cada corrida de la nueva política. Haga clic en OK para confirmar la selección.

  5. Haga clic en la pestaña Thresholds . Los umbrales de la fragmentación del índice para las operaciones de reorganizar y reconstruir pueden ser establecidos manualmente en los dos deslizadores. El valor por defecto para reorganizar está entre 10% y 30%. Todos los índices que tienen un valor de fragmentación arriba de 30% serán reconstruidos por defecto.

  6. El resultado final del análisis de fragmentación del índice depende del modo de escaneo seleccionado. Escoja entre los modos de escaneo limitado, de muestra y detallado.

    • Modo limitado – este es el modo más rápido que escanea el menor número de páginas. En el modo limitado, sólo las páginas encima del nivel más bajo del índice son escaneadas.

    • Modo de muestra – Sólo 1% de todas la páginas en un índice o una pila son escaneadas.

    • Modo detallado – Todas las páginas son escaneadas e incluidas en las estadísticas. Es el modo más lento y toma una gran cantidad de recursos cuando se compara con otros modos, pero retorna las estadísticas más precisas.

  7. En Resources threshold, establezca los umbrales de la carga de la CPU o el uso de la memoria. La política parará automáticamente si cualquiera de los valores umbrales son alcanzados.

  8. Si es necesario, establezca la notificación de correo electrónico en la pestaña Notification . Elija recibir notificaciones acerca de fallos de trabajos, éxitos de trabajos, o ambos. Para añadir receptores a la lista, haga clic en el botón Add y escriba la dirección de correo electrónico. Cuando termine, haga clic en OK para grabar los cambios.

La política está ahora disponible en la lista de políticas. Correrá automáticamente, basada en el programa establecido en el paso 3. Hay muchas operaciones en las políticas creadas que están disponibles en ApexSQL Defrag:

  1. Si la política necesita ser corrida ya mismo, haga clic derecho en la política en la lista y seleccione Run from the context menu. The policy will execute immediately. The result of the last run is available as soon as policy completes.

  2. La política seleccionada puede ser también habilitada o deshabilitada desde el menú contextual. Una política habilitada corre dependiendo de un programa establecido. Una política deshabilitada no se ejecutará hasta que sea habilitada de nuevo.

  3. Si cualquier parámetro necesita ser cambiado para una política, es mejor usar el comando Edit desde el menú contextual.

  4. Finalmente, si la política ya no es necesaria, puede ser eliminada ya sea usando el comando desde el menú contextual o seleccionándola y haciendo clic en el botón Delete en la cinta.

Related links:

 

No related posts.

diciembre 8, 2017