Problembehandlung bei unzureichendem Speicherplatz in tempdb

Die folgende Abfrage gibt die in allen Dateien in tempdb verfügbare Gesamtanzahl der freien Seiten und den freien Gesamtspeicherplatz (in MB) zurück.

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;

Die folgende Abfrage gibt die Gesamtanzahl der vom Versionsspeicher in tempdb verwendeten Seiten sowie den vom Versionsspeicher belegten Gesamtspeicherplatz (in MB) zurück.

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;

Wenn der Versionsspeicher viel Speicherplatz in tempdb belegt, müssen Sie ermitteln, welche Transaktion bereits am längsten ausgeführt wird. Mit dieser Abfrage können Sie die aktiven Transaktionen auflisten, und zwar in der Reihenfolge beginnend mit der am längsten dauernden Transaktion.

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

Eine lange andauernde Transaktion, die nicht mit einem Onlineindexvorgang verknüpft ist, erfordert einen großen Versionsspeicher. Dieser Versionsspeicher enthält alle Versionen, die seit dem Start der Transaktion generiert wurden. Onlineindexerstellungsvorgänge können bis zu ihrem Abschluss lange Zeit in Anspruch nehmen, es wird jedoch ein getrennter Versionsspeicher speziell für Onlineindexvorgänge verwendet. Deshalb verhindern diese Vorgänge nicht, dass die Versionen aus anderen Transaktionen entfernt werden. Weitere Informationen finden Sie unter Ressourcenverwendung bei der Zeilenversionsverwaltung.

Die folgende Abfrage gibt die Gesamtzahl der von den internen Objekten in tempdb verwendeten Seiten sowie den von den internen Objekten belegten Gesamtspeicherplatz (in MB) zurück.

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;

Die folgende Abfrage gibt die Gesamtzahl der von den Benutzerobjekten in tempdb verwendeten Seiten sowie den von den Benutzerobjekten belegten Gesamtspeicherplatz (in MB) zurück.

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;

Die folgende Abfrage gibt die Gesamtmenge des Speicherplatzes zurück, der von allen Dateien in tempdb belegt wird.

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

Mit dem folgenden Beispiel wird die Sicht all_task_usage erstellt. Nach der Abfrage gibt die Sicht den Gesamtspeicherplatz zurück, der von den internen Objekten in allen gleichzeitig ausgeführten Aufgaben in tempdb verwendet wird.

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

Mit dem folgenden Beispiel wird die Sicht all_session_usage erstellt. Nach der Abfrage gibt die Sicht den Gesamtspeicherplatz zurück, der von allen internen Objekten von gerade ausgeführten und abgeschlossenen Aufgaben in tempdb verwendet wird.

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

Angenommen, diese Sichten werden in einem dreiminütigen Intervall abgefragt, dann enthalten die Resultsets die folgenden Informationen.

• Um 17:00 Uhr erfolgte durch Sitzung 71 eine Zuordnung von 100 Seiten und eine Zuordnungsaufhebung von 100 Seiten seit dem Start der Sitzung.

• Um 17:03 Uhr erfolgte durch Sitzung 71 eine Zuordnung von 20100 Seiten und eine Zuordnungsaufhebung von 100 Seiten seit dem Start der Sitzung.

Wenn Sie diese Informationen analysieren, stellen Sie fest, dass zwischen den beiden Messungen durch die Sitzung 20.000 Seiten für interne Objekte zugeordnet wurden und dass keine Zuordnungsaufhebung von Seiten erfolgte. Dies weist auf ein potenzielles Problem hin.

Hinweis
Als Datenbankadministrator könnten Sie festlegen, dass der Abruf häufiger als alle drei Minuten erfolgt. Wenn eine Abfrage weniger als drei Minuten ausgeführt wird, verbraucht sie wahrscheinlich keine erhebliche Speichermenge in tempdb.

Um zu ermitteln, welcher Batch während dieser Zeit ausgeführt wird, verwenden Sie SQL Server Profiler, um die Ereignisklassen RPC:Completed und SQL:BatchCompleted aufzuzeichnen.

Eine Alternative zum Verwenden von SQL Server Profiler ist das Ausführen von DBCC INPUTBUFFER alle drei Minuten einmal für alle Sitzungen, was im folgenden Beispiel gezeigt wird.

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;

Führen Sie einen Abruf der Sicht all_query_usage durch, und führen Sie die folgende Abfrage aus, um den Abfragetext abzurufen:

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

Da sql_handle für jeden eindeutigen Batch eindeutig sein sollte, müssen Sie keine Duplikate der sql_handle-Einträge speichern.

Um das Planhandle und den XML-Plan zu speichern, führen Sie die folgende Abfrage aus.

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;

Als Alternative zum Abrufen der Funktionen sys.dm_exec_sql_text und sys.dm_exec_query_plan können Sie SQL Server Profiler-Ereignisse verwenden. Es gibt Profiler-Ereignisse, die zum Aufzeichnen des Abfrageplans und des generierten Abfragetexts verwendet werden können. Beispielsweise gibt Ereignis 165 Leistungsstatistiken für die Ablaufverfolgung, den SQL-Text, Abfragepläne und Abfragestatistiken zurück.