Buscar este blog

lunes, 6 de agosto de 2012

Mantenimiento de índices en SQL Server: cómo evitar la fragmentación

Los índices son objetos de la base de datos diseñados de manera similar a los índices que usamos en los libros para encontrar contenidos en el mismo.

Éstos ordenan los datos en nuestras tablas, permitiendo un acceso más rápido y eficiente a aquéllos que estemos consultando, modificando o eliminando.

Por ello, la actualización de los registro de las tablas de la base datos, obliga al servidor SQL a realizar ciertas operaciones que nos garanticen el orden de los datos en los índices. Debido a esta actualización, la información almacenada en los índices se ve fragmentada con su uso. Esta fragmentación depende de parámetros como el fill factor, número de páginas, tamaño del índice y frecuencia de actualización.

En definitiva, un índice sobre un tabla, debido a actualizaciones sobre ésta, puede perder eficacia y, con ello,  nuestras consultas volverse más lentas y su rendimiento deteriorarse.

Es necesario, pues, realizar periódicamente tareas de consulta del estado de nuestros índices, para mantener la fragmentación por debajo de unos límites recomendables. Ésta se mide en porcentaje, correspondiendo un valor bajo (entre 0% y 10%) a un estado óptimo del índice y siendo peor el estado cuando el valor de dicho indicador mayor sea.

Defragmentación, al rescate

Para evitar el deterioro del rendimiento en nuestro servidor, deberemos mantener nuestros índices en un estado de fragmentación óptimo. Lo podremos lograr sencillamente siguiendo estos pasos.

  • Primer paso: detectar fragmentación en los índices de tu base de datos. Para ello, nos basaremos en la vista de sistema sys.dm_db_index_physical_stats, que encapsularemos en la siguiente query:

  • Segundo paso: ejecutar un script para defragmentar los índices con problemas. El script determina si hay que hacer un Reorganize o un Rebuild para cada índice:

-- Ensure a USE  statement has been executed first.
SET NOCOUNT ON;
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 @command nvarchar(4000); 
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count > 1000;

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table.
DROP TABLE #work_to_do;
GO

Diferencias entre reconstruir (REBUILD) y reorganizar (REORGANIZE) un índice

Básicamente, existen dos maneras de volver a ordenar un índice. La más eficaz consiste en eliminar el índice y volver a crearlo, desde cero. Ésta garantiza un resultado óptimo, dejando la fragmentación en 0%. Como contrapartida, es una operación más costosa y genera bloqueos sobre la tabla cuyo índice se está actualizando. Usaremos esta opción (REBUILD) cuando la fragmentación del índice supere el 30%.

Para índices ligeramente fragmentados (entre el 10% y el 30%) existe la opción de reorganizar (REORGANIZE) el índice. Esta opción simplemente reordena los datos del índice dentro de las páginas que ocupa, devolviendo un orden adecuado al mismo. Se trata de una operación mucho más ligera, que no bloquea las tablas y vistas subyacentes.

Adicionalmente, habrá que tener en cuenta que solamente tiene sentido efectuar la operación de defragmentación sobre índices con un tamaño superior a 1000 páginas. Ya que, por debajo de este valor, SQL considera que es más eficiente escribir los datos de forma desordenada que mantener un índice poco efectivo, al tratarse de una tabla pequeña.

Referencias:

20 comentarios:

  1. Me ha venido como anillo al dedo esta entrada. Muy útil, muchas gracias por la excelente informacion que aportas en este blog

    ResponderEliminar
  2. Hola,
    Estoy haciendo pruebas en SQL server, con la base de datos Northwind, que puedes descargar en la página oficial de Microsoft.
    el caso es que a la hora de analizar una tabla que contiene lineas de pedido, 2160 concretamente, obtengo una fragmentación del 70% en los indices, este campo contiene valores repetidos. el caso es que he hecho una REBUILD, y un REORGANIZE, y al volver a analizar otra vez los indices, me continua indicando que la fragmentación es del 70%, ¿Por que puede ser esto?, No está realizando dichas acciones?, he notado que el REBUILD y el REORGANIZE, no tardan nada, puede ser este el problema?, gracias de anemano, y muy buen post

    ResponderEliminar
    Respuestas
    1. Hola, Pedro:
      Me alegra que te haya gustado el post.
      Como puedes ver en el último párrafo, SQL considera que no hay beneficio en organizar un índice para tablas muy pequeñas.
      No sé exactamente ahora mismo cuántas páginas de datos ocupan esos 2160 pedidos, pero seguramente no serán más de 1000 y por eso no ocurre nada.
      En cualquier caso, no debes preocuparte por la fragmentación con "solo" unos pocos miles de registro.

      Eliminar
  3. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  4. Buenas tardes, tengo un par de preguntas.
    1. Por que sucede la defragmentacion de índices?
    2. Por que ejecutas la defragmentacion? no es algo que se debe evitar?
    3. he ejecutado la reconstrucción de un indice que tenía un 66% y luego de volver a consultarlo seguía igual (lo hice en el management studio y en transact)

    ResponderEliminar
    Respuestas
    1. Hola, Mauricio:

      Son preguntas muy interesantes las que haces. En primer lugar, comentar que existen dos tipos de fragmentación de los índices: la interna y la externa.

      Como sabes, los datos se organizan en páginas de 8 KB en disco. SQL escribe los datos de manera contigua en estas páginas, pero si borramos datos dejamos huecos dentro de esas páginas. Fragmentación interna: las páginas tienen espacio en blanco que consume memoria igualmente, ya que SQL carga los datos por páginas en memoria.

      Por otro lado, si insertamos datos en medio de los que ya existen y se necesita crear páginas nuevas, éstas se crearán al final del fichero, haciendo que el disco duro tenga que desplazar sus cabezales para leer datos contiguos. Fragmentación externa.

      La desfragmentación sirve para deshacer esta situación y volver a organizar tanto los datos dentro de las páginas, como las páginas en disco.

      Hay situaciones en las que la fragmentación importa menos, pero ya estamos hablando de cosas subjetivas, e incluso de distintas formas de pensar de los expertos. Por ejemplo, si tuvieras más GB memoria de los que ocupa tu base de datos en disco, tendrías todas las páginas en RAM y la fragmentación en disco no importaría. También depende del tipo de disco: a los SSD les afecta bastante menos la fragmentación.

      Pero, en general, conviene tener un buen sistema de mantenimiento de índices.

      Respecto a la tercera pregunta, como decimos en el último párrafo, es posible que la tabla sea tan pequeña que SQL haya decidido que no obtiene ningún beneficio reorganizando los índices.

      Espero haber resuelto tus dudas.

      Saludos.

      Eliminar
  5. Buenos días Jaime, te agradezco por el tiempo que te permitiste en contestar, si me aclaró mucho, ya que en otros lados no se entendía directamente de que era al borrar registros de las tablas que quedaban espacios en las páginas, yo se que es algo ambiguo lo que voy a preguntar y mas por el hecho de como dijiste "distintas formas de pensar de los expertos" pero tendrás algunos tips, para una optima o algo que ayude significativamente al rendimiento de una BD?

    ResponderEliminar
    Respuestas
    1. Hola, Mauricio:

      La pregunta que haces no tiene otra respuesta que "la experiencia". Desde luego, no puedo decirte todas las buenas prácticas que he ido aprendiendo en un comentario de un blog :)

      En general, te recomiendo leer y practicar mucho. Con el tiempo se van aprendiendo las cosas. También acabarás conociendo ciertos blogs y libros de ciertos autores que te enseñen más que otros y seguirás más sus consejos que los de otros.
      Al final se trata de ir encontrando soluciones a los problemas que se te plantean día a día en tu trabajo o estudios.

      Eliminar
  6. Gracias Jaime, es precisamente lo que pensaba, es por ello que incluso ya comencé a leer un libro al respecto, tal vez por este medio, aunque ya me salí totalmente del tema original, quisiera saber si te has topado con inter-bloqueos de tablas y si es así (aunque me imagino que si) que has hecho para poder solucionar esto.

    Gracias.

    ResponderEliminar
  7. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  8. Gracias por el aporte, tengo una solo una duda, ¿Esto es valido para versiones 2008 en adelante?

    ResponderEliminar
    Respuestas
    1. Hola, Miguel:

      Sigue siendo válido hasta 2014, al menos. Con 2016 todavía no lo he probado, pero no hay ningún cambio documentado que aparentemente lo vaya a romper.

      Eso sí, si usas discos SSD o suficiente memoria RAM para toda tu base de datos, la fragmentación deja de ser un problema tan serio.

      Jaime

      Eliminar
  9. Excelente aporte yforma de explicar, con palabras sencillas, pero sobre todo muy útil. Gracias por tomarte el tiempo de compartir. Ya probé los 2 scripts en un servidor de pruebas y funcionan muy bien. Sólo una duda, en otro foro, leí algo parecido para defragmentar índices, lo hacen con el comando: DBCC DBREINDEX(@TableName,' ',90) donde comentan sobre la importancia de dejar un factor de llenado +/- de 90% para tablas que se modifican constantemente registros. Mi pregunta sería, ¿Cuál es la diferencia de ALTER INDEX vs DBREINDEX? y ¿Qué necesito modificar en tu script 'con alter index' para agregar el factor de llenado? Gracias.

    ResponderEliminar
    Respuestas
    1. Muchas gracias a ti, César, por tu amable comentario.
      DBCC DBREINDEX está marcado por Microsoft para su futura desaparición, como puedes comprobar en la MSDN: https://msdn.microsoft.com/es-es/library/ms181671.aspx.

      Desde ese mismo enlace puedes ir a ALTER INDEX para comprobar su sintaxis. Verás que una de las opciones que admite es fillfactor.

      Saludos.

      Eliminar
    2. Este comentario ha sido eliminado por el autor.

      Eliminar
    3. Gracias Jaime por tu pronta respuesta. Por cierto, respondiendo a Miguell, Probé los scripts con Sql Server 2016 y funcionó a la primera.

      Eliminar
  10. HOLA SOLO ME APLICA LOS CAMBIOS EN UNA PARTICIÓN DE MIS TABLAS DE DATOS

    ResponderEliminar
  11. Hola, buenisima la rutina, pero al ejecutarla me sale

    [Err] 42000 - [SQL Server]Debe declarar la variable escalar "@objectid".
    42000 - [SQL Server]Debe declarar la variable escalar "@objectname".
    42000 - [SQL Server]Debe declarar la variable escalar "@indexname".
    42000 - [SQL Server]Debe declarar la variable escalar "@partitioncount".
    42000 - [SQL Server]Debe declarar la variable escalar "@frag".
    42000 - [SQL Server]Debe declarar la variable escalar "@indexname".
    42000 - [SQL Server]Debe declarar la variable escalar "@frag".
    42000 - [SQL Server]Debe declarar la variable escalar "@indexname".
    42000 - [SQL Server]Debe declarar la variable escalar "@partitioncount".
    42000 - [SQL Server]Debe declarar la variable escalar "@command".
    42000 - [SQL Server]Debe declarar la variable escalar "@command".
    42000 - [SQL Server]Debe declarar la variable escalar "@command".


    que puedo estar haciendo mal?
    gracias, saludos!

    ResponderEliminar
    Respuestas
    1. Hola Roberto:

      ¿Has incluido la parte donde se declaran todas esas variables?

      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 @command nvarchar(4000);

      Saludos.

      Eliminar