SQL Server instant file initialization
Основано на статьях: Sergey Syrovatchenko и Самородов Фёдор
Когда SQL Server резервирует новое место на диске, то он инициализирует его нулями. Данное поведение можно отключить и как следствие сократить время выполнения некоторых операций и снизить нагрузку на дисковую подсистему.
Возможность резервирования места на диске без инициализации называется Instant File Initialization (мгновенная инициализация файлов). Фича эта не сильно известна, хотя ее использование стало возможным начиная с SQL Server 2005.
Какие преимущества можно получить от Instant File Initialization:
1. Ускорить создание новой базы данных
2. Сократить задержки и уменьшить время необходимое для увеличения файлов данных
3. Сократить время старта SQL Server, поскольку инициализация tempdb будет более быстрой
4. Сократить время при восстановлении из резервной копии, поскольку перед восстановлением SQL Server резервирует место под файлы, а потом переносит в них информацию из бекапа.
Важно отметить, что Instant File Initialization работает только для файлов данных (MDF и NDF). Файлы лога (LDF) всегда инициализируются нулями.
Как использовать Instant File Initialization?
Все очень просто включается. Открываем SQL Server Configuration Manager и узнаем от какого имени запускается экземпляр нашегоSQL Server.
Далее запускаем в Local Security Policy (Локальная политика безопасности) (cmd > secpol.msc) ищем User Rights Assignment (Название прав пользователя) – Perform volume maintenance tasks (Выполнение задач по обслуживанию томов)
Добавляем нужную нам личность в настройки, как показано на картинке.
Права, которые необходимы для работы Instant File Initialization, экземпляр SQL Server проверяет только один раз – во время старта. Именно поэтому нужно перезапустить SQL Server, чтобы применились наши настройки.
Теперь будем проводить эксперименты…
Для начала давайте проверим… включен ли Instant File Initialization?
Если он выключен, то при выполнении запроса:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE [master] GO IF DB_ID('IFI_DB') IS NOT NULL DROP DATABASE IFI_DB GO DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS CREATE DATABASE IFI_DB DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS GO IF DB_ID('IFI_DB') IS NOT NULL DROP DATABASE IFI_DB GO EXEC sp_readerrorlog 0, 1 |
в журнале можно увидеть заполнение нулями файлов данных:
|
1 2 3 4 5 6 7 8 |
Zeroing D:\DATABASES\SQL_2012\DATA\IFI_DB.mdf from page 0 to 520 (0x0 to 0x410000) Zeroing completed on D:\DATABASES\SQL_2012\DATA\IFI_DB.mdf Zeroing D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf from page 0 to 130 (0x0 to 0x104000) Zeroing completed on D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf Starting up database 'IFI_DB'. FixupLogTail(progress) zeroing D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf from 0x5000 to 0x6000. Zeroing D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf from page 3 to 33 (0x6000 to 0x42000) Zeroing completed on D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf |
Но если Instant File Initialization включен, то нулями заполняется только файл лога:
|
1 2 3 4 5 6 |
Zeroing D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf from page 0 to 130 (0x0 to 0x104000) Zeroing completed on D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf Starting up database 'IFI_DB'. FixupLogTail(progress) zeroing D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf from 0x5000 to 0x6000. Zeroing D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf from page 3 to 33 (0x6000 to 0x42000) Zeroing completed on D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf |
Если лень смотреть в журнал, то можно воспользоваться следующим скриптом:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
USE [master] GO SET NOCOUNT ON; IF DB_ID('IFI_DB') IS NOT NULL DROP DATABASE IFI_DB GO IF OBJECT_ID('tempdb.dbo.#IFI') IS NOT NULL DROP TABLE #IFI GO CREATE TABLE #IFI (dt DATETIME, info VARCHAR(50), txt VARCHAR(MAX)) GO DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS CREATE DATABASE IFI_DB DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS GO IF DB_ID('IFI_DB') IS NOT NULL DROP DATABASE IFI_DB GO INSERT INTO #IFI EXEC sp_readerrorlog 0, 1, 'Zeroing' IF EXISTS( SELECT 1 FROM #IFI WHERE txt LIKE 'Zeroing completed%' AND txt LIKE '%IFI_DB.mdf%' AND dt > DATEADD(HOUR, -1, dt) ) PRINT 'Instant File Initialization = OFF' ELSE PRINT 'Instant File Initialization = ON' |
На случай, если нужно временно отключить Instant File Initialization, то можно включить флаг трассировки 1806. Но как показывается практика, возможность использовать эту функциональность сильно экономит время и сокращает дисковую нагрузку.
Вот пара тестовых примеров и время на их выполнение с Instant File Initialization и без:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
USE [master] GO IF DB_ID('IFI_DB') IS NOT NULL DROP DATABASE [IFI_DB] GO CREATE DATABASE [IFI_DB] CONTAINMENT = NONE ON PRIMARY (NAME = N'IFI_DB', FILENAME = N'D:\DATABASES\SQL_2012\DATA\IFI_DB.mdf', SIZE = 102400MB LOG ON (NAME = N'IFI_DB_log', FILENAME = N'D:\DATABASES\SQL_2012\LOG\IFI_DB_log.ldf', SIZE = 2048KB) GO -- OFF: 00:16:04 -- ON: 00:00:12 ALTER DATABASE [IFI_DB] MODIFY FILE (NAME = N'IFI_DB', SIZE = 204800MB) GO -- OFF: 00:14:32 -- ON: 00:00:11 BACKUP DATABASE [IFI_DB] TO DISK = N'D:\DATABASES\SQL_2012\BACKUP\IFI_DB.bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION GO IF DB_ID('IFI_DB') IS NOT NULL DROP DATABASE [IFI_DB] GO -- OFF: 00:00:59 -- ON: 00:00:58 USE [master] GO RESTORE DATABASE [IFI_DB] FROM DISK = N'D:\DATABASES\SQL_2012\BACKUP\IFI_DB.bak' WITH FILE = 1, NOUNLOAD -- OFF: 00:28:03 -- ON: 00:00:16 |
Более визуальный пример от Фёдора Самородова:
Посмотрите внимательно на выдачу инструкции RESTORE DATABASE:
Сама команда восстановления сообщает, что она выполнялась 3331 секунду — это примерно 55 минут. Но с точки зрения внешнего наблюдателя восстановление базы заняло больше двух часов! Получается, что 55 минут сервер занимался непосредственно восстановлением БД, а ещё час двадцать занимался неизвестно чем.
На самом деле это время сервер потратил на выделение места под восстанавливаемую базу. Сложно поверить, но это заняло в полтора раза больше времени, чем полезный процесс.
Повторим эксперимент с восстановлением базы после включения Instant File Initialization:
Теперь база действительно восстанавливается за 55 минут — это почти в два с половиной раза быстрее, чем при настройках по умолчанию!
Использование Instant File Initialization – потрясающий способ уменьшить время простоя при восстановлении после сбоя. Создание файлов не будет занимать долго времени для инициации их нулями до того, как начнётся сама операция восстановления. Так что возьмете на вооружение такую полезную возможность как Instant File Initialization.
Кстати, для SQL Server 2016 CTP3.0 можно включить Instant File Initialization еще на этапе установки:
*** *** *** *** *** *** *** *** *** *** *** ***
Полезные Скрипты
Рубрика Проверь себя
Ссылка на наш канал YouTube
