Consideraciones sobre la configuración de crecimiento automático y reducción automática en SQL Server

Artículo
06/20/2023

Versión del producto original: SQL Server
Número de KB original: 315512

Resumen

La configuración predeterminada de crecimiento automático y reducción automática es adecuada en muchos sistemas de SQL Server. Sin embargo, hay entornos en los que es posible que tenga que ajustar los parámetros autogrow y autoshrink. En este artículo se proporciona información general para guiarle a la hora de seleccionar esta configuración para su entorno.

Estas son algunas cosas a tener en cuenta si decide ajustar los parámetros de crecimiento automático y reducción automática.

Cómo configurar los valores

Puede configurar o modificar los valores de crecimiento automático y reducción automática mediante una de las siguientes opciones:
- SQL Server Management Studio
- Una ALTER DATABASE instrucción
  - Usar opciones de archivo y grupo de archivos para modificar la configuración de crecimiento automático
  - Use las opciones SET para configurar AUTO_SHRINK las opciones.
Nota:

Para obtener más información sobre cómo establecer esta configuración en el nivel de archivo de base de datos, vea Agregar datos o archivos de registro a una base de datos.

También puede configurar la opción de crecimiento automático al crear una base de datos.

Para ver la configuración actual, ejecute el siguiente comando de Transact-SQL:
```
sp_helpdb [ [ @dbname= ] 'name' ]
```
Tenga en cuenta que la configuración de crecimiento automático es por archivo. Por lo tanto, tiene que establecerlas en al menos dos lugares para cada base de datos (una para el archivo de datos principal y otra para el archivo de registro principal). Si tiene varios datos o archivos de registro, debe establecer las opciones en cada archivo. Dependiendo del entorno, puede terminar con una configuración diferente para cada archivo de base de datos.

Consideraciones para AUTO_SHRINK

AUTO_SHRINKes una opción de base de datos en SQL Server. Al habilitar esta opción para una base de datos, esta base de datos se convierte en apta para reducirse por una tarea en segundo plano. Esta tarea en segundo plano evalúa todas las bases de datos que cumplen los criterios para reducir y reducir los archivos de datos o de registro.

Debe evaluar cuidadosamente la configuración de esta opción para las bases de datos de una instancia de SQL Server. Las operaciones de aumento y reducción frecuentes pueden dar lugar a diversos problemas de rendimiento.

Si varias bases de datos se someten a operaciones de reducción y crecimiento frecuentes, esto conducirá fácilmente a la fragmentación del nivel del sistema de archivos. Esto puede tener un impacto grave en el rendimiento. Esto es cierto tanto si usa la configuración automática como si aumenta y reduce manualmente los archivos con frecuencia.
Después de AUTO_SHRINK reducir correctamente los datos o el archivo de registro, una operación DML o DDL posterior puede ralentizarse significativamente si se requiere espacio y los archivos deben aumentar.
La AUTO_SHRINK tarea en segundo plano puede tomar recursos cuando hay muchas bases de datos que necesitan reducirse.
La AUTO_SHRINK tarea en segundo plano tendrá que adquirir bloqueos y otra sincronización que pueda entrar en conflicto con otra actividad normal de la aplicación.

Considere la posibilidad de establecer las bases de datos en un tamaño necesario y aumentarlas previamente. Deje el espacio sin usar en los archivos de base de datos si cree que los patrones de uso de la aplicación los necesitarán de nuevo. Esto puede evitar la reducción y el crecimiento frecuentes de los archivos de base de datos.

Consideraciones sobre AUTOGROW

Si ejecuta una transacción que requiere más espacio de registro del que está disponible y ha activado la opción crecimiento automático para el registro de transacciones de esa base de datos, el tiempo que tarda la transacción en completarse incluirá el tiempo que tarda el registro de transacciones en aumentar en la cantidad configurada. Si el incremento de crecimiento es grande o hay algún otro factor que hace que se lleve mucho tiempo, la consulta en la que se abre la transacción podría producir un error debido a un error de tiempo de espera. El mismo tipo de problema puede deberse a un crecimiento automático de la parte de datos de la base de datos.
Si ejecuta una transacción de gran tamaño que requiere que el registro crezca, otras transacciones que requieran una escritura en el registro de transacciones también tendrán que esperar hasta que se complete la operación de crecimiento.
Si tiene muchos crecimientos de archivos en los archivos de registro, es posible que tenga un número excesivamente grande de archivos de registro virtual (VLF). Esto puede dar lugar a problemas de rendimiento con las operaciones de inicio y en línea de la base de datos, replicación, creación de reflejo y captura de datos modificados (CDC). Además, esto a veces puede causar problemas de rendimiento con las modificaciones de datos.

Nota:

