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

Artículo
12/14/2024

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

Resumen

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

Estos son algunos aspectos que se deben tener en cuenta si decide ajustar los parámetros de crecimiento automático y autohrink.

Cómo configurar las opciones

Puede configurar o modificar la configuración de crecimiento automático y de reducción automática mediante una de las siguientes opciones:
- SQL Server Management Studio
- Una instrucción ALTER DATABASE
  - Usar las opciones 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, debe establecerlos en al menos dos lugares para cada base de datos (uno para el archivo de datos principal y otro para el archivo de registro principal). Si tiene varios archivos de datos o de registro, debe establecer las opciones en cada archivo. Dependiendo de su entorno, puede terminar con una configuración diferente para cada archivo de base de datos.

Consideraciones para AUTO_SHRINK

AUTO_SHRINK es una opción de base de datos en SQL Server. Al habilitar esta opción para una base de datos, esta base de datos es apta para reducirla mediante 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 en una instancia de SQL Server. Las operaciones frecuentes de crecimiento y reducción pueden provocar varios problemas de rendimiento.

Si varias bases de datos se someten a operaciones frecuentes de reducción y crecimiento, esto provocará fácilmente la fragmentación del nivel del sistema de archivos. Esto puede tener un impacto grave en el rendimiento. Esto es cierto si usa la configuración automática o si aumenta y reduce manualmente los archivos con frecuencia.
Después AUTO_SHRINK de 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 crecer.
La AUTO_SHRINK tarea en segundo plano puede ocupar recursos cuando hay muchas bases de datos que necesitan reducirse.
La AUTO_SHRINK tarea en segundo plano deberá adquirir bloqueos y otra sincronización que pueda entrar en conflicto con otras actividades de aplicación normales.

Considere la posibilidad de establecer 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 impedir la reducción y el crecimiento frecuentes de los archivos de base de datos.

Consideraciones para AUTOGROW

Si ejecuta una transacción que requiere más espacio de registro de lo que está disponible y ha activado la opción de 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 crecer por la cantidad configurada. Si el incremento de crecimiento es grande o hay algún otro factor que hace que tarde 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 derivar de un crecimiento automático de la parte de datos de la base de datos.
Si ejecuta una transacción grande que requiere que el registro crezca, otras transacciones que requieren 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 excesivo de archivos de registro virtual (VLF). Esto puede provocar problemas de rendimiento con las operaciones de inicio o en línea de la base de datos, la replicación, la creación de reflejo y la captura de datos modificados (CDC). Además, esto a veces puede causar problemas de rendimiento con modificaciones de datos.

Nota:

Si combina las opciones de crecimiento automático y de ajuste automático, puede crear una sobrecarga innecesaria. Asegúrese de que los umbrales que desencadenan las operaciones de crecimiento y reducción no provocarán cambios frecuentes de tamaño ascendente y descendente. Por ejemplo, puede ejecutar una transacción que hace que el registro de transacciones crezca en 100 MB en el momento en que se confirma. Algún tiempo después de que se inicie el autohrink y reduzca el registro de transacciones en 100 MB. A continuación, ejecuta la misma transacción y hace que el registro de transacciones crezca de nuevo en 100 MB. En ese ejemplo, va a crear una sobrecarga innecesaria y puede crear la fragmentación del archivo de registro, cualquiera de las cuales puede afectar negativamente al rendimiento.

Si aumenta la base de datos por pequeños incrementos, o si la aumenta y 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 en el 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, consulte Inicialización instantánea de archivos de la 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 su crecimiento de datos y registros a diario con el 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 cambiar estas actividades de mantenimiento a horas no punta.
El crecimiento automático y el crecimiento automático deben evaluarse cuidadosamente por un administrador de bases de datos entrenado (DBA); No deben dejarse no administrados.
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 va a usar en la configuración y la elección entre un crecimiento porcentual y un crecimiento de tamaño de MB específico depende de muchos factores del entorno. Una regla general general que puede usar para las pruebas es establecer la configuración de crecimiento automático en aproximadamente ocho el tamaño del archivo.
Active la \<MAXSIZE> configuración de cada archivo para evitar que un archivo crezca hasta un punto en el que use todo el espacio en disco disponible.
Mantenga el tamaño de las transacciones lo más pequeño posible para evitar el crecimiento de los archivos no planeados.

¿Por qué tengo que preocuparme por el espacio en disco si la configuración de 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 en disco disponible 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 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 pueden hacer que esas otras aplicaciones experimenten problemas.
El incremento de crecimiento del registro de transacciones debe ser lo suficientemente grande como para mantenerse al día 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 el ajuste automático. Comprueba esa base de datos y reduce esa base de datos si es necesario. A continuación, espera varios minutos antes de comprobar la siguiente base de datos que está configurada para el ajuste automático. En otras palabras, SQL Server no comprueba todas las bases de datos a la vez y las reduce 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 ajustar la carga automática en una instancia determinada de SQL Server, la base de datos puede tardar varias horas desde el momento en que la base de datos alcanza el umbral hasta que realmente se reduce.

Compartir a través de