AUTO_CLOSE і покарання розпеченим залізом

Якщо б SQL Server існував у часи Інквізиції, то за включення деяких опцій продакшен на серверах потрібно було б карати розпеченим залізом. Але якщо відкинути лірику, то далі розглянемо на чому не потрібно включати AUTO_CLOSE і до чого може призвести використання цієї опції.

Власне, як і будь-яка цікава історії з життя, все починалося з рутиною завдання.

Днями довелося зазирнути в Error Log на тестовому сервері. На другій хвилині очікування, SSMS неабияк стало погано від великої кількості повідомлень, які зберігав журнал, і я вирішив подивитися скільки ж важать логи з допомогою xp_enumerrorlogs:

DECLARE @t TABLE (lod_id INT PRIMARY KEY, last_log SMALLDATETIME, INT size)
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

Як правило, на тестових серверах я намагаюся не боротися з розміром журналу, оскільки при кожному старті SQL Server-а відбувається циклічна зміна файлів журналу: поточний журнал errorlog перейменовується в errorlog.1, створюється порожній файл errorlog і видаляється найстаріший журнал errorlog.6.

У разі, коли виникає необхідно очистити журнали, то на допомогу може прийти sp_cycle_errorlog. Але перед тим як очистити журнали, все ж таки захотілося зрозуміти, що в них такого цікавого записано.

Виконав читання з поточного журналу з допомогою збереженої процедури xp_readerrorlog:

EXEC sys.xp_readerrorlog

І тут мою периферійного зору попалися на очі численні повідомлення:

Starting up database '...'.

З одного боку, в цьому немає нічого поганого. При кожному старті SQL Server відкриває файли даних і перевіряє завантажувальну сторінку:

Starting up database '...'.
CHECKDB for database '...' out without errors on ... (local time).

Але після того як я відфільтрував за шуканим повідомленням, результати вибірки змусили замислитися:

DECLARE @t TABLE (log_date SMALLDATETIME, вільний VARCHAR(50), msg NVARCHAR(4000))
INSERT INTO @t
EXEC sys.xp_readerrorlog 0, 1, '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

Велика кількість такого роду повідомлень може виникати з-за включення опції AUTO_CLOSE.

Згідно документації, при включенні опції AUTO_CLOSE база даних буде автоматично закриватися і вивільняти всі займані нею ресурси, коли перестає існувати останнім користувальницьке з'єднання використовує цю БД. При повторному зверненні база буде автоматично переоткрываться… і так до нескінченності.

Колись давно читав, що на фізичному рівні операція AUTO_CLOSE для старих версій SQL Server була повністю синхронним процесом, який міг викликати великі затримки при постійних переоткрытиях файлів БД. Починаючи з SQL Server 2005, AUTO_CLOSE став асинхронним і частина проблем пішла. А що залишилося? Достатньо лише, щоб не користуватися цією опцією…

Для оптимізації продуктивності SQL Server виконує зміни сторінок в буферному кеші і не записує ці сторінки на диск після кожної модифікації. Замість цього SQL Server періодично створює контрольну точку, з якої записує поточні сторінки, які було змінено в пам'яті, разом з відомостями журналу транзакцій з пам'яті на диск. При закритті бази даних автоматично виконується CHECKPOINT. Відповідно при постійних закрытиях БД може сильно зростати дискова навантаження.

Також при кожному закриття бази даних очищається її процедурний кеш. Відповідно, коли база повторно відкриється доведеться генерувати виконання планів по новій. Але що ще сумніше… при закритті очищається також і буферний кеш, що збільшує дискову навантаження при виконанні запитів.

Microsoft зі мною солідарна і також не рекомендує включати 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.

Проте є пара нюансів. Якщо Ви використовуєте SQL Server 2000 або будь-яку Express редакцію, то при створенні нової бази даних, опція AUTO_CLOSE буде неявно включатися:

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

Браво Microsoft! Стоячі овації…

Хоча якщо подивитися з іншого боку, для SQL Server Express така поведінка цілком зрозуміло, оскільки в рамках цієї редакції існує обмеження на розмір використовуваної ОПЕРАТИВНОЇ пам'яті – не більше 1 Гб.

Але на майбутнє, якщо раптом буде потрібно розгортати базу даних за допомогою скрипта, краще перестрахуватися і явно відключати AUTO_CLOSE:

ALTER DATABASE [test] SET OFF AUTO_CLOSE

В процесі роботи я помітив ще один цікавий момент – при зверненні до деяких функцій або системним уявленням, всі бази даних з включеною опцією AUTO_CLOSE відкриватимуться:

USE [master]
GO

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

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

EXEC sys.xp_readerrorlog 0, 1, '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'.

Ми звертаємося до p1:

WAITFOR DELAY '00:03'
GO
SELECT DB_ID('p1')
GO
EXEC sys.xp_readerrorlog 0, 1, 'Starting up database "p'

Але p2 теж прокидається «за компанію»:

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'.

І ось нарешті ми докопалися до істини. На сервері різні користувачі активно зверталися до метаданих… це змушувало прокидатися бази даних з включеним AUTO_CLOSE, що в свою чергу неявно викликало зростання журналу.

Превентивні заходи, до речі, вийшли дуже простими:

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

Всі тестувалося на Microsoft SQL Server 2012 (SP3) (KB3072779) — 11.0.6020.0 (X64).

Власне, на цьому все. Всім дякую за увагу.

Тепер дозвольте знову пару слів сказати з іншої теми...

В кінці цього місяця, відбудеться невелика зустріч юзер-групи SQL Server. В рамках неї планується 2 доповіді. Один буде від мене в рамках якого я торкнуся питання продуктивності при роботі з XML XQuery. Другий доповідь про системи контролю версій баз даних.

Місце проведення: Україна, м. Харків, вул. Маломясницкая, 9/11 (ст. метро пр. Гагаріна) на базі академії «КРОК»
Час початку: 29 січня 2016 о 18:00

Участь повністю безкоштовне.
Додаткова інформація тут.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.