¿Habilitar AUTO_CLOSE es una mala idea?

Desde un punto de vista personal, te permite trabajar con la base de datos de producción AUTO CERRADO opción no es una mejor práctica. Déjame explicarte por qué no deberías incluir AUTO CERRADO y las implicaciones de usar esta opción.

El otro día tuve que buscar Registro de errores en el servidor de prueba. Después de un tiempo de espera de dos minutos, vi una gran cantidad de mensajes almacenados en el registro y decidí verificar el tamaño del registro con xp_enumerrorlogs:

DECLARE @t TABLE (lod_id INT PRIMARY KEY, last_log SMALLDATETIME, size INT)
INSERT INTO @t
EXEC sys.xp_enumerrorlogs

SELECT lod_id, last_log, size_mb = size / 1048576.
FROM @t
lod_id   last_log              size_mb
-------- --------------------- ---------------
0        2016-01-05 08:46:00   567.05288505
1        2015-12-31 12:53:00   1370.39249420
2        2015-12-18 11:32:00   768.46394729
3        2015-12-02 13:54:00   220.20050621
4        2015-12-02 13:16:00   24.04152870
5        2015-11-16 13:37:00   80.07946205
6        2015-10-22 12:13:00   109.33527946

Como de costumbre, en los servidores de prueba no me molesto Registro de errores tamaño porque cada comienzo con servidor SQL inicia el cambio cíclico de los archivos de registro: actual registro de errores renombrado a registro de errores.1archivo vacío registro de errores es creado y el primero registro de errores.6 es removido.

Si necesito limpiar los registros, sp_cycle_errorlog puede ser útil. Pero antes de limpiar los registros, se me ocurrió ver qué tomas interesantes se graban allí.

Leí el diario actual con un procedimiento guardado xp_readerrorlog:

EXEC sys.xp_readerrorlog

Y luego vi docenas de mensajes como este:

Starting up Base de datos '...'.

Por un lado, no hay nada de malo en eso. En cada comienzo, servidor SQL abre archivos de datos y comprueba la página de descarga:

Starting up Base de datos '...'.
CHECKDB for Base de datos '...' finished without errors on ... (local time).

Pero después de filtrar un mensaje interesante, los resultados despertaron interés:

DECLARE @t TABLE (log_date SMALLDATETIME, spid VARCHAR(50), msg NVARCHAR(4000))
INSERT INTO @t
EXEC sys.xp_readerrorlog 0, 1, N'Starting up Base de datos'

SELECT msg, COUNT_BIG(1)
FROM @t
GROUP BY msg
HAVING COUNT_BIG(1) > 1
ORDER BY 2 DESC
------------------------------------------------------ --------------------
Starting up Base de datos 'AUTOTEST_DESCRIBER'.             127723
Starting up Base de datos 'MANUAL_DESCRIBER'.               12913
Starting up Base de datos 'AdventureWorks2012'.             12901

Un gran número de tales mensajes puede ser el resultado AUTO CERRADO el parámetro se establece en ON.

Según la documentación en línea, si incluye AUTO CERRADO opción, la base de datos se cierra automáticamente y elimina todos los recursos después de que sale el último usuario. Cuando se solicite una nueva conexión, la base de datos se volverá a abrir automáticamente... y así indefinidamente.

Hace algún tiempo leí esto en versiones anteriores servidor SQL, AUTO CERRADO era un proceso síncrono que podía causar largas demoras al reabrir y cerrar los archivos de la base de datos. A partir de Servidor SQL 2005, AUTO CERRADO el proceso se volvió asincrónico y el problema desapareció parcialmente. Pero hay muchos problemas con AUTO CERRADO que aún quedaba.

Para optimizar el rendimiento, servidor SQL cambia las páginas en el caché del búfer y no escribe esas páginas en el disco después de cada cambio. En lugar de, servidor SQL crea un punto de control en el que escribe las páginas actuales modificadas en la memoria, junto con la información del registro de transacciones de la memoria al disco. Cuando la base de datos se cierra, Control ejecutado automáticamente. En consecuencia, la carga del disco puede aumentar significativamente cuando la base de datos se vuelve a cerrar.

