domingo, 12 de abril de 2009

Manten tus índices de forma eficiente

Desde SQL Server 2005 la sentencia DBCC SHOWCONTIG ha sido reemplazada mediante la función de administración del sistema sys.dm_db_index_physical_stats(…) mediante la cual podemos realizar consultas dinámicas sobre el estado de nuestros índices y montones.

Al margen de que en este artículo no voy a evaluar el uso de DBCC SHOWCONTIG, debes saber que dicha cláusula marcada como DEPRECIADA desde SQL Server 2005 no contempla ya algunas de las características de SQL 2005 o 2008 (datos espaciales, nvarchar(max), varchar(max),…particiones,…) por lo que deberías de dejar de consultarla en pro de la DMF sys.dm_db-index-physical_stats(…)

¿Por qué aparece la fragmentación?

En la mayoría de las aplicaciones, las operaciones de modificación de datos que sufren nuestras tablas no son ni mucho menos repartidas de forma equitativa por la misma. Esto quiere decir que se darán situaciones en las que realicemos varias inserciones sobre las mismas páginas, dejando otras prácticamente sin tocar. Además, las inserciones producirán page-splits con el paso del tiempo, dando al final una situación en la que el relleno de las páginas de nuestros índices ha variado respecto a su estado inicial. Con el tiempo las páginas no serán correlativas porque al buscar espacio en disco donde almacenarlas, este no será asignado de forma secuencial (fragmentación externa). Además, se darán situaciones en las que existan páginas con espacio libre suficiente para albergar datos, que se encuentran repartidos en otras páginas con también espacio libre suficiente, produciendo una situación en la que por ejemplo 2 páginas puedan unirse en una sola (fragmentación interna)

¿Qué problemas conlleva la fragmentación?

A grandes rasgos, si existe fragmentación, lo que ocurre es que datos que deberían estar de forma contigua y compacta no lo estén. Esto implica que en ocasiones se necesiten mayor numero de lecturas E/S para recuperar un dato, ya que si este debería encontrarse en una sola página y se encuentra en dos, ya estamos necesitando una lectura extra que no debería existir. Pero piensa siempre a lo grande y no pienses en una lectura sola, piensa en ese problema reproducido con un mayor porcentaje y te darás cuenta que al final estamos hablando de ciclos de CPU y lecturas de disco que pueden hacer que consultas que duran milisegundos pasen al orden de los segundos.

¿Cómo evitamos la fragmentación?

Existen 3 formas de eliminar la fragmentación:

  • Borrar y recrear el índice fragmentado

Uso de DROP INDEX … y luego CREATE INDEX …

Beneficios: Se puede especificar que el nuevo índice cumpla con la especificación de relleno dada por el FILLFACTOR. La recreación del índice es la mejor puesto que se rellena conforme le hayamos especificado y además todo será correlativo y compacto.

Desventajas: La operación es offline tanto en el borrado como en recreación del mismo (no se podrá consultar). Además la operación es atómica, lo cual quiere decir que si paramos la creación del índice, este último no se crea y por tanto nos quedaremos sin el (hasta volver a recrearlo). Otra desventaja importante es que debemos tener presente que puede que tengamos claves ajenas apuntando al índice, las cuales tendremos que desactivar o borrar previo a realizar esta operación.

  • Reordenar las páginas de los niveles hoja del índice

Uso de ALTER INDEX … REORGANIZE

Beneficios: Reorganiza los datos del nivel hoja del índice en un orden lógico. La ordenación se realiza de forma online por lo que podemos consultar el índice mientras se realiza la operación, y además si la interrumpimos, permanecerá en un estado consistente

Desventajas: Este método no obtiene resultados de desfragmentación tan eficientes como un borrado y recreación de índice clustered

  • Recrear el índice

Uso de ALTER INDEX … REBUILD

La recreación del índice consiste en realizar un borrado y recreación del mismo. La diferencia con la primera forma de eliminar fragmentación que hemos visto, es que esta operación está pensada para realizarse como tarea de mantenimiento al estilo de reorganización. Es decir, que no es necesario tener en cuenta entre otras cosas las claves ajenas que están apuntándole, por ejemplo.

De forma predeterminada, la operación es offline, lo cual quiere decir que durante el tiempo de recreación del índice no se puede consultar, pero es posible especificar un flag en su definición, que posibilite su recreación ONLINE (solo para ediciones Enterprise edition). Además, es por supuesto mas eficiente que la mera reorganización por lo que en según que casos es mas óptimo realizarlo.

De los tres métodos, solo nos interesa a priori el 2º y 3º debido al gran número de desventajas que se presentan en el primero.

¿Cómo detectamos la fragmentación?

De entre todas las columnas devueltas por la DMF sys.dm_db_index_physical_stats, nos centraremos en las columnas siguientes

Columna

Descripción

avg_fragmentation_in_percent

El porcentaje de fragmentación externa, es decir páginas que están fuera de orden y no consecutivas.

fragment_count

Numero de fragmentos en el índice (número de páginas físicamente consecutivas en un nivel hoja)

avg_fragment_size_in_pages

Media de número de páginas en un fragmento en un índice

avg_page_space_used_in_percent

Promedio de espacio de almacenamiento de datos disponible usado en todas las páginas

