Устранение неполадок, связанных с нехваткой места на диске для базы данных tempdb

  • Статья

В этом подразделе рассматриваются процедуры и рекомендации, направленные на помощь в диагностике, поиск и решение проблем, вызванных нехваткой места на диске для базы данных tempdb. Нехватка места на диске для базы данных tempdb может привести к существенным сбоям производственной среды SQL Server и к невозможности осуществления выполняемыми приложениями необходимых операций.

Требования к пространству для базы данных tempdb

Системная база данных tempdb является глобальным ресурсом, доступным всем пользователям, подключенным к экземпляру сервера SQL Server. База данных tempdb используется для хранения следующих объектов: пользовательские объекты, внутренние объекты и хранилища версий. На сервере SQL Server 2005 для базы данных tempdb необходимо большее дисковое пространство, чем в более ранних версиях сервера SQL Server. Дополнительные сведения см. в разделе Планирование размера базы данных tempdb.

Для контроля дискового пространства, используемого пользовательскими объектами, внутренними объектами и хранилищами версий в файлах базы данных tempdb, можно использовать динамическое административное представление sys.dm_db_file_space_usage. Кроме того, для контроля деятельности по выделению и освобождению страниц в базе данных tempdb на уровне сеанса или задачи можно использовать динамические административные представления sys.dm_db_session_space_usage и sys.dm_db_task_space_usage. Эти представления могут быть использованы для определения больших запросов, временных таблиц или табличных переменных, которые используют большой объем дискового пространства базы данных tempdb.

Диагностика проблем с местом на диске для базы данных tempdb

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

Ошибка Возникает, если

1101 или 1105

Любой сеанс должен выделить пространство в базе данных tempdb.

3959

Хранилище версий заполнено. Эта ошибка обычно возникает после ошибок 1105 или 1101 в журнале.

3967

Хранилище версий принудительно сжато, так как база данных tempdb заполнена.

3958 или 3966

Транзакция не может найти необходимую запись версии в базе данных tempdb.

Проблемы с местом на диске для базы данных tempdb также возникают, если база данных настроена на автоматическое увеличение и ее размер быстро возрастает.

Контроль места на диске, занимаемого базой данных tempdb

Следующие примеры показывают, как определить объем доступного пространства в базе данных tempdb и пространства, используемого хранилищем версий, внутренними и пользовательскими объектами.

Определение объема свободного пространства в базе данных tempdb

Следующий запрос возвращает общее количество свободных страниц и общий объем свободного пространства в мегабайтах (МБ), доступный во всех файлах базы данных tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

Определение объема пространства, используемого хранилищем версий

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

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;

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

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

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

Затянувшаяся транзакция, не связанная с оперативной обработкой индекса, требует большого размера хранилища версий. Это хранилище версий хранит все версии, сформированные со времени запуска транзакции. Транзакции построения индекса в оперативном режиме могут потребовать много времени, но используют отдельные хранилища версий, привязанные к операциям с индексами в оперативном режиме. Поэтому эти операции не предотвращают удаление версий из других транзакций. Дополнительные сведения см. в разделе Использование ресурсов при управлении версиями строк.

Определение объема пространства, используемого внутренними объектами

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

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

Определение объема пространства, используемого пользовательскими объектами

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

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

Определение общего объема пространства (свободного и используемого)

Следующий запрос возвращает общий объем дискового пространства, используемого всеми файлами в базе данных tempdb.

SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files

Контроль пространства, используемого запросами

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

Следующие методы облегчают определение запросов, которые используют больше всего пространства в базе данных tempdb. Первый метод проверяет данные уровня пакета и использует меньшее количество данных, чем второй метод. Второй метод может быть использован для определения конкретного запроса, временной таблицы или табличной переменной, которые занимают дисковое пространство; но для получения ответа необходимо собрать больше данных.

Метод 1. Сведения уровня пакета

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

Для продолжения использования этого метода задание агента SQL Server должно быть настроено на опрос динамических административных представлений sys.dm_db_session_space_usage и sys.dm_db_task_space_usage с интервалом в несколько минут. В следующем примере используется интервал опроса, равный трем минутам. Необходимо опрашивать оба представления, так как представление sys.dm_db_session_space_usage не включает деятельность по выделению пространства текущей активной задачи. Сравнение различий между страницами, выделенными в два интервала, позволяет вычислить, сколько страниц было выделено в период между интервалами.

