¡TRANSOLUTIONS AHORA ES PARTE DE BAUFEST!
Ingresa a baufest.com para conocer más sobre nuestros servicios.

BLOG

Viernes, 18 Mayo 2018 08:18

Mantenimiento de Índices y Estadísticas en Azure SQL Database

Michael Villegas InnovaMichael Villegas, DBA Senior en Trans Solutions Systems, es un entusiasta de la tecnología, principalmente del mundo de las bases de datos donde él se desenvuelve como un DBA desde hace ya más de 6 años. En esta oportunidad veremos lo importante que es una base de datos con mantenimiento para evitar problemas como tiempos de inactividad y pérdida de datos.

Desde la aparición de Azure SQL Database como servicio PaaS, se nos presentaron una serie de características muy útiles que alivian el trabajo cotidiano de un DBA. Entre ellas tenemos: evitar la administración del servidor, no preocuparnos de la ejecución de backups, evitar mantener al día la base de datos en cuanto a los parches, etc. Así mismo, facilita la realización de otras tareas que no son tan comunes, pero si importantes para un DBA, como el afinamiento a través de índices, encriptación de la base de datos, monitoreo de consultas pesadas, etc.

Con todas estas características que nos trae Azure SQL Database, uno podría dejarse llevar y despreocuparse del mantenimiento normal que se le hace a una base de datos. Gracias a este servicio, la gran mayoría de tareas no requieren nuestra intervención y para el resto sólo es cuestión de dar unos cuantos clics en el portal y todo esta perfectamente configurado. Sin embargo, la realidad es que las bases de datos, así estas sean PaaS, necesitan del cuidado de un DBA, ya que adolecen de los mismos problemas que las bases de datos regulares (on-premise) con relación al mantenimiento de índices y estadísticas.

Primero hablaremos del mantenimiento de estadísticas. Estas son muy importantes para la generación de los planes de ejecución. De ellas depende el camino que decida tomar el motor de base de datos para poder realizar el acceso a los datos. Históricamente, SQL Server siempre ha tenido inconvenientes para mantener sus estadísticas actualizadas. Esto se debe a que la opción de actualización automática de estadísticas tiene habilitada la base de datos por defecto, sin embargo, se basa en un algoritmo que exige que antes de que se lance la actualización automática, se hayan cambiado una gran cantidad de registros dentro de la tabla. Todo esto, en muchas ocasiones, tarda demasiado tiempo y por ende causa que tengamos estadísticas desactualizadas fácilmente.

Ahora, realizar el mantenimiento de los índices también es importante. Las modificaciones de los registros en las tablas causan que los índices anexos a ellas sufran de fragmentación. Este fenómeno hace que SQL Server tenga que leer más paginas para poder obtener el resultado requerido. Lo cual, aumenta las operaciones de I/O incrementando el tiempo de ejecución de las consultas.

En las bases de datos “on premise”, tenemos varias opciones para realizar mantenimientos de índices y estadísticas. La más conocida y recomendada es la solución de Ola Hallengren. Esta solución tiene una serie de opciones las cuales permiten hacer un mantenimiento mas inteligente de los índices y estadísticas de las que se ofrece dentro del mismo SQL Server. Me refiero a los planes de mantenimientos y al ya conocido sp_updatestats. Sin embargo, esta solución no esta completamente optimizada para un entorno PaaS. Depende de Jobs y asume que el mantenimiento de las bases de datos de la instancia se maneje desde una base de datos centralizada.

Para las bases de datos PaaS se ha encontrado una solución derivada de la original de Ola Hallengren, la cual fue tomada de este post. Esta solución requiere la creación de un esquema llamado “dba” donde se guardará la tabla de log y los procedimientos almacenados necesarios para que la solución funcione. Una vez que se tienen creados los objetos dentro de la base de datos PaaS, se necesita programar una tarea para que ejecute el procedimiento almacenado que realizará el mantenimiento de índices y estadísticas. Para programar esta tarea se tienen muchas opciones, pero la escogida para el mantenimiento de nuestras bases de datos fue “Azure Automation”. Para ello se tomó como guía el siguiente post.

Finalmente, se programó la ejecución de la tarea de mantenimiento utilizando los siguientes valores para los parámetros. Estos aseguran que se considere la reorganización de los índices a partir de encontrar un 10% de fragmentación dentro de ellos. Además, considera que una fragmentación alta se considere a partir de un 30% de fragmentación, y en esos casos se haga un “rebuild” de los índices. En conclusión, también se le indica que haga la actualización de estadísticas solo para las que han sufrido modificaciones.

EXECUTE dba.IndexOptimize
  @Databases = '$AzureSQLDatabaseName',
  @FragmentationLow = NULL,
  @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
  @FragmentationLevel1 = 10,
  @FragmentationLevel2 = 30,
  @UpdateStatistics = 'ALL',
  @OnlyModifiedStatistics = 'Y',
  @LogToTable = 'Y',
  @Execute = 'Y'