Desbordando AVG y SUM: Cuando SQL Server se queda sin dedos para sumar

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.

Configuración inicial de los ficheros de una base de datos en SQL Server
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:

Comentarios

  1. Hola, se agradece la información, esta muy clara.
    Me pasa que al realizar el siguiente script:

    use master
    GO
    USE [master]
    GO
    ALTER DATABASE [database] MODIFY FILE ( NAME = N'Conciliacion_log', MAXSIZE = UNLIMITED)
    GO

    Voy luego a las propiedades de la base y nuevamente esta con crecimiento limitado.
    A que se puede deber que no tome los cambios del script?

    ResponderEliminar
    Respuestas
    1. Hola, Héctor:

      En principio el script es correcto. Simplemente, asegúrate de que el nombre del fichero es el correcto y de que sea el del log el que quieres cambiar.

      Eliminar
  2. a mi me sucede lo mismo y no lo hago con el script, lo hago directamente clic derecho en archivo de la base de datos, luego archivo y le doy crecimiento automático al archivo del log y luego al otro día aparece tamaño de archivo restringido al valor que tenia inicialmente,.

    ResponderEliminar
    Respuestas
    1. Hola, Óscar:

      La verdad es que me resulta complicado poder decir por qué puede ocurrirte esto.

      Algunas cosas que se me vienen a la cabeza son:

      1. Que restaures un backup y se vuelvas a la configuración que tenía la base de datos en el backup.

      2. Que no tengas más espacio en disco y por ello el fichero no pueda coger más tamaño.

      3. Que el sistema operativo, por algún motivo, pueda estar limitando el espacio destinado a SQL Server...

      Saludos.

      Eliminar
  3. Hola buen día,

    tengo mi base de datos con crecimiento automatico en 10 MB ilimitada, tambien cuento con espacio en el disco del archivo de la base de datos, pero me llego una alerta donde dice que no le queda espacio a mi base, revise y tiene 1.95 MB disponibles y no realiza el crecimiento automatico, espero me puedan ayudar.

    Saludos

    ResponderEliminar

Publicar un comentario