Crecimiento automático de los ficheros de la base de datos

Una de las configuraciones que debemos revisar en nuestras bases de datos es la que corresponde a los parámetros de crecimiento automático (autogrowth) de sus ficheros.

Nuestras bases de datos crecen con el uso y SQL Server reserva espacio en disco duro para los ficheros que la componen, pidiéndole al sistema operativo más espacio cuando el que tenía asignado deja de estar disponible. Cada vez que esto ocurre, el rendimiento de nuestra base de datos se ve afectado, ya que el servidor debe bloquear la actividad en ella mientras obtiene el nuevo espacio. Así pues, es deseable configurar las opciones de crecimiento de los ficheros de la base de datos de tal manera que:

• Los eventos de crecimiento ocurran con poca frecuencia
• No nos excedamos en la cantidad de disco reservada a los ficheros de una base de datos, ya que podría no ser necesario y estaríamos consumiendo recursos útiles para otras bases de datos en el mismo disco o sistema de discos.

No existe una fórmula mágica que nos dé unos valores óptimos universales con los que configurar todas nuestras bases de datos, ya que éstos dependerán de su actividad y necesidad de espacio. Incluso, para una misma base de datos, estos valores podrían ir cambiando con el tiempo (dependiendo de número de usuarios, de cambios en la estructura que incluyan datos de más tamaño...)

SQL Server asigna unos valores iniciales a nuestras bases de datos que, generalmente, son poco adecuados.  Para el fichero de datos, el valor inicial es de 1MB, mientras que al fichero del log le asigna un 10% sobre su tamaño actual. Una base de datos en producción puede llegar a crecer fácilmente varios MB en un solo día. Esta circunstancia implicaría que la base de datos estaría realizando reservas de nuevo espacio en disco todas esas veces, posiblemente durante la fase de actividad de la misma, en la que el rendimiento debe ser óptimo. Además, los nuevos espacios en disco no se reservarían de manera continua, aumentando la fragmentación de los ficheros en disco.

Figura 1. Configuración inicial de los ficheros de una base de datos en SQL Server

Para ambos ficheros podemos elegir entre asignar un porcentaje o un valor fijo. Debemos estudiar bien cómo crecen para determinar cuál es la configuración óptima.

Inicialmente, la opción de asignar un porcentaje puede parecer la mejor elección. Sin embargo, si nuestra base de datos va a crecer considerablemente en su uso diario, no es lo más apropiado. Supongamos que aplicamos un 10% de crecimiento automático al fichero de datos a una base de datos recién creada. Si el tamaño de la base de datos progresa de forma constante con el paso del tiempo, desde unos pocos megas hasta cientos de Gigabytes o incluso Terabytes, las asignaciones de nuevo espacio que estaremos haciendo a nuestra base de datos serán igualmente muy dispares entre sus primeros días de vida y meses después, cuando esté llena de información.

Figura 2. Opciones de configuración del crecimiento de los ficheros

Así pues, la mejor opción es conocer en qué forma crece nuestra base de datos y elegir valores adecuados, que cumplan con las dos premisas enunciadas anteriormente, tanto para el fichero de datos como para el fichero de log. Es una simple configuración que nos ahorrará problemas de rendimiento, típicos en bases de datos de gran tamaño que usan los valores por defecto propuestos por SQL Server.

Finalmente, cabe reseñar que siempre tenemos la alternativa de desactivar la opción de autocrecimiento en nuestras bases de datos, y ejecutar tareas de mantenimiento que comprueben el tamaño las mismas y les asignen nuevo espacio en caso de ser necesario mediante scripts. Este proceso se podría llevar a cabo fuera de los horarios operativos de la base de datos. Pero eso lo dejamos para un próximo post...

Enlaces externos:

• http://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/