Una de las principales responsabilidades del DBA es garantizar que las bases de datos funcionen de forma óptima. La forma más eficaz de hacerlo es mediante los índices. Los índices SQL son uno de los mayores recursos cuando se trata de mejorar el rendimiento. Sin embargo, lo que ocurre con los índices es que se degradan con el tiempo. Para que esto no sea un problema a largo plazo podemos utilizar un mantenimiento de índices y estadísticas como te vamos a explicar a continuación.
Cómo crear y configurar el mantenimiento de índices y estadísticas
Lo primero que tenemos que hacer es crear el procedimiento almacenado «IndexOptimize» que nos proporciona “ola.hallengren”. Este lo usaremos como base, aunque podríamos usar otros métodos.
Teniendo dicho procedimiento creado lo podemos modificar como queramos. Nosotros recomendamos la siguiente configuración:
EXECUTE [dbo].[IndexOptimize] @Databases = 'ALL_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @LOBCompaction = 'N', @TimeLimit='14400', @LockTimeout='0', @DatabaseOrder=NULL, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @LogToTable = 'Y'
La anterior configuración realiza lo siguiente:
El script realiza el mantenimiento de los índices y estadísticas sobre todas las bases de datos que indiquemos en la variable de @Databases. Intenta hacerlo online si se puede, y si no, lo hace offline.
Están definidos los niveles de fragmentación que recomienda Microsoft:
- Si tiene un 5% no realiza mantenimiento de ellos.
- Entre un 5% y un 30% reorganiza.
- Más de un 30% reconstruye.
Si tiene menos de 1000 páginas el índice no hace nada tampoco, ya que no afecta al rendimiento.
Respecto a las estadísticas, al usar el parámetro @OnlyModifiedStatistics, actualizará solo las que hayan cambiado, siendo más eficiente.
Gracias al parámetro @TimeLimit podemos ponerle un tiempo límite para asegurarnos de que acabe antes de empezar con producción.
Una vez generado y configurado el script de mantenimiento lo debemos meter dentro de un job para poder programar cada cuanto queremos que se ejecute, tanto que días como a que horas. Lo recomendado es todos los días y que se realice en horas donde no haya producción o baja actividad. Si tienes un SQL Server Express puedes realizarlo como os explicamos en la anterior entrada, donde os contamos cómo realizar un script de mantenimiento en SQL Server Express.
Esperamos haberte ayudado, sino confía en los profesionales y deja que nos encarguemos del mantenimiento.
Nos vemos en próximas entradas.