Además, cada cierre de la base de datos borra la caché de procedimientos. Entonces, cuando la base de datos se vuelva a abrir, los planes de ejecución deberán generarse nuevamente. Pero lo que es peor, desactivar el caché del búfer también lo restablece, lo que aumenta la carga del disco cuando se ejecutan las consultas.

Que hace microsoft pensando en AUTO CERRADO?

“Si AUTO_CLOSE está habilitado, esta opción puede provocar una degradación del rendimiento en las bases de datos a las que se accede con frecuencia debido a una mayor sobrecarga al abrir y cerrar la base de datos después de cada conexión. AUTO_CLOSE también borra el caché de procedimientos después de cada conexión »

Sin embargo, hay un par de matices. У Servidor SQL 2000 o cualquier Rápido edición cuando crea un nuevo archivo de base de datos AUTO CERRADO La opción estará habilitada por defecto:

USE [master]
GO

IF DB_ID('test') IS NOT NULL
    DROP Base de datos [test]
GO

CREATE Base de datos [test]
GO

SELECT is_auto_close_on
FROM sys.databases
WHERE database_id = DB_ID('test')
is_auto_close_on
----------------
1

Sin embargo, si miras hacia un lado, entonces esto Servidor SQL Express el comportamiento es fácil de explicar porque esta versión establece un límite de tamaño RAM uso - máximo 1 GB.

Pero en el futuro, si necesita implementar una base de datos usando un script, es mejor estar seguro y deshabilitar explícitamente AUTO CERRADO:

ALTER Base de datos [test] SET AUTO_CLOSE OFF

Mientras trabajaba, noté otra cosa interesante: al llamar a ciertas funciones del sistema o al explorar todas las bases de datos con AUTO CERRADO se abrirán las opciones:

USE [master]
GO

IF DB_ID('p1') IS NOT NULL
    DROP Base de datos [p1]
GO
CREATE Base de datos [p1]
GO
ALTER Base de datos [p1] SET AUTO_CLOSE ON
GO

IF DB_ID('p2') IS NOT NULL
    DROP Base de datos [p2]
GO
CREATE Base de datos [p2]
GO
ALTER Base de datos [p2] SET AUTO_CLOSE ON
GO

EXEC sys.xp_readerrorlog 0, 1, N'Starting up Base de datos ''p'
GO
LogDate                 ProcessInfo  Text
----------------------- ------------ ----------------------------------
2016-01-25 17:36:40.310 spid53       Starting up Base de datos 'p1'.
2016-01-25 17:36:41.980 spid53       Starting up Base de datos 'p2'.

Llamar p1:

WAITFOR DELAY '00:03'
GO
SELECT DB_ID('p1')
GO
EXEC sys.xp_readerrorlog 0, 1, N'Starting up Base de datos ''p'

Pero p2 "Despierta" también:

LogDate                 ProcessInfo  Text
----------------------- ------------ ----------------------------------
2016-01-25 17:36:40.310 spid53       Starting up Base de datos 'p1'.
2016-01-25 17:36:41.980 spid53       Starting up Base de datos 'p2'.
2016-01-25 17:39:17.440 spid52       Starting up Base de datos 'p1'.
2016-01-25 17:39:17.550 spid52       Starting up Base de datos 'p2'.

Finalmente, llegamos a la parte donde hablamos del término medio. En el servidor, varios usuarios accedieron activamente a los metadatos creando bases de datos con ellos habilitados AUTO CERRADO abierta, lo que a su vez provocó la Registro de errores crecimiento.

Las medidas preventivas, por cierto, son muy simples:

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = (
    SELECT '
ALTER Base de datos ' + QUOTENAME(name) + ' SET AUTO_CLOSE OFF WITH NO_WAIT;'
FROM sys.databases
WHERE is_auto_close_on = 1
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

EXEC sys.sp_executesql @SQL

Todas las pruebas se implementaron en Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64).

Conclusión

Puede parecer lógico cerrar una base de datos que no se utiliza para liberar recursos y mejorar el rendimiento. Pero en realidad daña su base de datos mucho más de lo que ayuda. Por lo tanto, si no está seguro de que esta característica sea importante para usted, lo mejor es dejar AUTO CERRADO instalación APAGADO.

Artículos de interés

Subir