SQL Server 2008 script para reconstruir todos los índices de todas las tablas en todas las bases de datos
Tengo regularmente las mismas preguntas alrededor de los index y su fragmentación en SQL2008. Así que decidí añadir un puesto más en Internet para discutir de la reconstrucción y la reorganización del index y la forma de detectar la fragmentación de los indexes.
Entrada
Como la fragmentación puede tener un impacto negativo en la eficiencia de acceso a datos, una de las principales tareas de un DBA es mantener los indexes de la base de datos.
En el ciclo de vida de una base de datos, la fragmentación es un comportamiento natural y esperado. Si la base de datos se actualiza con frecuencia a través de un INSERT, UPDATE o DELETE que podemos esperar a la fragmentación en el tiempo.
Plato principal
Desde SQL 2005, la función de administración dinámica sys.dm_db_index_physical_stats devuelve información de tamaño y la fragmentación de los datos e indexes en la tabla o vista especificada.
| NB: Aunque SQL Server 2008 sigue apoyando el DBCC SHOWCONTING de SQL Server 2000, esta característica se quitará en una versión futura de SQL Server. Así que invito a que lo borre de su nuevos desarrollos. |
1. Hay tres modos en la sintaxis sys.dm_db_index_physical_stats:
| LIMITED (defaut): | Este modo es más rápido, ya que escanea un menor número de páginas. Analiza todas las páginas en un Heap, pero escanea las páginas nivel primario, lo que significa que las páginas encima del nivel hoja de un index. |
| SAMPLED | Este modo se devuelven las estadísticas sobre la base de una muestra de un porcentaje de todas las páginas en el index o Heap. Si el index o el heap tiene menos de 10 000 páginas, el DETAILED método se utiliza en lugar de SAMPLED. |
| DETAILED | Este modo escanea todas las páginas y devuelve todas las estadísticas. Estar atentos, para LIMITED hasta DETAILED hasta LIMITED, los modos son cada vez más lento a medida que más trabajo.. En mi script que lo utilizan |
2. Hay dos tipos de fragmentación de indexes.
| Logical fragmentation (indexes) | es el porcentaje de páginas de índice que corresponde a las páginas fuera de orden en las hojas. Una orden es una página para que la siguiente página física asignada en el IAM no es la página apuntada por el puntero de página siguiente en la página de la hoja actual. |
| Extent fragmentation (heap) | es el porcentaje de extensiones en un montón de fuera de orden en las páginas del periódico. Una medida que no se solicita cuando la extensión de la página actual a un montón que no es físicamente la extensión siguiente contiene las extensiones de la página anterior. |
Para reducir la fragmentación, es necesario reorganizar o reconstruir el index. Elegir entre la reorganización y reconstrucción depende del valor de la fragmentación. El nivel de fragmentación de un index o el heap se muestra en la columna avg_fragmentation_in_percent. El valor debe estar lo más cerca posible a cero. Un valor entre 5-30% indica fragmentación moderada, mientras que cualquier valor de más de 30% indica una alta fragmentación.
El campos avg_page_space_used_in_percent es otro valor que vale la pena de examinar de cerca. Este valor representa la cantidad de espacio utilizado en los index. Un valor por debajo del 75% se asocia generalmente con la fragmentación interna (más páginas en blanco en nuestro libro recomendado).
Postre
El programa se ejecutará en las versiones de SQL Server 2008 o superior. El objetivo es reducir la fragmentación de volver a crear índices, la reorganización o la reconstrucción de los índices:
DESCARGAR AQUÍ Ejemplo de script
Para ejecutar el procedimiento almacenado sin desfragmentar los indexes: EXECUTE handdleFragmentationIndexes @debugMode = 1
Para ejecutar el procedimiento almacenado y la desfragmentación de indexes: EXECUTE handdleFragmentationIndexes
Para ejecutar el procedimiento almacenado y los indexes defragement en una base de datos específica: EXECUTE handdleFragmentationIndexes databaseName @ = 'MyDatabaseName'
El script de ejemplo se extrae en parte del BOL siguiente: sys.dm_db_index_physical_stats (Transact-SQL)
Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |