Поделиться через


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

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

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

Системная база данных 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 страниц для внутренних объектов и не освободил ни одной страницы. Это указывает на возможную проблему.

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

Администратор базы данных может принять решение о более частом, чем раз в три минуты, проведении опроса. Однако если запрос выполняется менее трех минут, он, вероятно, не будет занимать значительных объемов дискового пространства в базе данных 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