lunes, 25 de julio de 2011

Mantenimiento de Indices

FRAGMENTACION DE INDICES SQL SERVER

A - Conceptos Generales y Tipos de Fragmentación


Los índices son los únicos objetos que pierden su efectividad con el paso del tiempo si no se le da un mantenimiento adecuado.

La fragmentación de los índices tiene lugar cuando se modifican los registros de una tabla por inserción, update o borrado de datos y estas modificaciones afectan una o más páginas del índice.

Hay dos tipos de fragmentación a nivel de índice, ambas afectan directamente la performance de los procesos que utilizan los mimos, veamos:

A- Fragmentación Interna: es el tipo de fragmentación que tiene lugar cuando se lleva a cabo una operación de borrado. El borrado de datos genera liberación de espacio en las páginas de índice, lo que produce que sólo una parte de la página del índice esté ocupada. Esto provoca que el SQL Server con el tiempo tenga que leer más páginas de índice que las necesarias, por el mal aprovechamiento que implica tener páginas de índice semi vacías.

B- Fragmentación Externa: cuando llevamos a cabo un insert y la página no puede almacenarse, el SQL Server genera una nueva página para alojar los datos insertados, conservando un orden lógico, pero no un orden físico en las páginas de índice, a esto se lo conoce como “page split” . Cuando el SQL Server hace un page split está generando fragmentación externa.


B - Detección y Evaluación de la Fragmentación


A partir del Sel Server 2005 fue reemplazada la utilidad DBCC SHOWCONTIG por una vista del sistema llamadaa SYS.DM_DB_INDEX_PHYSICAL_STATS , la cual arroja datos más certeros y consume menos recursos del sistema en su ejecución.

Veamos pues un ejemplo mediante el cual se relevan aquellos índices que cuentan con más de 1000 páginas y un porcentaje de fragmentación superior al % 20.

SELECT
b.name as 'database',
c.name as 'name',
index_type_desc,
avg_fragmentation_in_percent,
page_count
FROM sys.dm_db_index_physical_stats (9, NULL, NULL, NULL, 'limited')AS A
LEFT JOIN SYS.DATABASES B
ON A.DATABASE_iD = B.DATABASE_ID
LEFT JOIN SYS.OBJECTS C
ON A.OBJECT_iD = C.OBJECT_iD
WHERE avg_fragmentation_in_percent > 20 -- buscando % de fragmentación en este caso > 20
and index_level = 0 --> analizando la rama principal de los índices
and page_count > 1000 --> mas de 1000 hojas

order by name


Vamos a hacer un paréntesis aquí y veamos lo que Microsoft nos dice en torno a los niveles de fragmentación:

A- “ Fragmentación Inócua”: Microsoft dice que no debiéramos estar preocupados por la eventual fragmentación de índices que contienen menos de 1000 páginas. Este es todo un parámetro a tener en cuenta. También nos habla de no tomar en cuenta la fragmentación existente sobre tablas pequeñas ya que las mismas no tienen impacto sobre la performance de las queries y son muy difícil de ser eliminadas por rutinas de reorganizado o reconstrucción de índices.

B- “Fragmentación Baja”: es aquella que no supera el % 5 del índice. La recomendación es no intentar eliminar la misma, pues puede ser mayor el costo de hacerlo que los beneficios obtenidos como consecuencia de ello.

C- “Fragmentación Media”: es la fragmentación > % 5 y < = al %30.: Microsoft recomienda hacer un Reorganize de los índices.

D- “Fragmentación Alta”: es la fragmentación > al % 30: Microsoft recomienda hacer un Rebuild de los índices.


C - Métodos de Desfragmentación


Hay varios métodos para desfragmentar índices, es importante elegir el método adecuado acorde al entorno de aplicación del mismo.


1) Alter Index Reorganize


• Se recomienda usar este método cuando el % de fragmentación es de > % 5 y < = al %30.:
• Se utiliza la instrucción ALTER INDEX con la claúsula REORGANIZE. Esta instrucción reemplaza a la vieja DBCC INDEXDEFRAG
• La reorganización se realiza en línea ya que no mantiene grandes bloqueos
• Este proceso utiliza una mínima cantidad de recursos del sistema
• Básicamente se desfragmentan los índices, se compactan las páginas vacías acordes al valor de fill factor y reordenan la páginas de índices a nivel físico, para que coincidan con el ordenamiento a nivel lógico.
• Para reorganizar las páginas de un índice particionado en una de las particiones, se be utilizar la cláusula PARTITION
• El reorganizar un índice No regenera las estadísticas.

Alter Index "Nombre_Indice" on "Nombre_Tabla"

Reorganize;


  2) Alter Index Rebuild / Create Index with Drop_Existing = ‘on’


• Se regenera el índice en su totalidad respetando el fill factor y, se hace un update de las estadísticas
• Es una operación que demanda muchos recursos del sistema puesto que el motor de base de datos requiere el doble de espacio del que ocupa el índice para crear primero el índice nuevo y luego borrar el viejo. Además se toma un espacio adicional para hacer esta operación en el disco salvo que se le indique hacer el ordenamiento en la TempDb
• Se usa sólo para reconstruir índices cuyo porcentaje de fragmentación esté por sobre el % 30
• Puede ser realizada en línea excepto que el índice esté sobre columnas de tipo LOB (image, text, nvarchar, xml, varchar(max)), o que sean índices XML
• Se puede hacer un Rebuild de un índice con la cláusula PARTITION, sólo en el caso del método Alter Index

Alter Index "Nombre_Indice" on "Nombre_Tabla"

Rebuild;


Create Index "Nombre_Indice" on "Nombre_Tabla(nombre campo)"

With Drop


3) Disabling Indexes


• Es el método de reconstrucción total de un índice que menos recursos consume.
• Al deshabilitar un índice se impide que el usuario tenga acceso al mismo y a las tablas subyacentes. Esto hace que solo pueda ser usado en ambientes que permitan un downtime para llevar a cabo la operación.
• No requiere a diferencia de la sentencia Rebuild del doble de espacio en disco que usa el índice, puesto que la definición del índice se conserva en los metadatos eliminándose físicamente el mismo. (para ello deben deshabilitarse todos los índices clustered y luego, en otra transacción, se debe implementar un Rebuild de todos los índices)


Alter Index "Nombre_Indice" Clustered on "Nombre_Tabla"
DISABLE;
Alter Index ALL on "Nombre_Tabla"
REBUILD with (fillfactor = xx, sort in TempDb = On);



4) Cuándo los Indices Non-Clustered se reconstruyen automáticamente?


• Desde un Heap a un Cluster: Se recontruyen los índices Non Clustered

• Desde un Cluster a un Heap: Se reconsruyen los índices Non Clustered

• Rebuilding un Unique Cluster Index : No se reconstruyen los indices Non Clustered

• Rebuilding un No Unique Cluster Index –

4.1) Hasta sql server 2000 se reconstruyen los índices non clustered
4.2) Desde sql Server 2005 ya no se reconstruyen los índices nonclustered

6 comentarios:

  1. Muy buena info! Estoy haciendo un rebuild unicamente de las tablas y la fragmentacion se va a 0 pero el page_count sigue igual, esta bien eso? Que deberia hacer?
    No entiendo porque haces un rebuild y un create with drop juntos. o son dos alternativas?

    Y respecto a indices, estoy en una nueva base de datos, que tiene muchos indices varios en cada tabla, esta bien eso? Como considero cuando devalua a la tabla? Gracias

    ResponderEliminar
  2. Buenas Tardes Damian, un gusto saludarte.
    En el post explico las distintas posibilidades a la hora de desfragmentar un Indice y sus costos y beneficios.
    Básicamente tu puedes Reconstruir el Indice (Alter Index Rebuild / Create Index with Drop_Existing = ‘on’) - son 2 métodos distintos que tienen los mismos resultados.
    Debes escoger uno solo de estos métodos, pero sabiendo que ambos
    1. Reconstruyen totalmente los idx llevando a cero la fragmentación
    2. Requieren el doble del espacio ocupado por el idx ya que antes de borrar el idx viejo el proceso regenera el idx nuevo(conviviendo ambos en un determinado instante). Esto generará que veas en tu file, donde tienes ubicados los idx, un espacio no utilizado (es el espacio tomado por el proceso para llevar a cabo su objetivo, que luego es liberado)
    3. Regeneran la estadísticas en su totalidad

    Puedes también intentar reorganizar los idx,
    Ventajas: no ocuparás el doble de espacio en disco, es un proceso mucho menos costoso
    Desventajas: no regeneras estadísticas, obtienes un nivel de desfragmentación menor que si reconstruyes de cero el idx.

    Yo te aconsejo reconstruir un idx si lo encuentras fragmentado en al menos un % 35, de lo contrario reorganizar.

    He puesto en el mismo post un script al que haces referencia para estudiar el % de fragmentación. Solo tienes que tener en cuenta el page_count para el where de esa query, ya que de ese modo evitarás desfragmentar idx pequeños. Pero el page count no te dice el % de fragmentación sino lo grande o chico que es un idx.
    Solo el avg_fragmentation_in_percent, es el que te indica la fragmentación real del idx.

    Respecto de tu última pregunta relacionada con si tienes muchos o pocos idx para tus tablas. Esto es muy relativo con tu ambiente de producción si es OLAP u OLTP.

    Yo te aconsejo que veas mi post en este mismo blog donde dejé un script con el cual podrás ver cuáles idx de los que tienes definidos son usados realmente y cuales no merecen seguir existiendo.

    Quedo a tu disposición, abrazo de Argentina.

    ResponderEliminar
  3. Hola Gustavo!!, en donde encontraste las recomandaciones de microsoft?.

    ResponderEliminar
  4. Muchas gracias por su blog, me ayudó mucho.

    Saludos desde Guayaquil, Ecuador.

    ResponderEliminar
  5. Hola, ¿Quiera saber si podría orientarme? hice un rebuild a un indice que estaba fragmentado al 65%, pero bajó la fragmentación solo hasta el 25% ¿A qué se puede deber? Gracias de antemano

    ResponderEliminar
  6. Hola Gustavo, mis saludos. Se que el post es un poco viejo, pero aprovecho de hacer una pregunta, tengo uns BD, que tiene porcentajes de fragmentacion elevados, 60,70 y hasta 90% en algunos objetos. El problema es que no importa el proceso que haga, la fragmentacion no baja a numeros aceptables, lo mas que logro es que baje a un 50%. Eso es normal? Por que no baja mas?
    Gracias por tu aporte.

    ResponderEliminar