Планирование размера базы данных tempdb
Изменения: 14 апреля 2006 г.
В этом подразделе описываются изменения в системной базе данных tempdb, сделанные в SQL Server 2005, а также содержатся рекомендации по определению необходимого места на диске для базы данных tempdb. Также включены рекомендации по настройке базы данных tempdb для обеспечения оптимальной производительности в рабочей среде и сведения по мониторингу использования места базой данных tempdb.
В SQL Server 2005 базе данных tempdb требуется больше места на диске, чем в предыдущих версиях SQL Server. Это обусловлено следующими изменениями:
- некоторые новые возможности SQL Server 2005 используют базу данных tempdb;
- некоторые возможности, использовавшие базу данных tempdb в предыдущих версиях SQL Server, могут требовать больше места на диске базы данных tempdb в SQL Server 2005;
- некоторые возможности, которые не применяли базу данных tempdb в предыдущих версиях SQL Server, применяют базу данных tempdb в SQL Server 2005.
Учитывая эти изменения, важно предоставить достаточно места на диске для базы данных tempdb при обновлении до SQL Server 2005, чтобы обеспечить текущую рабочую нагрузку и дополнительные требования к месту на диске тех возможностей SQL Server, которые используют базу данных tempdb.
Использование базы данных tempdb
Системная база данных tempdb является глобальным ресурсом, доступным всем пользователям, которые подключены к экземпляру SQL Server. База данных tempdb служит для хранения следующих объектов: пользовательских объектов, внутренних объектов и хранилищ версий.
Пользовательские объекты
Пользовательские объекты явно создаются пользователями. Эти объекты могут быть ограничены областью действия либо пользовательского сеанса, либо подпрограммы, в которой они созданы. Подпрограмма — это хранимая процедура, триггер или определяемая пользователем функция. Пользовательскими объектами могут быть:
- пользовательские таблицы и индексы
- системные таблицы и индексы
- глобальные временные таблицы и индексы
- локальные временные таблицы и индексы
- табличные переменные
- таблицы, возвращаемые возвращающими табличное значение функциями
Внутренние объекты
Внутренние объекты создаются компонентом SQL Server Database Engine при необходимости для обработки инструкций SQL Server. Они создаются и удаляются в области действия инструкции. Внутренними объектами могут быть:
- рабочие таблицы для хранения операций с курсором, операций подкачки и временных больших объектов (LOB);
- рабочие файлы для операций хэш-соединения или статистических хэш-выражений;
- промежуточные результаты сортировки при таких операциях, как создание или перестроение индексов (если указан параметр SORT_IN_TEMPDB), либо определенных запросах GROUP BY, ORDER BY или UNION.
Каждый внутренний объект использует минимум девять страниц: одну IAM-страницу и один восьмистраничный экстент. Дополнительные сведения о страницах и экстентах см. в разделе Страницы и экстенты.
Хранилища версий
Хранилище версий — это коллекция страниц данных, содержащих строки данных, которые необходимы для поддержки возможностей, применяющих управление версиями строк. В SQL Server 2005 предусмотрено два хранилища версий: общее хранилище версий и хранилище версий оперативного построения индексов. Хранилища версий содержат следующее:
- версии строк, сформированных транзакциями изменения данных в базе данных, которая использует уровни изоляции моментальных снимков или зафиксированного чтения при использовании управления версиями строк;
- версии строк, сформированных транзакциями изменения данных для следующих операций: фоновых операций с индексами, множественных активных результирующих наборов (режим MARS) и триггеров AFTER.
В следующей таблице приведены возможности SQL Server, которые создают пользовательские объекты, внутренние объекты и версии строк в базе данных tempdb. Если возможно, предоставлены способы оценки использования места на диске.
| Возможность | Использование базы данных tempdb | Дополнительные сведения |
|---|---|---|
Операции массовой загрузки с триггерами включены |
В SQL Server 2005 оптимизация массового импорта применяется в случаях, когда включены триггеры. SQL Server 2005 использует управление версиями строк для тех триггеров, которые обновляют или удаляют транзакции. Копия каждой удаленной или обновленной строки добавляется в хранилище версий. См. раздел «Триггеры» далее в этой таблице. |
|
Запросы обобщенных табличных выражений |
Обобщенные табличные выражения могут рассматриваться как временные результирующие наборы, определенные в области выполнения одиночных инструкций SELECT, INSERT, UPDATE, DELETE и CREATE VIEW. Когда в плане запроса для обобщенного табличного выражения для сохранения промежуточных результатов используется оператор буферизации, ядро СУБД создает рабочую таблицу в базе данных tempdb для поддержки этой операции. |
|
Курсоры |
Управляемые набором ключей и статические курсоры используют рабочие таблицы, встроенные в базу данных tempdb. Управляемые набором ключей курсоры используют рабочую таблицу для хранения набора ключей, который идентифицирует строки в курсоре. Статические курсоры используют рабочую таблицу для хранения полного результирующего набора курсора. Использование места на диске курсорами может изменяться в зависимости от выбранного плана запроса. Если план запроса такой, как в предыдущих версиях SQL Server, выделяется примерно столько же места на диске. |
|
Компонент Database Mail |
См. раздел «Компонент Service Broker» далее в этой таблице. |
|
DBCC CHECKDB |
Команда DBCC CHECKDB использует рабочие таблицы базы данных tempdb для хранения промежуточных результатов и сортировки. Для команды DBCC CHECKDB требуется больше места на диске вследствие следующих изменений в ее работе.
Чтобы определить необходимое место на диске для базы данных tempdb для этой операции, запустите команду DBCC CHECKDB WITH ESTIMATE_ONLY. |
|
Уведомления о событиях |
См. раздел «Компонент Service Broker» далее в этой таблице. |
|
Индексы |
При создании или перестроении (в автономном или оперативном режиме) индекса можно присвоить параметру SORT_IN_TEMPDB значение ON, чтобы указать компоненту Database Engine базу данных tempdb для хранения промежуточных результатов сортировки, которые используются при построении индекса. Если указан параметр SORT_IN_TEMPDB и необходимо выполнить сортировку, база данных tempdb должна иметь достаточно места на диске для хранения наибольшего индекса, к которому следует добавить место, равное значению параметра index create memory. Дополнительные сведения см. в разделе Пример места на диске для индекса. Для автономных операций с индексами, которые не используют возможности SQL Server 2005, требуется столько же места на диске, как и в предыдущих версиях SQL Server. Изменения использования базы данных tempdb в SQL Server 2005 В SQL Server 2005 предусмотрена возможность секционирования таблиц и индексов. Для секционированных индексов, если параметр индекса SORT_IN_TEMPDB указан и индекс выровнен относительно базовой таблицы, базе данных tempdb должно быть выделено достаточно места для хранения промежуточных запусков сортировки наибольшей секции. Если индекс не выровнен, в базе данных tempdb должно быть достаточно места для хранения промежуточных запусков сортировки всех секций. Дополнительные сведения см. в разделе Дополнительные рекомендации по секционированным индексам. Для фоновых операций с индексами применяется управление версиями строк, позволяющее изолировать действия с индексами от воздействия изменений, внесенных другими транзакциями. Это позволяет не запрашивать общую блокировку уже считанных строк. Одновременное обновление и удаление, выполняемое пользователем во время фоновых операций с индексами, требует места в базе данных tempdb для записей с версиями. Если в фоновых операциях с индексами используется параметр SORT_IN_TEMPDB и необходима сортировка, база данных tempdb должна иметь дополнительное место на диске для хранения промежуточных результатов сортировки. Фоновым операциям с индексами, создающим, удаляющим или перестраивающим кластеризованный индекс, также необходимо дополнительное место для построения и обслуживания временного индекса сопоставления. Дополнительные сведения см. в разделе Требования к месту на диске для DDL-операций индекса. |
База данных tempdb и создание индекса Дополнительные рекомендации по секционированным индексам Требования к месту на диске для DDL-операций индекса |
Переменные и параметры типа данных больших объектов (LOB) |
Типы данных 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 |
В SQL Server 2005 режим MARS может создаваться в рамках одного соединения. Если во время сеанса MARS выполняется инструкция изменения данных (например, INSERT, UPDATE или DELETE) в момент, когда есть активный результирующий набор, строки, которых коснулось изменение, сохраняются в хранилище версий базы данных tempdb. См. раздел «Управление версиями строк» далее в этой таблице. |
|
Уведомления запросов |
См. раздел «Компонент Service Broker» далее в этой таблице. |
|
Запросы |
Запросы, содержащие инструкции SELECT, INSERT, UPDATE и DELETE, могут использовать внутренние объекты для хранения промежуточных результатов операций хэш-соединений, статистических хэш-выражений или сортировки. Если план выполнения запроса в SQL Server 2005 кэширован, кэшируются и рабочие таблицы, необходимые плану. Если рабочая таблица кэшируется, таблица усекается, и в кэше остается девять страниц для повторного использования. Это повышает производительность следующего выполнения запроса. При нехватке памяти компонент Database Engine может удалить план выполнения и связанные рабочие таблицы. |
|
Управление версиями строк |
Управление версиями строк — это стандартная структура в SQL Server 2005, используемая для поддержания следующих функций.
Версии строк хранятся в хранилище версий базы данных 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. |
|
Хранимые процедуры |
Хранимые процедуры позволяют создавать пользовательские объекты, такие как глобальные или локальные временные таблицы и их индексы, переменные или параметры. В SQL Server 2005 временные объекты хранимых процедур могут кэшироваться для оптимизации выполнения операций, связанных с удалением и созданием этих объектов. Это может повысить требования к месту на диске базы данных tempdb. На каждый временный объект предусмотрено максимум девять страниц для повторного использования. См. раздел «Временные таблицы и переменные table» далее в этой таблице. |
|
Временные таблицы и переменные table
|
Временные таблицы и переменные table хранятся в базе данных tempdb. Требования к месту на диске для временных табличных объектов не отличаются от тех, что были в предыдущих версиях SQL Server. Способ оценки размера временной таблицы — такой же, как и для стандартной таблицы. Дополнительные сведения см. в разделе Предполагаемый размер таблицы. Переменная table ведет себя как локальная переменная. Переменная table имеет тип table и используется в первую очередь для временного хранения набора строк, возвращаемых как результирующий набор возвращающей табличное значение функции. Место на диске для хранения переменной table зависит от размера объявленной переменной и хранимого в ней значения. В SQL Server 2005 локальные временные таблицы и переменные кэшируются при выполнении следующих условий.
При кэшировании временной таблицы или переменной table временный объект не удаляется после того, как становится ненужным. Вместо этого он усекается. Максимум девять страниц сохраняются и повторно используются при следующем выполнении вызывающего объекта. Кэширование позволяет значительно ускорить операции удаления и создания объектов, а также уменьшить число конфликтов выделения страниц. Для оптимизации производительности следует вычислить место на диске, необходимое для кэшируемых локальных временных таблиц или переменных типа table в базе данных tempdb с помощью следующей формулы: 9 страниц на временную таблицу * среднее количество временных таблиц на процедуру * количество максимальных одновременных выполнений процедуры |
|
Триггеры |
В предыдущих версиях SQL Server логика триггеров была основана на записях журнала, и база данных tempdb не использовалась. В SQL Server 2005 таблицы inserted и deleted, используемые в триггерах AFTER, создаются в базе данных tempdb. Т.е. версии строк, которые обновляются и удаляются триггером, управляются. Это относится ко всем строкам, которые изменяются инструкцией, вызывающей срабатывание триггера. Версии строк, которые вставляются триггером, не управляются. Триггеры INSTEAD OF и запросы используют базу данных tempdb похожим способом. Для триггеров INSTEAD OF требуется столько же места на диске, как и в предыдущих версиях SQL Server. См. раздел «Запросы» выше в этой таблице. При массовой загрузке данных с включенными триггерами копия каждой удаленной или обновленной строки добавляется в хранилище версий. |
|
Определенные пользователем функции |
Определенные пользователем функции позволяют создавать временные пользовательские объекты, такие как глобальные или локальные таблицы и их индексы, переменные или параметры. Например, таблица, которую возвращает возвращающая табличное значение функция, хранится в базе данных tempdb. В SQL Server 2005 типы данных, разрешенные для параметров и возвращаемых значений скалярных функций и возвращающих табличное значение функций, включают большинство типов данных LOB. Например, возвращаемое значение может иметь тип xml или varchar(max). См. раздел «Переменные и параметры типа данных больших объектов» выше в этой таблице. В SQL Server 2005 временные объекты возвращающих табличное значение, определяемых пользователем функций могут кэшироваться для оптимизации выполнения операций, связанных с удалением и созданием этих объектов. См. раздел «Временные таблицы и переменные table» выше в этой таблице. |
|
XML |
Переменные и параметры типа xml могут иметь размер до 2 ГБ. Для хранения они используют основную память, если значения небольшие. Однако крупные значения хранятся в базе данных tempdb. См. раздел «Переменные и параметры типа данных больших объектов» выше в этой таблице. Системная хранимая процедура sp_xml_preparedocument создает рабочую таблицу в базе данных tempdb. Синтаксический анализатор MSXML использует эту временную таблицу для хранения XML-документов, прошедших анализ. Базе данных tempdb требуется примерно столько места на диске, каков размер указанного XML-документа, при выполнении хранимой процедуры. |
Планирование размера для обновления на SQL Server 2005
Определение требуемого размера базы данных tempdb в рабочей среде зависит от множества факторов. Как описано выше в этом разделе, эти факторы включают текущую рабочую нагрузку и используемые возможности SQL Server. Рекомендуется проанализировать текущую рабочую нагрузку, выполнив следующие задачи в среде тестирования SQL Server 2005.
- Установите автоматическое расширение для базы данных tempdb.
- Запустите отдельные запросы или файлы трассировки рабочей нагрузки и следите за использованием диска базой данных tempdb.
- Выполните операции обслуживания индексов, например перестроение индексов и следите за использованием диска базой данных tempdb.
- Используйте сведения об используемом месте из предыдущих шагов для прогнозирования общей рабочей нагрузки, скорректируйте полученное значение с учетом планируемой параллельной обработки и задайте соответствующий размер базы данных tempdb.
Дополнительные сведения о мониторинге использования места на диске базой данных tempdb см. в разделе Устранение неполадок, связанных с нехваткой места на диске для базы данных tempdb. Дополнительные сведения об оценке использования базы данных tempdb для операций с индексами см. в разделе Пример места на диске для индекса.
Настройка базы данных tempdb в рабочих средах
Чтобы оптимизировать использование базы данных tempdb, соблюдайте требования и рекомендации из раздела Оптимизация производительности базы данных tempdb.
Мониторинг использования базы данных tempdb
Нехватка места на диске для базы данных tempdb может привести к существенным сбоям рабочей среды SQL Server и помешать работающим приложениям завершить операции. В SQL Server 2005 можно использовать динамическое административное представление sys.dm_db_file_space_usage для контроля места на диске, используемого этими возможностями в файлах базы данных tempdb. Кроме того, для контроля деятельности по выделению и освобождению страниц в базе данных tempdb на уровне сеанса или задачи можно использовать динамические представления управления sys.dm_db_session_space_usage и sys.dm_db_task_space_usage. Эти представления могут быть использованы для определения больших запросов, временных таблиц или табличных переменных, которые используют много места на диске базы данных tempdb. Также предусмотрено несколько соответствующих счетчиков, которые можно использовать для отслеживания свободного места в базе данных tempdb и ресурсов, использующих базу данных tempdb. Дополнительные сведения см. в разделе Устранение неполадок, связанных с нехваткой места на диске для базы данных tempdb.
См. также
Задачи
Устранение неполадок, связанных с нехваткой места на диске для базы данных tempdb
Основные понятия
База данных tempdb
Оптимизация производительности базы данных tempdb
Другие ресурсы
Оптимизация баз данных
Работа с базой данных tempdb в SQL Server 2005
Справка и поддержка
Получение помощи по SQL Server 2005
Журнал изменений
| Версия | Журнал |
|---|---|
14 апреля 2006 г. |
|