Устранение неполадок, связанных с нехваткой места на диске для базы данных 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

В следующем примере создается представление 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;

Опросите представление 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;

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