• Статья

Оптимизация производительности базы данных tempdb

Размер и физическое размещение базы данных tempdb может влиять на производительность системы. Например, если для базы данных tempdb установлен слишком малый размер, часть системной нагрузки может приходиться на автоувеличение базы данных tempdb до размера, требуемого для поддержки рабочей нагрузки при каждом перезапуске экземпляра SQL Server. Этих затрат можно избежать, увеличив размер базы данных tempdb и файла журнала. Сведения об определении подходящего размера места на диске для базы данных tempdb см. в разделе Планирование размера базы данных tempdb.

Рекомендации относительно размера и размещения tempdb

Для обеспечения оптимальной производительности базы данных tempdb рекомендуется следующая конфигурация базы данных tempdb в рабочей среде.

  • Установите модель восстановления базы данных tempdb в значение SIMPLE. Эта модель автоматически резервирует место под журнал, уменьшая требования к месту на диске.

    Дополнительные сведения см. в разделах ALTER DATABASE (Transact-SQL) и Как просмотреть или изменить модель восстановления базы данных (среда SQL Server Management Studio).

  • Включите автоматическое расширение файлов tempdb. Это разрешает файлу расти вплоть до заполнения диска.

    ПримечаниеПримечание

    Если рабочая среда не допускает возможных приостановок приложений, которые могут возникнуть при выполнении операций автоувеличения, предварительно выделите место на диске, чтобы обеспечить ожидаемую рабочую нагрузку.

  • Установите шаг увеличения размера файла на приемлемую величину, чтобы избежать слишком малого увеличения размера файлов базы данных tempdb. Если увеличение размера файла будет идти слишком медленно по сравнению с объемом записываемых в базу tempdb данных, база данных tempdb может требовать постоянного расширения. Это повлияет на производительность. Рекомендуются следующие значения для установки шага увеличения FILEGROWTH для файлов tempdb.

    Размер файла tempdb

    Шаг роста FILEGROWTH

    от 0 до 100 МБ

    10 МБ

    от 100 до 200 МБ

    20 МБ

    200 МБ или больше

    10%*

    * Может потребоваться установить процент с учетом быстродействия подсистемы ввода-вывода, на которой расположены файлы tempdb. Чтобы избежать тайм-аутов возможных кратковременных блокировок, рекомендуется ограничить продолжительность операции автоувеличения приблизительно двумя минутами. Например, если подсистема ввода-вывода может заполнять файл со скоростью 50 МБ в секунду, то шаг роста FILEGROWTH следует установить в значение не более 6 ГБ, вне зависимости от размера файла tempdb. Если возможно, используйте мгновенную инициализацию файла базы данных, чтобы улучшить производительность операций автоувеличения.

  • Заранее выделите место для всех файлов tempdb, установив размер файла в значение, достаточное, чтобы гарантировать обычную рабочую нагрузку в среде. Это предотвращает слишком частое расширение tempdb, которое может повлиять на производительность. Следует установить автоувеличение для базы данных tempdb, но это следует сделать, чтобы увеличить место на диске для незапланированных исключений.

  • Создайте столько файлов, сколько требуется, чтобы максимально увеличить пропускную способность диска. Использование нескольких файлов сокращает конфликты хранилищ базы данных tempdb и обеспечивает гораздо лучшую масштабируемость. Однако не создавайте слишком много файлов, так как это может привести к уменьшению производительности и увеличить издержки управления. Общая рекомендация состоит в том, чтобы создать один файл данных для каждого ЦП на сервере (для каждого параметра маска схожести), а затем установить число файлов, при необходимости уменьшив его или увеличив. Имейте в виду, что двухъядерный ЦП рассматривается как два процессора.

  • Сделайте файлы одинакового размера, это обеспечивает оптимальную производительность с пропорциональным заполнением.

  • Поместите базу данных tempdb на быструю подсистему ввода-вывода. Если имеется много непосредственно присоединенных дисков, то используйте чередование дисков.

  • Расположите базу данных tempdb на дисках, отличных от используемых пользовательскими базами данных.

Изменение параметров размера и расширения базы данных tempdb

Параметры размера и увеличения размера файла базы данных tempdb или файлов журнала можно изменить с помощью одного из следующих методов:

Значения параметров размера файла и шага роста файла применяются всегда при создании базы tempdb. Например, если размер файла данных tempdb увеличить до 20 МБ, а шаг увеличения размера файла — до 15 процентов, новые значение немедленно вступят в силу. Если последующие действия транзакций приводят к увеличению размера tempdb, размер файла данных будет сбрасываться до 20 МБ каждый раз при перезапуске экземпляра SQL Server.

Просмотр параметров размера и расширения базы данных tempdb

Параметры размера и увеличения размера файла данных базы tempdb или файлов журнала можно просмотреть с помощью одного из следующих методов.

  • Среда SQL Server Management Studio.

  • Выполнение следующего запроса.

    SELECT 
    name AS FileName, 
    size*1.0/128 AS FileSizeinMB,
    CASE max_size 
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file will grow to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' = 
        CASE
            WHEN growth = 0 THEN 'Size is fixed and will not grow.'
            WHEN growth > 0 AND is_percent_growth = 0 
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

Обнаружение ошибок в обозначениях путей при выполнении операций ввода-вывода на диске

Если параметру PAGE_VERIFY присвоено значение CHECKSUM, то происходит обнаружение страниц базы данных, поврежденных из-за ошибок в обозначениях путей при выполнении операций ввода-вывода на диске, и информация об этих ошибках, таких как MSSQLSERVER_823, MSSQLSERVER_824 или MSSQLSERVER_825, записывается в журнал регистрации ошибок SQL. Ошибки пути дискового ввода-вывода могут быть причиной повреждения базы данных и обычно происходят при сбоях питания или сбоях оборудования диска, которые возникают во время записи страницы на диск. Дополнительные сведения об ошибках ввода-вывода см. в главе 2 раздела Основные операции ввода-вывода в SQL Serveг.

В более ранних версиях SQL Server параметру базы данных PAGE_VERIFY присваивается значение NONE применительно к базе данных tempdb, которая не может быть изменена. В SQL Server 2008 значением по умолчанию для базы данных tempdb является CHECKSUM для новых установок SQL Server. После обновления установки SQL Server значением по умолчанию остается NONE. Рекомендуется задать значение параметра PAGE_VERIFY для базы данных tempdb, равное CHECKSUM.