Instant File Initialization

  • Tutorial
Когда 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 (Локальная политика безопасности) ищем User Rights Assignment (Название прав пользователя) – Perform volume maintenance tasks (Выполнение задач по обслуживанию томов)



Добавляем нужную нам личность в настройки, как показано на картинке.



Права, которые необходимы для работы Instant File Initialization, экземпляр SQL Server проверяет только один раз – во время старта. Именно поэтому нужно перезапустить SQL Server, чтобы применились наши настройки.

Теперь будем проводить эксперименты…

Для начала давайте проверим… включен ли Instant File Initialization?

Если он выключен, то при выполнении запроса:

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

в журнале можно увидеть заполнение нулями файлов данных:

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 включен, то нулями заполняется только файл лога:

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

Если лень смотреть в журнал, то можно воспользоваться следующим скриптом:

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 и без:

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

Если в целом, использование Instant File Initialization – потрясающий способ уменьшить время простоя при восстановлении после сбоя. Создание файлов не будет занимать долго времени для инициации их нулями до того, как начнётся сама операция восстановления. Так что возьмете на вооружение… что есть в мире такая фишка как Instant File Initialization.

Кстати, для SQL Server 2016 CTP3.0 можно включить Instant File Initialization еще на этапе установки:



Если хотите поделиться этой статьей с англоязычной аудиторией:
Instant File Initialization – Killer Feature for SQL Server
Поделиться публикацией
Похожие публикации
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама
Комментарии 12
  • +4
    Если фича такая прекрасная, то почему она не включена «из коробки»? Наверное, есть какие-то минусы от ее использования — о них в статье умолчали?
    • 0
      Судя по материалам из быстрого поиска — только соображения безопасности. Возможность прочитать «сырые» «стёртые» данные от других баз?
      • +1
        Дык это проблема процедуры удаления предыдущих данных, при чем здесь создание новой базы? Ведь новую мог никто и не создавать, а просто пойти почитать байты на диске.
        • +2
          Для того, чтобы пойти и почитать любые байты на диске (в том числе и удаленные файлы старых бэкапов таких директорий, как %SystemRoot%\System32\config, ну или ещё какие чувствительные данные), MSSQL как раз и требуется привилегия «Perform volume maintenance tasks». Я бы с осторожностью применял это, т.к. любая потенциальная уязвимость в MSSQL позволяющая удаленное исполнение кода (даже из-под пользователя с правами NT SERVICE\MSSQL$SQL_2012) может привести к утечке данных, к которым, по идее, доступа у NT SERVICE\MSSQL$SQL_2012 быть не должно.

          Команда, писавшая MSSQL, могла бы использовать FSCTL_SET_SPARSE + FSCTL_SET_ZERO_DATA + FSCTL_QUERY_ALLOCATED_RANGES + SetFilePointerEx() + SetEndOfFile() для того, чтобы переложить на ОС управление быстрым созданием больших файлов и виртуальным заполнением этих файлов нулями без лишней фрагментации. Почему они не стали это делать, а пошли другим путём (через лишние привилегии), непонятно.
          • 0
            Очень подробный ответ. Спасибо.

            Теперь по поводу осторожности… Если есть возможность ускорить дисковые операции, то почему не воспользоваться? Вопросы безопасности стоят не во всех организациях. Некоторым подавай максимальную производительность. А бывают ситуации, когда сервер ушел в мир иной и нужно в кратчайшие сроки восстановить базу из бекапа на другом железе. Там применение Instant File Initialization может сократить простой организации и нервы окружающих.
            • 0
              Еще забыл добавить… При включении Transparent Data Encryption (позволяет шифровать файлы на диске), Instant File Initialization работать не будет.
      • +1
        Instant File Initialization я включал для всех серверов, на которых работал и за два года не увидел никаких проблем при ее использовании. Кто столкнулся с проблемами – напишите в комментариях. Буду очень благодарен.
        • 0
          Потому что права системы — настройка windows, а не SQL Server. А вот давать всем подряд права Perform volume maintenance tasks действительно не стоит из соображений безопасности.
        • –10
          Единственная киллер-фича MS — это откаты. Других причин выделять на них бюджеты нет. Тем более — в современном мире.
          • +3
            Это ты с Oracle перепутал… MS SQL так дешев, что там откатывать нечего.
          • 0
            Некоторые системы хранения позволяют детектить «забите нулями» и дедуюлицировать их на ходу, сильно снижая нагрузку на свою дисковую подсистему.
            • 0
              Добавляю еще один способ: «Как проверить включен ли Instant File Initialization?»:

              IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
                  DROP TABLE #temp
              GO
              
              CREATE TABLE #temp (Value VARCHAR(8000))
              GO
              
              IF EXISTS (
                      SELECT 1
                      FROM sys.configurations
                      WHERE name = 'xp_cmdshell'
                          AND value_in_use = 1
                          AND IS_SRVROLEMEMBER('sysadmin') = 1
                  )
              BEGIN
              
                  INSERT INTO #temp
                  EXEC('xp_cmdshell ''whoami /priv''')
              
                  SELECT IsEnabled =
                      CASE WHEN Value LIKE '%Enabled%' COLLATE SQL_Latin1_General_CP1_CI_AS
                          THEN 1
                          ELSE 0
                      END
                  FROM #temp
                  WHERE Value LIKE '%SeManageVolumePrivilege%'
              
              END
              

              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.