Gracias a la información que podemos consultar en dichas columnas, podremos realizar filtros en función del nivel de fragmentación:

  • Si hay más de un 30% de fragmentación externa (avg_fragmentation_in_percent), o menos de un 60% de espacio usado medio por página en un índice (avg_page_space_used_in_percent), es recomendable realizar un REBUILD.
  • Si hay entre un 10% y un 15% de fragmentación externa o entre un 60% y un 75% de espacio usado medio por página en un índice es recomendable realizar un REORGANIZE.

Además, no nos va a interesar analizar todos los índices porque quizás tengamos miles de ellos y no todos tengan tantos datos y/o fragmentación como para que nos resulte óptimo desfragmentarlos. Es por ello que de todos los índices solo nos quedaremos con los que satisfagan el siguiente filtro:

WHERE (avg_fragmentation_in_percent > 10

or avg_page_space_used_in_percent < 75)

and page_count > 8

La parte de page_count > 8 la pongo porque me parece interesante que si un índice es tan pequeño que no tiene ni 8 páginas, no merece a priori nuestra atención. Esto obviamente depende mucho del entorno en el que estemos pero en la mayoría de situaciones no ganamos nada desfragmentando un índice tan pequeño y el coste de su desfragmentación no está justificado (pese a que sea tan bajo que parezca insignificante, si hablamos de miles de índices la cosa no es tan simple).

Mediante el script que voy a indicar a continuación, se devolverá entre otras cosas la sentencia recomendada para desfragmentar el índice según las premisas definidas anteriormente.

declare @tempFragmentation as table(bbdd sysname,

[table] varchar(128),

index_name varchar(128),

avg_fragmentation_in_percent decimal (28,2),

avg_page_space_used_in_percent decimal (28,2),

page_count bigint,

record_count bigint,

fragment_count bigint,

internal_fragmentation bit,

external_fragmentation bit,

recommendation nvarchar(1024))

declare @nombreBBDD nvarchar(128)

declare @nombreBBDD_sin_espacios nvarchar(128)

declare @sentenciaSQL nvarchar(max)

DECLARE cursorBBDD CURSOR read_only fast_forward forward_only FOR

SELECT [name] FROM sys.databases

where state_desc = 'ONLINE'

OPEN cursorBBDD

-- Perform the first fetch.

FETCH NEXT FROM cursorBBDD

into @nombreBBDD

WHILE @@FETCH_STATUS = 0

BEGIN

set @nombreBBDD_sin_espacios = @nombreBBDD

set @nombreBBDD = QUOTENAME(@nombreBBDD)

set @sentenciaSQL = N'

with dt as (

SELECT index_id,object_id,database_id,avg_fragmentation_in_percent, avg_page_space_used_in_percent,page_count,record_count,fragment_count

from sys.dm_db_index_physical_stats (DB_ID('''+ @nombreBBDD_sin_espacios +'''), NULL, NULL, NULL, NULL)

where ( avg_fragmentation_in_percent > 10

or avg_page_space_used_in_percent < 75)

and page_count > 8

)

SELECT db_name(database_id) BBDD,ss.name+''.''+st.name [table] ,si.name index_name, dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent,page_count,record_count,fragment_count,

case

when avg_page_space_used_in_percent < 75 then 1

else 0

end Fragmentacion_interna,

case

when avg_fragmentation_in_percent > 10 then 1

else 0

end Fragmentacion_externa,

case

when ( avg_page_space_used_in_percent < 60

or avg_fragmentation_in_percent > 15

) then ''ALTER INDEX '' + si.name+ '' ON ''+ SCHEMA_NAME(st.SCHEMA_ID)+''.''+st.name +'' REBUILD;''

else ''ALTER INDEX '' + si.name+ '' ON ''+ SCHEMA_NAME(st.SCHEMA_ID)+''.''+st.name +'' REORGANIZE;''

end SENTENCIA_RECOMENDADA

from dt

INNER JOIN '+ @nombreBBDD+ '.sys.indexes si ON si.object_id = dt.object_id

and dt.index_id <> 0

AND si.index_id = dt.index_id

INNER JOIN '+ @nombreBBDD+ '.sys.tables st on st.object_id = dt.object_id

INNER JOIN '+ @nombreBBDD+ '.sys.schemas ss on ss.schema_id = st.schema_id

order by BBDD,dt.avg_fragmentation_in_percent desc, dt.avg_page_space_used_in_percent asc,page_count desc,record_count desc,fragment_count desc

'

insert into @tempFragmentation ( bbdd,[table],index_name,avg_fragmentation_in_percent,

avg_page_space_used_in_percent,page_count,

record_count,fragment_count,internal_fragmentation,

external_fragmentation,recommendation)

execute(@sentenciaSQL)

-- This is executed as long as the previous fetch succeeds.

FETCH NEXT FROM cursorBBDD

into @nombreBBDD

END

CLOSE cursorBBDD

DEALLOCATE cursorBBDD

select * from @tempFragmentation

Quedaría por tanto simplemente que modificarais este script para que las consultas recomendadas sean planificadas para ejecución en horas a las que vuestro modelo de negocio le vengan mejor ¿tal vez a las 2am? ¿tal vez las recreaciones deben ser ONLINE? Digamos que con este esqueleto no os resultará difícil definiros vuestro modelo de reindexación específico.

Publicar un comentario