@
leyendo...
Tecnología

Revisando la fragmentación de índices en SQL Server

Últimamente, he estado revisando e incluso haciendo algunas consultorías para empresas sobre el tema de desempeño y, claro, siempre el tema de índices sale a relucir.

Con el tiempo y actividades tipo INS, UPD y DEL en las tablas de una base de datos, los índices asociados a dichas tablas, se van fragmentando. En esencia, la fragmentación se da por que se tiene mucho espacio libre en las páginas del índice, lo cual impacta al desempeño porque hay que insertar en el cache de datos un porcentaje superior de páginas, dada la cantidad de páginas con espacio libre.

Adicionalmente, se presenta fragmentación cuando los índices tienen páginas en las cuales el orden lógico, de acuerdo con el valor de la llave, no concuerda con el orden físico dentro del archivo de datos.

Por ello, es importante revisar qué tan fragmentados están los índices.

Por el SQL Server Management Studio puede hacerse, yendo a la tabla sobre la cual se quiere ver que tan fragmentada está uno de los índices que tiene dicha tabla. Por ejemplo, para la base de datos [WideWorldImporters] en SQL Server 2016 SP2, tabla [Sales.OrderLines], índice [PK_Sales_OrderLines] se vería de la siguiente manera:

Es decir, muy poca fragmentación.

Para una base de datos con pocas tablas y pocos índices por tabla, de pronto el anterior proceso funciona. Pero no escala para bases de datos con muchas tablas y más de cinco o seis índices por tabla. ¿Qué hacer en ese caso?

El siguiente script les permitirá revisar, para una base de datos en particular, los índices fragmentados y, de acuerdo con los umbrales propuestos por Microsoft para reorganizar o reconstruir los índices, también validar que tipo de comando T-SQL usar para desfragmentar los índices:

declare @strBD nvarchar(50);
set @strBD = N'';
select [Table Name] = OBJECT_NAME(STATSIDX.object_id, database_id)
    ,[Index ID] = STATSIDX.index_id
    ,[Index Name] = SYSIDX.name
    ,Index_Type = index_type_desc
    ,Logic_Frag = ROUND(avg_fragmentation_in_percent,2)
    ,Action = case
        when (avg_fragmentation_in_percent < 30.0 and avg_fragmentation_in_percent >= 5.0)
            then 'ALTER INDEX REORGANIZE'
        when avg_fragmentation_in_percent >= 30.0
            then 'ALTER INDEX REBUILD'
        else 'NOTHING'
     end
    ,avg_page_space_used_in_percent
    ,Date_Collected=CAST(GETDATE() AS DATE)
    ,Time_Collected=CAST(GETDATE() AS TIME)
    ,Partition_Number = partition_number
    ,page_count
--  ,record_count
--from sys.dm_db_index_physical_stats(DB_ID(@strBD), null, null, null, 'SAMPLED') AS STATSIDX
from sys.dm_db_index_physical_stats(DB_ID(@strBD), null, null, null, 'LIMITED') AS STATSIDX
left outer join sys.indexes AS SYSIDX on STATSIDX.index_id = SYSIDX.index_id AND STATSIDX.object_id = SYSIDX.object_id
where alloc_unit_type_desc = 'IN_ROW_DATA' -- AND page_count >= 1000
order by avg_fragmentation_in_percent DESC;

Recuerden en la segunda línea poner el nombre de la base de datos en la variable @strBD.

El script les da fecha y hora de recolección de datos para que después de desfragmentar, puedan hacer una comparación a ver cómo les fue.

Fíjense que hay algunas secciones del código comentadas. La primera de ellas, en combinación con la segunda, les permite saber cuantos registros tiene asociada la estructura del índice en disco. Recuerden comentar la línea del “from” para evitar error. La tercera sección, permite saber cuántas páginas tiene el índice; esto porque en ciertas ocasiones, para bases de datos con sistemas muy fragmentados, yo voy primero por aquellas estructuras con mucha fragmentación y con un número alto de páginas. Yo generalmente uso el valor 1.000, pero pues ustedes pueden modificarlo de acuerdo con sus necesidades.

Conociendo esto, ya lo que queda es desfragmentar el o los índices deseados. De nuevo, lo pueden hacer por SQL Server Management Studio, dando clic derecho sobre el índice y seleccionando “Rebuild” o “Reorganize” de acuerdo con el resultado anterior, teniendo presente que deben ir uno por uno y recordar o tener claro en donde deben reconstruir y en donde deben reorganizar; es decir, no escala, si hay muchas tablas y muchos índices por dichas tablas.

O pueden usar, en mi percepción, la mejor solución que existe para esto, la de Ola Hallengren titulada SQL Server Index and Statistics Maintenance. Lean bien la página Web y los ejemplos, porque es una solución parametrizada para cubrir muchos escenarios asociados con situaciones de mantenimiento de índices e incluso de estadísticas, además, con el query anterior pueden hacerlo para algunos índices o simplemente olvidarse de ese query y simplemente dejarle todo el trabajo a la solución de Ola. Es muy eficiente. He tenido escenarios de bases de datos con muchos índices fragmentados, unos 400 mal contados y reconstruirlos ha tardado un poco menos de tres horas. No prometo que para ustedes el escenario sea similar o incluso idéntico en términos de resultados, pero me parece que una ventana de mantenimiento para esto en ambientes productivos es un tiempo bien gastado.

Lo anterior, es decir, reconstruir o reorganizar uno o más índices fragmentados, lo pueden hacer también por el plan de mantenimiento. Pero les contaré como hacerlo en otro post, que este ya está quedando muy largo. Sin embargo, no creo que quieran hacerlo por plan de mantenimiento después de hacerlo con la solución de Ola, que seguro les encantará.

Saludos.

Anuncios

Comentarios

Los comentarios están cerrados.

Categorías

Archivos

Anuncios
A %d blogueros les gusta esto: