Ing. Sistemas

Reorganizar y volver a generar índices
SQL Server Database Engine (Motor de base de datos de SQL Server) mantiene índices automáticamente cada vez que inserta, actualiza o elimina operaciones realizadas en los datos subyacentes. Con el tiempo, estas modificaciones pueden hacer que la información del índice se disperse por la base de datos (se fragmente). La fragmentación ocurre cuando losíndices tienen páginas en las que la ordenación lógica, basada en el valor de clave, no coincide con la ordenación física dentro del archivo de datos. Los índices muy fragmentados pueden reducir el rendimiento de la consulta y ralentizar la respuesta de la aplicación. Para obtener más información, vea el sitio web de Microsoft.
Puede solucionar la fragmentación del índice reorganizándolo o volviéndolo agenerar. Para los índices con particiones generados en un esquema de partición, puede utilizar cualquiera de estos métodos en un índice completo o en una sola partición de un índice cualquiera.
 Detectar la fragmentación
El primer paso necesario para detectar qué método de desfragmentación utilizar es analizar el índice a fin de determinar la magnitud de la fragmentación. Si utiliza la funcióndel sistema sys.dm_db_index_physical_stats, podrá detectar la fragmentación de un índice específico, de todos los índices de una tabla o vista indizada, de todos los índices de una base de datos o de todos los índices de todas las bases de datos. Para los índices con particiones, sys.dm_db_index_physical_stats también proporciona información de la fragmentación para cada partición.
El conjuntode resultados devuelto por la función sys.dm_db_index_physical_stats tiene las columnas siguientes.
Columna | Descripción |
avg_fragmentation_in_percent | Porcentaje de fragmentación lógica (páginas de un índice que no funcionan correctamente). |
fragment_count | Número de fragmentos (páginas hoja físicamente consecutivas) en el índice. |
avg_fragment_size_in_pages | Número promedio depáginas en un fragmento del índice. |
Una vez determinada la magnitud de la fragmentación, utilice la siguiente tabla para determinar el mejor método para corregir la fragmentación propiamente dicha.
Valor de avg_fragmentation_in_percent | Instrucción correctiva |
> 5% y < = 30% | ALTER INDEX REORGANIZE |
> 30% | ALTER INDEX REBUILD WITH (ONLINE = ON)* |
* La regeneración de uníndice se puede ejecutar en línea o sin conexión. La reorganización de un índice siempre se ejecuta en línea. Para lograr una disponibilidad similar a la opción de reorganización, debe volver a generar los índices en línea.
Estos valores proporcionan directrices generales para la determinación del punto en el que debe cambiar entre ALTER INDEX REORGANIZE y ALTER INDEX REBUILD. No obstante, los valoresreales pueden variar de un caso a otro. Es importante que experimente la determinación del mejor umbral para su entorno.
Los niveles de fragmentación muy bajos (inferiores al 5 por ciento) no deben tratarse con ninguno de estos comandos, dado que el beneficio de quitar una cantidad de fragmentación tan pequeña es casi siempre ampliamente superado por el costo de reorganizar o volver a generar elíndice.
Nota |
En general, la fragmentación en índices pequeños normalmente no se puede controlar. Las páginas de índices pequeños se almacenan en extensiones mixtas. Las extensiones mixtas pueden estar compartidas por hasta ocho objetos, de modo que es posible que no se pueda reducir la fragmentación en un índice pequeño después de reorganizar o volver a generar dicho índice. Para obtenermás información acerca de las extensiones mixtas, vea Descripción de páginas y extensiones. |
Ejemplo
En el siguiente ejemplo se realiza una consulta a la función de administración dinámica sys.dm_db_index_physical_stats para que devuelva el promedio de fragmentación de todos los índices de la tabla Production.Product. Si utiliza la tabla anterior, la solución recomendada es reorganizar...