Следующий пример предоставляет запросы, необходимые для задания агента SQL Server.

A. Получение сведений об объеме пространства, используемого внутренними объектами во всех запущенных на данный момент задачах в каждом сеансе.

В следующем примере создается представление all_task_usage. При запросе к этому представлению возвращается общий объем пространства, используемый внутренними объектами во всех выполняемых в данный момент задачах в базе данных tempdb.

CREATE VIEW all_task_usage
AS 
    SELECT session_id, 
      SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count 
    FROM sys.dm_db_task_space_usage 
    GROUP BY session_id;
GO

Б. Получение сведений об объеме пространства, используемого внутренними объектами текущего сеанса для выполняемых в данных момент и завершенных задач.

В следующем примере создается представление all_session_usage. При запросе к этому представлению возвращается объем пространства, используемый всеми внутренними объектами выполняемых в данный момент и в завершенных задачах в базе данных tempdb.

CREATE VIEW all_session_usage 
AS
    SELECT R1.session_id,
        R1.internal_objects_alloc_page_count 
        + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
        R1.internal_objects_dealloc_page_count 
        + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
    FROM sys.dm_db_session_space_usage AS R1 
    INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;
GO

Если запросы к этим представлениям выполняются с трехминутным интервалом, результирующие наборы предоставляют следующие сведения.

  • К 17:00 сеанс 71 выделил 100 страниц и освободил 100 страниц с начала этого сеанса.
  • К 17:03 сеанс 71 выделил 20 100 страниц и освободил 100 страниц с начала этого сеанса.

При анализе этих сведений можно узнать, что между двумя измерениями сеанс выделил 20 000 страниц для внутренних объектов и не освободил ни одной страницы. Это указывает на возможную проблему.

ms176029.note(ru-ru,SQL.90).gifПримечание.
Администратор базы данных может принять решение о более частом, чем раз в три минуты, проведении опроса. Однако, если запрос выполняется менее трех минут, он, вероятно, не будет занимать значительных объемов дискового пространства в базе данных tempdb.

Чтобы узнать, какой пакет выполнялся в это время, используйте приложение SQL Server Profiler для захвата классов событий RPC:Completed и SQL:BatchCompleted.

Альтернативой использования приложения SQL Server Profiler является выполнение инструкции DBCC INPUTBUFFER каждые три минуты для всех сеансов, как показано в следующем примере.

DECLARE @max int;
DECLARE @i int;
SELECT @max = max (session_id)
FROM sys.dm_exec_sessions
SET @i = 51
  WHILE @i <= @max BEGIN
         IF EXISTS (SELECT session_id FROM sys.dm_exec_sessions
                    WHERE session_id=@i)
         DBCC INPUTBUFFER (@i)
         SET @i=@i+1
         END;

Метод 2. Сведения уровня запроса

Иногда одного взгляда на входной буфер или событие SQL:BatchCompleted приложения SQL Server Profiler бывает недостаточно, чтобы определить, какой запрос занимает больше всего места на диске в базе данных tempdb. Для получения ответа могут быть использованы следующие методы, однако они требуют сбора большего количества данных, чем процедуры, описанные в методе 1.

Чтобы использовать этот метод, настройте задание агента SQL Server для опроса динамического административного представления sys.dm_db_task_space_usage. Интервал опроса должен быть более коротким — раз в минуту — по сравнению с методом 1. Этот интервал должен быть коротким, так как представление sys.dm_db_task_space_usage не возвращает данные, если запрос (задача) в данный момент не выполняются.

В опрашивающем запросе представление, определенное на динамическом административном представлении sys.dm_db_task_space_usage, соединяется с представлением sys.dm_exec_requests для возврата столбцов sql_handle, statement_start_offset, sql_handle, statement_start_offset и plan_handle.

CREATE VIEW all_request_usage
AS 
  SELECT session_id, request_id, 
      SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,
      SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count 
  FROM sys.dm_db_task_space_usage 
  GROUP BY session_id, request_id;
GO
CREATE VIEW all_query_usage
AS
  SELECT R1.session_id, R1.request_id, 
      R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,
      R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle
  FROM all_request_usage R1
  INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;
GO

