Rozwiązywanie problemów za mało miejsca na dysku w tempdb

Następująca kwerenda zwraca całkowita liczba wolnych stron i całkowita ilość wolnego miejsca w megabajtach (MB) dostępnej w wszystkie pliki w 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;

Następująca kwerenda zwraca całkowitą liczbę stron magazynu wersja i miejsca w MB używanych przez przechowywanie wersja w 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;

Jeśli magazynu wersja przy użyciu dużo miejsca w tempdb, należy określić, co jest najdłużej działają transakcji.Ta kwerenda służy do wyświetlania listy aktywnych transakcji w porządku, przez najdłuższy działającą transakcję.

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

Długotrwałe transakcji, która nie jest związany z operacją indeksu online wymaga magazynu wersja duże.Magazynu w wersja śledzi wszystkie wersje generowany od momentu rozpoczęcia transakcji.Transakcje budowania indeksu online może trwać bardzo długo czas na zakończenie, ale oddzielną wersja używany Magazyn przeznaczonego do operacji online indeksu.Dlatego operacje te nie uniemożliwiają wersje z innymi transakcjami usuwaniu.Aby uzyskać więcej informacji, zobacz Obciążenie zasobów wersji wiersza.

Następująca kwerenda zwraca całkowitą liczbę stron, używany przez obiekty wewnętrzne i miejsca w MB używanych przez wewnętrzne obiekty w 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;

Następująca kwerenda zwraca całkowitą liczbę stron, używany przez obiekty użytkownik i całkowity obszar zajmowany przez obiektów użytkowników w 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;

Następująca kwerenda zwraca całkowitą ilość miejsca na dysku, używane przez wszystkie pliki w tempdb.

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

Poniższy przykład tworzy widok all_task_usage.Kwerendy, widoku zwraca całkowity obszar zajmowany przez wewnętrzne obiekty w wszystkie uruchomione zadania 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

Poniższy przykład tworzy widok all_session_usage.Kwerendy, widoku zwraca miejsce używane przez wszystkie wewnętrzne obiekty z systemem i wykonane zadania w 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

Załóżmy, że te widoki są badane kwerendami interwałem trzy minuty, zestawy wyników Podaj następujące informacje.

• 17:00 sesja 71 przydzielone 100 stron i cofniętych przydziałów 100 stron od rozpoczęcia sesja.

• At 5:03 P.M., session 71 allocated 20100 pages and deallocated 100 pages since the start of the session.

Podczas analizowania informacji można stwierdzić, że między dwoma pomiarami: Sesja przydzielone 20 000 stron wewnętrzne obiekty i nie deallocate, wszystkie strony.Wskazuje potencjalny problem.

Do określenia partia, który działa podczas tego czas, użyj Programu SQL Profiler do przechwytywania RPC: ukończono i SQL:BatchCompleted klas zdarzeń.

Zamiast SQL Server Profiler jest uruchomienie dbcc inputbuffer po co trzy minuty dla wszystkich sesji, jak pokazano w następującym przykładzie.

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;

Sondowania z widoku all_query_usage, i uruchom następującą kwerendę, aby uzyskać tekst kwerendy:

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

Ponieważ sql_handle powinien być unikatowy dla każdej partia unikatowy, nie trzeba zapisać duplikat sql_handle wpisów.

Aby zapisać plan uchwyt i XML plan, uruchom następującą kwerendę.

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;

Jako alternatywę do sondowanie sys.dm_exec_sql_text i sys.dm_exec_query_plan funkcje, można użyć SQL Server Profiler zdarzenia.Istnieją profiler zdarzenia, które mogą być używane do przechwytywania planu kwerend i tekst kwerendy, który jest generowany.Na przykład zdarzenie 165 zwraca statystyki wydajności dla śledzenia, tekst SQL, planów kwerend i statystyk kwerendy.