January 25, 2016 at 19:55AUTO_CLOSE
- Tutorial
If SQL Server existed during the Inquisition, then for the inclusion of some options on production servers it would be necessary to punish with a hot iron. But if we discard the lyrics, then we’ll further examine why AUTO_CLOSE does not need to be enabled and what the use of this option can lead to.
Actually, like any interesting life story, it all started with a routine of the task.
The other day I had to look at Error Log on a test server. In the second minute of waiting, SSMS pretty much went awry from the abundance of messages that the log kept, and I decided to see how much the logs were weighing using xp_enumerrorlogs :
As a rule, on the test server, I try not to deal with the size of the magazine, because at each start SQL Server yet it cycles the log files: the current log errorlog is renamed errorlog.1 , create an empty file errorlog and removes the oldest log errorlog.6 .
In the event that it is necessary to clear the logs, sp_cycle_errorlog may come to the rescue . But before clearing the magazines, I still wanted to understand what was written in them of such interesting things.
Read from the current log using the xp_readerrorlog stored procedure :
And then my peripheral vision caught the eye of numerous messages:
On the one hand, there is nothing wrong with that. At each start, SQL Server opens the data files and checks the boot page:
But after I filtered by the search message, the results of the selection made me think:
A large number of these types of messages may occur due to the inclusion of the AUTO_CLOSE option .
According to the documentation, when the AUTO_CLOSE option is enabled, the database will automatically close and release all the resources it occupies when the last user connection using this database ceases to exist. Upon repeated appeal, the base will automatically reopen ... and so on ad infinitum.
Once upon a time I read that at the physical level, the AUTO_CLOSE operation for older versions of SQL Server was a completely synchronous process, which could cause long delays when the database files were repeatedly opened. Starting with SQL Server 2005 , AUTO_CLOSEbecame asynchronous and some of the problems went away. What is left? Enough to not use this option ...
To optimize performance, SQL Server performs page changes in the buffer cache and does not write these pages to disk after each modification. Instead, SQL Server periodically creates a checkpoint at which it writes the current pages that have been modified in memory, along with transaction log information from memory to disk. When the database is closed, CHECKPOINT is automatically executed . Accordingly, with constant database closures, disk load can increase significantly.
Also, each time the database is closed, its procedural cache is cleared. Accordingly, when the database is reopened, you will have to generate execution plans for a new one. But what is even sadder ... when closed, the buffer cache is also cleared, which increases the disk load when executing queries.
Microsoft is in agreement with me and also does not recommend including AUTO_CLOSE :
When AUTO_CLOSE is set ON, this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection.
However, there are a couple of nuances. If you are using SQL Server 2000 or anyExpress edition, then when creating a new database, the AUTO_CLOSE option will be implicitly enabled:
Bravo Microsoft ! A standing ovation ...
Although if you look from the other side, for SQL Server Express this behavior is understandable, since within this edition there is a limit on the size of RAM used - no more than 1 GB.
But for the future, if you suddenly need to deploy the database using a script, it is better to play it safe and explicitly disable AUTO_CLOSE :
In the process, I noticed another interesting point - when accessing some functions or system views, all databases with the AUTO_CLOSE option turned on will open:
We turn to p1 :
But p2 also wakes up "for the company":
And finally, we got to the bottom of the truth. On the server, various users actively accessed metadata ... this made the database wake up with AUTO_CLOSE enabled , which in turn implicitly caused the log to grow.
Preventive measures, by the way, came out very simple:
Everything was tested on Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64).
If you want to share this article with an English audience:
Enabling AUTO_CLOSE is a bad idea?
Actually, like any interesting life story, it all started with a routine of the task.
The other day I had to look at Error Log on a test server. In the second minute of waiting, SSMS pretty much went awry from the abundance of messages that the log kept, and I decided to see how much the logs were weighing using 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
As a rule, on the test server, I try not to deal with the size of the magazine, because at each start SQL Server yet it cycles the log files: the current log errorlog is renamed errorlog.1 , create an empty file errorlog and removes the oldest log errorlog.6 .
In the event that it is necessary to clear the logs, sp_cycle_errorlog may come to the rescue . But before clearing the magazines, I still wanted to understand what was written in them of such interesting things.
Read from the current log using the xp_readerrorlog stored procedure :
EXEC sys.xp_readerrorlog
And then my peripheral vision caught the eye of numerous messages:
Starting up database '...'.
On the one hand, there is nothing wrong with that. At each start, SQL Server opens the data files and checks the boot page:
Starting up database '...'.
CHECKDB for database '...' finished without errors on ... (local time).
But after I filtered by the search message, the results of the selection made me think:
DECLARE @t TABLE (log_date SMALLDATETIME, spid VARCHAR(50), msg NVARCHAR(4000))
INSERT INTO @t
EXEC sys.xp_readerrorlog 0, 1, N'Starting up database'
SELECT msg, COUNT_BIG(1)
FROM @t
GROUP BY msg
HAVING COUNT_BIG(1) > 1
ORDER BY 2 DESC
------------------------------------------------------ --------------------
Starting up database 'AUTOTEST_DESCRIBER'. 127723
Starting up database 'MANUAL_DESCRIBER'. 12913
Starting up database 'AdventureWorks2012'. 12901
A large number of these types of messages may occur due to the inclusion of the AUTO_CLOSE option .
According to the documentation, when the AUTO_CLOSE option is enabled, the database will automatically close and release all the resources it occupies when the last user connection using this database ceases to exist. Upon repeated appeal, the base will automatically reopen ... and so on ad infinitum.
Once upon a time I read that at the physical level, the AUTO_CLOSE operation for older versions of SQL Server was a completely synchronous process, which could cause long delays when the database files were repeatedly opened. Starting with SQL Server 2005 , AUTO_CLOSEbecame asynchronous and some of the problems went away. What is left? Enough to not use this option ...
To optimize performance, SQL Server performs page changes in the buffer cache and does not write these pages to disk after each modification. Instead, SQL Server periodically creates a checkpoint at which it writes the current pages that have been modified in memory, along with transaction log information from memory to disk. When the database is closed, CHECKPOINT is automatically executed . Accordingly, with constant database closures, disk load can increase significantly.
Also, each time the database is closed, its procedural cache is cleared. Accordingly, when the database is reopened, you will have to generate execution plans for a new one. But what is even sadder ... when closed, the buffer cache is also cleared, which increases the disk load when executing queries.
Microsoft is in agreement with me and also does not recommend including AUTO_CLOSE :
When AUTO_CLOSE is set ON, this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection.
However, there are a couple of nuances. If you are using SQL Server 2000 or anyExpress edition, then when creating a new database, the AUTO_CLOSE option will be implicitly enabled:
USE [master]
GO
IF DB_ID('test') IS NOT NULL
DROP DATABASE [test]
GO
CREATE DATABASE [test]
GO
SELECT is_auto_close_on
FROM sys.databases
WHERE database_id = DB_ID('test')
is_auto_close_on
----------------
1
Bravo Microsoft ! A standing ovation ...
Although if you look from the other side, for SQL Server Express this behavior is understandable, since within this edition there is a limit on the size of RAM used - no more than 1 GB.
But for the future, if you suddenly need to deploy the database using a script, it is better to play it safe and explicitly disable AUTO_CLOSE :
ALTER DATABASE [test] SET AUTO_CLOSE OFF
In the process, I noticed another interesting point - when accessing some functions or system views, all databases with the AUTO_CLOSE option turned on will open:
USE [master]
GO
IF DB_ID('p1') IS NOT NULL
DROP DATABASE [p1]
GO
CREATE DATABASE [p1]
GO
ALTER DATABASE [p1] SET AUTO_CLOSE ON
GO
IF DB_ID('p2') IS NOT NULL
DROP DATABASE [p2]
GO
CREATE DATABASE [p2]
GO
ALTER DATABASE [p2] SET AUTO_CLOSE ON
GO
EXEC sys.xp_readerrorlog 0, 1, N'Starting up database ''p'
GO
LogDate ProcessInfo Text
----------------------- ------------ ----------------------------------
2016-01-25 17:36:40.310 spid53 Starting up database 'p1'.
2016-01-25 17:36:41.980 spid53 Starting up database 'p2'.
We turn to p1 :
WAITFOR DELAY '00:03'
GO
SELECT DB_ID('p1')
GO
EXEC sys.xp_readerrorlog 0, 1, N'Starting up database ''p'
But p2 also wakes up "for the company":
LogDate ProcessInfo Text
----------------------- ------------ ----------------------------------
2016-01-25 17:36:40.310 spid53 Starting up database 'p1'.
2016-01-25 17:36:41.980 spid53 Starting up database 'p2'.
2016-01-25 17:39:17.440 spid52 Starting up database 'p1'.
2016-01-25 17:39:17.550 spid52 Starting up database 'p2'.
And finally, we got to the bottom of the truth. On the server, various users actively accessed metadata ... this made the database wake up with AUTO_CLOSE enabled , which in turn implicitly caused the log to grow.
Preventive measures, by the way, came out very simple:
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = (
SELECT '
ALTER DATABASE ' + 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
Everything was tested on Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64).
If you want to share this article with an English audience:
Enabling AUTO_CLOSE is a bad idea?