Если план запроса находится в кэше, можно в любой момент извлечь текст запроса на языке Transact-SQL и план выполнения запроса в формате XML showplan. Чтобы получить текст выполняемого запроса на языке Transact-SQL, используйте значение sql_handle и функцию динамического управления sys.dm_exec_sql_text. Чтобы получить план выполнения запроса, используйте значение sql_handle и функцию динамического управления sys.dm_exec_query_plan.

SELECT * FROM sys.dm_exec_sql_text(@sql_handle);
SELECT * FROM sys.dm_exec_query_plan(@plan_handle);

Если план запроса не находится в кэше, можно использовать один из следующих методов, чтобы получить текст запроса на языке Transact-SQL или план выполнения запроса.

A. Использование метода опроса.

Опросите представление all_query_usage и выполните следующий запрос для получения текста запроса:

SELECT R1.sql_handle, R2.text 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2;

Так как дескриптор sql_handle должен быть уникальным для каждого уникального пакета, нет необходимости сохранять повторяющиеся записи sql_handle.

Чтобы сохранить дескриптор плана и план XML, выполните следующий запрос:

SELECT R1.plan_handle, R2.query_plan 
FROM all_query_usage AS R1
OUTER APPLY sys.dm_exec_query_plan(R1.plan_handle) AS R2;

Б. Использование событий приложения SQL Server Profiler.

В качестве альтернативы опросу функций sys.dm_exec_sql_text и sys.dm_exec_query_plan можно использовать события приложения SQL Server Profiler. Существуют события приложения SQL Server Profiler, которые могут быть использованы для захвата сформированных плана и текста запроса. Например, событие 165 возвращает статистики производительности для трассировки, текст SQL, планы запроса и статистику запроса.

Контроль пространства, используемого временными таблицами и табличными переменными

Для контроля над пространством, используемым временными таблицами и табличными переменными, можно использовать подход, схожий с опрашивающими запросами. Приложения, которые используют большие объемы пользовательских данных внутри временных таблиц или табличных переменных, могут вызвать проблемы использования дискового пространства в базе данных tempdb. Эти таблицы или переменные принадлежат к пользовательским объектам. Можно использовать столбцы user_objects_alloc_page_count и user_objects_dealloc_page_count представления динамического управления sys.dm_db_session_space_usage и следовать методу, описанному выше.

Контроль выделения и освобождения страниц сеансом

Следующая таблица показывает результаты, возвращенные динамическими административными представлениями sys.dm_db_file_space_usage, sys.dm_db_session_space_usage и sys.dm_db_task_space_usage для заданного сеанса. Каждая строка представляет действие по выделению или освобождению пространства в базе данных tempdb для указанного сеанса. Действия отображаются в столбце Событие. Остальные столбцы показывают значения, которые будут возвращены в столбцах динамического административного представления.

Для этого сценария предполагается, что база данных tempdb запускается с 872 страницами в невыделенных экстентах и 100 страницами в зарезервированных под пользовательские объекты экстентах. Сеанс выделяет 10 страниц для пользовательской таблицы, а затем освобождает их. Первые 8 страниц расположены в смешанном экстенте. Оставшиеся 2 страницы расположены в равномерном экстенте.

Событие dm_db_file_space_usage столбец unallocated_extent_page_count dm_db_file_space_usage столбец user_object_reserved_page_count dm_db_session_space_usage и dm_db_task_space_usage столбец user_object_alloc_page_count dm_db_session_space_usage и dm_db_task_space_usage столбец user_object_dealloc_page_count

Запуск

872

100

0

0

Выделяет страницу 1 из существующего смешанного экстента

872

100

1

0

Выделяет страницы с 2 по 8, используя один новый смешанный экстент

864

80

8

0

Выделяет страницу 9, используя один новый равномерный экстент

856

108

16

0

Выделяет страницу 10 из существующего равномерного экстента

856

108

16

0

Освобождает страницу 10 из существующего равномерного экстента

856

108

16

0

Освобождает страницу 9 и равномерный экстент

864

100

16

8

Освобождает страницу 8

864

100

16

9

Освобождает страницы с 7 по 1 и освобождает смешанный экстент

872

100

16

16

См. также

Основные понятия

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

Другие ресурсы

Устранение неполадок ядра СУБД

Справка и поддержка

Получение помощи по SQL Server 2005