Планирование размера базы данных tempdb
В этом разделе показано, как определить необходимое место на диске для размещения базы данных tempdb. Также включены рекомендации по настройке базы данных tempdb для обеспечения оптимальной производительности в рабочей среде и сведения по мониторингу использования места базой данных tempdb.
Использование базы данных tempdb
Системная база данных tempdb является глобальным ресурсом, доступным всем пользователям, которые подключены к экземпляру SQL Server. База данных tempdb служит для хранения следующих объектов: пользовательских объектов, внутренних объектов и хранилищ версий.
Пользовательские объекты
Пользовательские объекты явно создаются пользователями. Эти объекты могут быть ограничены областью действия либо пользовательского сеанса, либо подпрограммы, в которой они созданы. Подпрограмма — это хранимая процедура, триггер или определяемая пользователем функция. Пользовательскими объектами могут быть следующие элементы:
пользовательские таблицы и индексы
системные таблицы и индексы
глобальные временные таблицы и индексы
локальные временные таблицы и индексы
табличные переменные
таблицы, возвращаемые возвращающими табличное значение функциями
Внутренние объекты
Внутренние объекты создаются компонентом SQL Server Database Engine при необходимости для обработки инструкций SQL Server. Они создаются и удаляются в области действия инструкции. Внутренними объектами могут быть следующие элементы:
рабочие таблицы для хранения операций с курсором, операций подкачки и временных больших объектов (LOB);
рабочие файлы для операций хэш-соединения или статистических хэш-выражений;
промежуточные результаты сортировки при таких операциях, как создание или перестроение индексов (если указан параметр SORT_IN_TEMPDB), либо определенных запросах GROUP BY, ORDER BY или UNION.
Каждый внутренний объект использует минимум девять страниц: одну IAM-страницу и один восьмистраничный экстент. Дополнительные сведения о страницах и экстентах см. в разделе Страницы и экстенты.
Хранилища версий
Хранилище версий — это коллекция страниц данных, содержащих строки данных, которые необходимы для поддержки возможностей, применяющих управление версиями строк. Существует два хранилища версий: общее хранилище версий и хранилище версий оперативного построения индексов. Хранилища версий содержат следующее:
версии строк, сформированных транзакциями изменения данных в базе данных, которая использует уровни изоляции моментальных снимков или зафиксированного чтения при использовании управления версиями строк;
версии строк, сформированных транзакциями изменения данных для следующих операций: фоновых операций с индексами, множественных активных результирующих наборов (режим MARS) и триггеров AFTER.
В следующей таблице приведены возможности SQL Server, которые создают пользовательские объекты, внутренние объекты и версии строк в базе данных tempdb. Если возможно, предоставлены способы оценки использования места на диске.
Возможность |
Использование базы данных tempdb |
Дополнительные сведения |
|---|---|---|
Операции массовой загрузки с триггерами включены |
Оптимизация массового импорта доступна, если включены триггеры. SQL Server использует управление версиями строк для триггеров, которые обновляют или удаляют транзакции. Копия каждой удаленной или обновленной строки добавляется в хранилище версий. См. раздел «Триггеры» далее в этой таблице. |
|
Запросы обобщенных табличных выражений |
Обобщенные табличные выражения могут рассматриваться как временные результирующие наборы, определенные в области выполнения одиночных инструкций SELECT, INSERT, UPDATE, DELETE и CREATE VIEW. Когда в плане запроса для обобщенного табличного выражения для сохранения промежуточных результатов используется оператор буферизации, компонент Database Engine создает рабочую таблицу в базе данных tempdb для поддержки этой операции. |
|
Курсоры |
Управляемые набором ключей и статические курсоры используют рабочие таблицы, встроенные в базу данных tempdb. Управляемые набором ключей курсоры используют рабочую таблицу для хранения набора ключей, который идентифицирует строки в курсоре. Статические курсоры используют рабочую таблицу для хранения полного результирующего набора курсора. Использование места на диске курсорами может изменяться в зависимости от выбранного плана запроса. Если план запроса такой, как в предыдущих версиях SQL Server, выделяется примерно столько же места на диске. |
|
Компонент Database Mail |
См. раздел «Компонент Service Broker» далее в этой таблице. |
|
DBCC CHECKDB |
Команда DBCC CHECKDB использует рабочие таблицы базы данных tempdb для хранения промежуточных результатов и сортировки. Чтобы определить место на диске, необходимое базе данных tempdb для этой операции, выполните команду DBCC CHECKDB WITH ESTIMATEONLY. |
|
Уведомления о событиях |
См. раздел «Компонент Service Broker» далее в этой таблице. |
|
Индексы |
При создании или перестроении (в автономном или оперативном режиме) индекса можно присвоить параметру SORT_IN_TEMPDB значение ON, чтобы указать компоненту Database Engine базу данных tempdb для хранения промежуточных результатов сортировки, которые используются при построении индекса. Если указан параметр SORT_IN_TEMPDB и необходимо выполнить сортировку, база данных tempdb должна иметь достаточно места на диске для хранения наибольшего индекса, к которому следует добавить место, равное значению параметра index create memory. Дополнительные сведения см. в разделе Пример места на диске для индекса. Возможно секционирование таблиц и индексов. Для секционированных индексов, если параметр индекса SORT_IN_TEMPDB указан и индекс выровнен относительно базовой таблицы, базе данных tempdb должно быть выделено достаточно места для хранения промежуточных запусков сортировки наибольшей секции. Если индекс не выровнен, в базе данных tempdb должно быть достаточно места для хранения промежуточных запусков сортировки всех секций. Дополнительные сведения см. в разделе Дополнительные рекомендации по секционированным индексам. Для фоновых операций с индексами применяется управление версиями строк, позволяющее изолировать действия с индексами от воздействия изменений, внесенных другими транзакциями. Управление версиями строк позволяет не запрашивать общую блокировку уже считанных строк. Одновременное обновление и удаление, выполняемое пользователями во время фоновых операций с индексами, требует для записей версий места в базе данных tempdb. Если в фоновых операциях с индексами используется параметр SORT_IN_TEMPDB и необходима сортировка, база данных tempdb должна иметь дополнительное место на диске для хранения промежуточных результатов сортировки. Фоновым операциям с индексами, создающим, удаляющим или перестраивающим кластеризованный индекс, также необходимо дополнительное место на диске для построения и обслуживания временного индекса сопоставления. Дополнительные сведения см. в разделе Требования к месту на диске для DDL-операций индекса. |
База данных tempdb и создание индекса Дополнительные рекомендации по секционированным индексам Требования к месту на диске для DDL-операций индекса |
Переменные и параметры типа данных больших объектов (LOB) |
Типы данных больших объектов: varchar(max), nvarchar(max), varbinary(max)text, ntext, image и xml. Эти типы могут иметь размер до 2 ГБ и их можно использовать в качестве переменных или параметров в хранимых процедурах, определяемых пользователями функциях, пакетах и запросах. Параметры и переменные, определенные как тип данных LOB, используют для хранения основную память, если значения небольшие. Однако крупные значения хранятся в базе данных tempdb. При хранении в базе данных tempdb переменные и параметры LOB рассматриваются как внутренние объекты. Чтобы получить сведения о страницах, выделенных внутренним объектам в текущем сеансе, можно выполнить запрос к динамическому административному представлению sys.dm_db_session_space_usage. Некоторые встроенные строковые функции, такие как SUBSTRING или REPLICATE, могут требовать промежуточного временного хранения в базе данных tempdb при работе со значениями типа LOB. Кроме того, если в базе данных активирован уровень изоляции транзакций на основе версий строк и в крупных объектах сделаны изменения, измененный фрагмент типа LOB копируется в хранилище версий базы данных tempdb. |
|
Режим MARS |
На одном соединении может быть несколько активных результирующих наборов результатов (так называемый режим MARS). Если во время сеанса MARS выполняется инструкция изменения данных (например INSERT, UPDATE или DELETE) в момент, когда есть активный результирующий набор, строки, которых коснулось изменение, сохраняются в хранилище версий базы данных tempdb. См. раздел «Управление версиями строк» далее в этой таблице. |
|
Уведомления запросов |
См. раздел «Компонент Service Broker» далее в этой таблице. |
|
Запросы |
Запросы, содержащие инструкции SELECT, INSERT, UPDATE и DELETE, могут использовать внутренние объекты для хранения промежуточных результатов операций хэш-соединений, статистических хэш-выражений или сортировки. Когда кэшируется план выполнения запроса, кэшируются и необходимые рабочие таблицы. Если кэшируется рабочая таблица, она усекается и в кэше остается девять страниц для повторного использования. Это повышает производительность следующего выполнения запроса. При нехватке памяти компонент Database Engine может удалить план выполнения и связанные рабочие таблицы. |
|
Управление версиями строк |
Управление версиями строк — это стандартная структура, используемая для поддержки следующих функций:
Версии строк хранятся в хранилище версий базы данных tempdb в течение времени, когда активная транзакция должна обращаться к ним. Содержимое текущего хранилища версий возвращается представлением sys.dm_tran_version_store. Страницы хранилища версий отслеживаются на файловом уровне, поскольку они являются глобальными ресурсами. Для просмотра текущего размера хранилища версий можно использовать столбец version_store_reserved_page_count из представления sys.dm_db_file_space_usage. При очистке хранилища версий необходимо рассмотреть наиболее долго выполняющуюся транзакцию, которой нужен доступ к определенной версии. Наиболее долго выполняющаяся транзакция, связанная с очисткой хранилища версий, может быть найдена путем просмотра столбца elapsed_time_seconds в представлении sys.dm_tran_active_snapshot_database_transactions. Счетчики Свободное место в базе данных Tempdb (КБ) и Размер хранилища версий (КБ) объекта Транзакции можно использовать для отслеживания размера и темпов роста хранилища версий строк в базе данных tempdb. Дополнительные сведения см. в разделе SQL Server, объект Transactions. Чтобы оценить необходимое место на диске для базы данных tempdb для управления версиями строк, прежде всего, необходимо учесть, что активная транзакция должна хранить все изменения в хранилище версий. Это означает, что транзакция моментальных снимков, которая запущена позднее, может обращаться к старым версиям. Помимо этого, если выполняется активная транзакция моментальных снимков, все данные хранилища версий, создаваемые транзакциями, которые активны на момент запуска моментального снимка транзакциями, также должны быть сохранены. Основная формула для расчетов: [Размер хранилища версий] = 2 * [Данные хранилища версий, создаваемые за одну минуту] * [Наибольшее время выполнения транзакции (в минутах)] |
Основные сведения об уровнях изоляции на основе управления версиями строк |
Service Broker |
Компонент Service Broker помогает создавать асинхронные слабосвязанные приложения, в которых независимые компоненты совместно выполняют ту или иную задачу. Эти компоненты приложений обмениваются сообщениями, которые содержат сведения, необходимые для выполнения задачи. Компонент Service Broker явно использует базу данных tempdb для сохранения текущего диалогового контекста, который не может содержаться в памяти. Необходимый размер — примерно 1 КБ на диалог. Кроме того, компонент Service Broker неявно использует базу данных tempdb при фиксации объектов в контексте выполнения запроса, например рабочих таблиц, которые используются в событиях таймера и фоновых доставляемых диалогах. Компоненты Database Mail, Event Notifications и Query Notifications неявно используют компонент Service Broker. |
|
Хранимые процедуры |
Хранимые процедуры позволяют создавать пользовательские объекты, такие как глобальные или локальные временные таблицы и их индексы, переменные или параметры. Временные объекты хранимых процедур могут кэшироваться для оптимизации операций, которые удаляют и создают эти объекты. Это может повысить требования к месту на диске базы данных tempdb. На каждый временный объект предусмотрено максимум девять страниц для повторного использования. См. раздел «Временные таблицы и переменные table» далее в этой таблице. |
|
Временные таблицы и переменные table
|
Временные таблицы и переменные table хранятся в базе данных tempdb. Требования к месту на диске для временных табличных объектов не отличаются от тех, что были в предыдущих версиях SQL Server. Способ оценки размера временной таблицы — такой же, как и для стандартной таблицы. Дополнительные сведения см. в разделе Предполагаемый размер таблицы. Переменная table ведет себя как локальная переменная. Переменная table имеет тип table и используется в первую очередь для временного хранения набора строк, возвращаемых как результирующий набор возвращающей табличное значение функции. Место на диске для хранения переменной table зависит от размера объявленной переменной и хранимого в ней значения. Локальные временные таблицы и переменные кэшируются, если выполняются следующие условия.
При кэшировании временной таблицы или переменной table временный объект не удаляется после того, как становится ненужным. Вместо этого он усекается. Максимум девять страниц сохраняются и повторно используются при следующем выполнении вызывающего объекта. Кэширование позволяет значительно ускорить операции удаления и создания объектов, а также уменьшить число конфликтов выделения страниц. Для оптимизации производительности следует вычислить место на диске, необходимое для кэшируемых локальных временных таблиц или переменных типа table в базе данных tempdb с помощью следующей формулы: 9 страниц на временную таблицу * среднее количество временных таблиц на процедуру * количество максимальных одновременных выполнений процедуры |
Инструкция CREATE TABLE (Transact-SQL) Использование переменных и параметров (компонент Database Engine) |
Триггеры |
Таблицы inserted и deleted, используемые в триггерах AFTER, создаются в базе данных tempdb. Т.е. версии строк, которые обновляются и удаляются триггером, управляются. Это относится ко всем строкам, которые изменяются инструкцией, вызывающей срабатывание триггера. Версии строк, которые вставляются триггером, не управляются. Триггеры INSTEAD OF и запросы используют базу данных tempdb похожим способом. Для триггеров INSTEAD OF требуется столько же места на диске, как и в предыдущих версиях SQL Server. См. раздел «Запросы» выше в этой таблице. При массовой загрузке данных с включенными триггерами копия каждой удаленной или обновленной строки добавляется в хранилище версий. |
|
Определенные пользователем функции |
Определенные пользователем функции позволяют создавать временные пользовательские объекты, такие как глобальные или локальные таблицы и их индексы, переменные или параметры. Например, таблица, которую возвращает возвращающая табличное значение функция, хранится в базе данных tempdb. Типы данных, разрешенные для параметров, а также для возвращаемых значений скалярных и табличных функций, включают большинство типов данных LOB. Например, возвращаемое значение может иметь тип xml или varchar(max). См. раздел «Переменные и параметры типа данных больших объектов» выше в этой таблице. Временные объекты определяемых пользователем функций, возвращающих табличное значение, могут кэшироваться для оптимизации операций, связанных с удалением и созданием этих объектов. См. раздел «Временные таблицы и переменные table» выше в этой таблице. |
|
XML |
Переменные и параметры типа xml могут иметь размер до 2 ГБ. Для хранения они используют основную память, если значения небольшие. Однако крупные значения хранятся в базе данных tempdb. См. раздел «Переменные и параметры типа данных больших объектов» выше в этой таблице. Системная хранимая процедура sp_xml_preparedocument создает рабочую таблицу в базе данных tempdb. Средство синтаксического анализа MSXML использует эту временную таблицу для хранения XML-документов, прошедших анализ. Базе данных tempdb требуется примерно столько места на диске, каков размер указанного XML-документа, при выполнении хранимой процедуры. |
Реализация языка XML в SQL Server |
Планирование размера дискового пространства, необходимого для обновления до уровня SQL Server
Определение требуемого размера базы данных tempdb в рабочей среде зависит от множества факторов. Как описано выше в этом разделе, эти факторы включают текущую рабочую нагрузку и используемые возможности SQL Server. Рекомендуется проанализировать текущую рабочую нагрузку, выполнив следующие задачи в среде тестирования SQL Server.
Установите автоувеличение для базы данных tempdb.
Запустите отдельные запросы или файлы трассировки рабочей нагрузки и следите за использованием диска базой данных tempdb.
Выполните операции обслуживания индексов, например перестроение индексов и следите за использованием диска базой данных tempdb.
Используйте сведения об используемом месте из предыдущих шагов для прогнозирования общей рабочей нагрузки, отрегулируйте полученное значение с учетом планируемой параллельной обработки и задайте соответствующий размер базы данных tempdb.
Дополнительные сведения о мониторинге использования места на диске базой данных tempdb см. в разделе Устранение неполадок, связанных с нехваткой места на диске для базы данных tempdb. Дополнительные сведения об оценке использования базы данных tempdb для операций с индексами см. в разделе Пример места на диске для индекса.
Настройка базы данных tempdb в рабочих средах
Чтобы оптимизировать использование базы данных tempdb, соблюдайте требования и рекомендации из раздела Оптимизация производительности базы данных tempdb.
Мониторинг использования базы данных tempdb
Нехватка места на диске для базы данных tempdb может привести к существенным сбоям рабочей среды SQL Server и помешать работающим приложениям завершить операции. Для контроля места на диске, используемого указанными функциями в файлах базы данных tempdb, можно использовать динамическое административное представление sys.dm_db_file_space_usage Кроме того, для контроля деятельности по выделению и освобождению страниц в базе данных tempdb на уровне сеанса или задачи можно использовать динамические административные представления sys.dm_db_session_space_usage и sys.dm_db_task_space_usage. Эти представления могут быть использованы для определения больших запросов, временных таблиц или табличных переменных, которые используют много места на диске базы данных tempdb. Также предусмотрено несколько соответствующих счетчиков, которые можно использовать для отслеживания свободного места в базе данных tempdb и ресурсов, использующих базу данных tempdb. Дополнительные сведения см. в разделе Устранение неполадок, связанных с нехваткой места на диске для базы данных tempdb.
См. также