Si combina las opciones de crecimiento automático y reducción automática, puede crear una sobrecarga innecesaria. Asegúrese de que los umbrales que desencadenan las operaciones de aumento y reducción no provocarán cambios frecuentes de tamaño ascendente y descendente. Por ejemplo, puede ejecutar una transacción que haga que el registro de transacciones aumente en 100 MB en el momento en que se confirma. Algún tiempo después, la reducción automática se inicia y reduce el registro de transacciones en 100 MB. A continuación, ejecute la misma transacción y hará que el registro de transacciones vuelva a crecer en 100 MB. En ese ejemplo, está creando una sobrecarga innecesaria y, potencialmente, creando la fragmentación del archivo de registro, cualquiera de las cuales puede afectar negativamente al rendimiento.

Si aumenta la base de datos en pequeños incrementos o si la hace crecer y, a continuación, la reduce, puede acabar con la fragmentación del disco. La fragmentación del disco puede causar problemas de rendimiento en algunas circunstancias. Un escenario de pequeños incrementos de crecimiento también puede reducir el rendimiento del sistema.

En SQL Server, puede habilitar la inicialización instantánea de archivos. La inicialización instantánea de archivos acelera las asignaciones de archivos solo para los archivos de datos. La inicialización instantánea de archivos no se aplica a los archivos de registro. Para obtener más información, vea Inicialización instantánea de archivos de base de datos.

Procedimientos recomendados para el crecimiento automático y la reducción automática

Para un sistema de producción administrado, debe considerar que el crecimiento automático es simplemente una contingencia para un crecimiento inesperado. No administre los datos y registre el crecimiento día a día con crecimiento automático.
Puede usar alertas o programas de supervisión para supervisar los tamaños de archivo y aumentar los archivos de forma proactiva. Esto le ayuda a evitar la fragmentación y le permite desplazar estas actividades de mantenimiento a horas no punta.
El escalado automático y el crecimiento automático deben ser evaluados cuidadosamente por un administrador de bases de datos (DBA) entrenado; No deben dejarse sin administrar.
El incremento de crecimiento automático debe ser lo suficientemente grande como para evitar las penalizaciones de rendimiento enumeradas en la sección anterior. El valor exacto que se usará en la configuración y la elección entre un crecimiento porcentual y un crecimiento específico del tamaño de MB depende de muchos factores del entorno. Una regla general que puede usar para las pruebas es establecer la configuración de crecimiento automático en aproximadamente un ocho del tamaño del archivo.
Active la \<MAXSIZE> configuración de cada archivo para evitar que un archivo crezca hasta un punto en el que se use todo el espacio disponible en disco.
Mantenga el tamaño de las transacciones lo más pequeño posible para evitar el crecimiento de archivos no planeados.

¿Por qué tengo que preocuparme por el espacio en disco si la configuración del tamaño se controla automáticamente?

La configuración de crecimiento automático no puede aumentar el tamaño de la base de datos más allá de los límites del espacio disponible en disco en las unidades para las que se definen los archivos. Por lo tanto, si confía en la funcionalidad de crecimiento automático para ajustar el tamaño de las bases de datos, debe comprobar de forma independiente el espacio disponible en el disco duro. La configuración de crecimiento automático también está limitada por el MAXSIZE parámetro que seleccione para cada archivo. Para reducir la posibilidad de quedarse sin espacio, puede supervisar el contador de Monitor de rendimiento SQL Server: Databases Object: Data File(s) Size (KB) y configurar una alerta cuando la base de datos alcanza un tamaño determinado.
El crecimiento no planeado de los archivos de datos o de registro puede ocupar espacio que otras aplicaciones esperan estar disponibles y puede provocar problemas en esas otras aplicaciones.
El incremento de crecimiento del registro de transacciones debe ser lo suficientemente grande como para mantenerse por delante de las necesidades de las unidades de transacción. Incluso con el crecimiento automático activado, puede recibir un mensaje que indica que el registro de transacciones está lleno, si no puede crecer lo suficientemente rápido como para satisfacer las necesidades de la consulta.
SQL Server no prueba constantemente las bases de datos que han alcanzado el umbral configurado para la reducción automática. En su lugar, examina las bases de datos disponibles y busca la primera que está configurada para la reducción automática. Comprueba esa base de datos y la reduce si es necesario. A continuación, espera varios minutos antes de comprobar la siguiente base de datos configurada para la reducción automática. Es decir, SQL Server no comprueba todas las bases de datos a la vez y las reduce todas a la vez. Funcionará a través de las bases de datos de forma round robin para escalonar la carga durante un período de tiempo. Por lo tanto, en función del número de bases de datos que haya configurado para realizar la reducción automática en una instancia de SQL Server determinada, puede tardar varias horas desde el momento en que la base de datos alcanza el umbral hasta que realmente se reduce.