Problembehandlung bei unzureichendem Speicherplatz in tempdb

Artikel
12/15/2008

Dieses Thema enthält schrittweise Anweisungen und Empfehlungen zum Diagnostizieren und Beheben von Problemen, die durch unzureichenden Speicherplatz in der tempdb-Datenbank verursacht werden. Wenn nicht mehr genügend Speicherplatz in tempdb vorhanden ist, kann das erhebliche Störungen in der SQL Server-Produktionsumgebung verursachen und dazu führen, dass ausgeführte Anwendungen Vorgänge nicht abschließen können.

Speicherplatzanforderungen von tempdb

Die tempdb-Systemdatenbank ist eine globale Ressource, die für alle Benutzer verfügbar ist, die mit einer Instanz von SQL Server verbunden sind. In der tempdb-Datenbank werden die folgenden Objekte gespeichert: Benutzerobjekte, interne Objekte und Versionsspeicher. In SQL Server 2005 wird für tempdb mehr Speicherplatz benötigt als in früheren Versionen von SQL Server. Weitere Informationen finden Sie unter Kapazitätsplanung für tempdb.

Sie können mithilfe der dynamischen Verwaltungssicht sys.dm_db_file_space_usage den von den Benutzerobjekten, den internen Objekten und den Versionsspeichern in den tempdb-Dateien belegten Speicherplatz überwachen. Um außerdem die Aktivität für die Seitenzuordnung und die Zuordnungsaufhebung in tempdb auf der Sitzungs- oder Aufgabenebene zu überwachen, können Sie die dynamischen Verwaltungssichten sys.dm_db_session_space_usage und sys.dm_db_task_space_usage verwenden. Mithilfe dieser Sichten können große Abfragen, temporäre Tabellen oder Tabellenvariablen identifiziert werden, die große Mengen von Speicherplatz in tempdb belegen.

Diagnostizieren von Speicherplatzproblemen in tempdb

Die folgende Tabelle führt Fehlermeldungen auf, die auf unzureichenden Speicherplatz in der tempdb-Datenbank hinweisen. Diese Fehler können im SQL Server-Fehlerprotokoll gefunden werden und können auch bei ausgeführten Anwendungen zurückgegeben werden.

Fehler	Wird in folgender Situation generiert
1101 oder 1105	Eine Sitzung muss Speicherplatz in tempdb zuordnen.
3959	Der Versionsspeicher ist voll. Dieser Fehler tritt üblicherweise nach einem 1105- oder 1101-Fehler im Protokoll auf.
3967	Der Versionsspeicher wird zwangsweise verkleinert, weil tempdb voll ist.
3958 oder 3966	Eine Transaktion kann den benötigten Versionsdatensatz in tempdb nicht finden.

tempdb-Speicherplatzprobleme werden auch angezeigt, wenn die automatische Vergrößerung der Datenbank aktiviert ist und sich die Größe der Datenbank schnell erhöht.

Überwachen des tempdb-Speicherplatzes

Die folgenden Beispiele zeigen, wie der in tempdb verfügbare Speicherplatz sowie der vom Versionsspeicher und von den internen und von den Benutzerobjekten belegte Speicherplatz ermittelt werden kann.

Ermitteln des in tempdb verfügbaren freien Speicherplatzes

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;

Ermitteln des durch den Versionsspeicher belegten Speicherplatzes

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;

Ermitteln der bereits am längsten dauernden Transaktionen

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.

Ermitteln des von internen Objekten belegten Speicherplatzes

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;

Ermitteln des von Benutzerobjekten belegten Speicherplatzes

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;

Ermitteln des Gesamtspeicherplatzes (verfügbar und belegt)

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

Überwachen des von Abfragen verwendeten Speicherplatzes

Einer der häufigsten Problemtypen im Zusammenhang mit dem tempdb-Speicherplatz wird durch große Abfragen verursacht, die große Mengen an Speicherplatz beanspruchen. Im Allgemeinen wird dieser Speicherplatz für interne Objekte verwendet, z. B. für Arbeitstabellen oder Arbeitsdateien. Zwar können Sie durch Überwachen des von den internen Objekten verwendeten Speicherplatzes ermitteln, wie viel Speicherplatz verwendet wird, Sie können damit aber nicht direkt erkennen, welche Abfrage diesen Speicherplatz verwendet.

Mit den folgenden Methoden können Sie die Abfragen identifizieren, die den meisten Speicherplatz in tempdb verwenden. Die erste Methode überprüft die Daten auf der Batchebene und ist weniger datenintensiv als die zweite Methode. Die zweite Methode kann verwendet werden, um die spezielle Abfrage, temporäre Tabelle oder Tabellenvariable zu identifizieren, die den Speicherplatz verwendet, es müssen dabei jedoch mehr Daten erfasst werden, um die Antwort zu generieren.

Methode 1: Informationen auf der Batchebene

Wenn die Batchanforderung nur einige wenige Abfragen enthält und nur eine davon eine komplexe Abfrage ist, reicht es zumeist aus zu wissen, welcher Batch den Speicherplatz verbraucht, und es muss nicht ermittelt werden, welche spezielle Abfrage für den Speicherverbrauch verantwortlich ist.

Um mit dieser Methode fortzufahren, muss ein SQL Server Agent Job eingerichtet werden, mit dem aus den dynamischen Verwaltungssichten sys.dm_db_session_space_usage und sys.dm_db_task_space_usage ein Datenabruf mit einem Abrufintervall von wenigen Minuten durchgeführt wird. Im folgenden Beispiel wird ein Abrufintervall von drei Minuten verwendet. Sie müssen beide Sichten abrufen, weil sys.dm_db_session_space_usage nicht die Zuordnungsaktivität der aktuell aktiven Aufgabe einschließt. Durch Vergleichen des Unterschieds zwischen den bei zwei Zeitintervallen zugeordneten Seiten können Sie berechnen, wie viele Seiten zwischen den Intervallen zugeordnet sind.

Die folgenden Beispiele zeigen die Abfragen, die für den SQL Server Agent-Auftrag benötigt werden.

A. Ermitteln des Speicherplatzes, der in jeder Sitzung von den internen Objekten in allen gleichzeitig ausgeführten Aufgaben verbraucht wird.

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

B. Ermitteln des Speicherplatzes, der in der aktuellen Sitzung von den internen Objekten sowohl für laufende als auch für abgeschlossene Aufgaben verbraucht wird.

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;

Methode 2: Informationen auf der Abfrageebene

Manchmal geht aus einer bloßen Betrachtung des Eingabepuffers oder des SQL Server Profiler-Ereignisses SQL:BatchCompleted nicht immer hervor, welche Abfrage den meisten Speicherplatz in tempdb verwendet. Mit den folgenden Methoden kann diese Frage beantwortet werden, allerdings müssen für diese Methoden mehr Daten erfasst werden als bei den in Methode 1 definierten Prozeduren.

Um mit dieser Methode fortzufahren, muss ein SQL Server Agent Job-Auftrag eingerichtet werden, mit dem aus der dynamischen Verwaltungssicht sys.dm_db_task_space_usage ein Datenabruf durchgeführt wird. Das Abrufintervall sollte im Vergleich zur Methode 1 kurz sein, z. B. einmal pro Minute. Das Intervall wird so kurz festgelegt, weil sys.dm_db_task_space_usage keine Daten zurückgibt, wenn die Abfrage (die Aufgabe) aktuell nicht ausgeführt wird.

In der Abrufabfrage wird die für die dynamische Verwaltungssicht sys.dm_db_task_space_usage definierte Sicht mit sys.dm_exec_requests verknüpft, um die Spalten sql_handle, statement_start_offset, statement_end_offset und plan_handle zurückzugeben.

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

Wenn sich der Abfrageplan im Cache befindet, können Sie jederzeit den Transact-SQL-Text der Abfrage und den Abfrageausführungsplan im XML showplan-Format abrufen. Um den Transact-SQL-Text für die ausgeführte Abfrage abzurufen, verwenden Sie den sql_handle-Wert und die dynamische Verwaltungsfunktion sys.dm_exec_sql_text. Um die Abfrageplanausführung abzurufen, verwenden Sie den plan_handle-Wert und die dynamische Verwaltungsfunktion sys.dm_exec_query_plan.

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

Wenn sich der Abfrageplan nicht im Cache befindet, können Sie eine der folgenden Methoden verwenden, um den Transact-SQL-Text der Abfrage und den Abfrageausführungsplan abzurufen.

A. Verwenden der Abrufmethode

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;

B. Verwenden von SQL Server Profiler-Ereignissen

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.

Überwachen des von temporären Tabellen und Tabellenvariablen verwendeten Speicherplatzes

Zum Überwachen des von temporären Tabellen und temporären Variablen verwendeten Speicherplatzes gehen Sie ähnlich vor wie beim Abrufen von Abfragen. Anwendungen, die eine große Menge an Benutzerdaten in temporären Tabellen oder temporären Variablen erfassen, können zu Speichernutzungsproblemen in tempdb führen. Diese Tabellen oder Variablen gehören zu den Benutzerobjekten. Sie können die Spalten user_objects_alloc_page_count und user_objects_dealloc_page_count in der dynamischen Verwaltungssicht sys.dm_db_session_space_usage verwenden und entsprechend den oben beschriebenen Methoden vorgehen.

Überwachen der Seitenzuordnung und der Zuordnungsaufhebung nach Sitzung

Die folgende Tabelle zeigt die Ergebnisse, die von den dynamischen Verwaltungssichten sys.dm_db_file_space_usage, sys.dm_db_session_space_usage und sys.dm_db_task_space_usage für eine angegebene Sitzung zurückgegeben werden. Jede Zeile steht dabei für eine Zuordnungs- oder Zuordnungsaufhebungsaktivität in tempdb für eine angegebene Sitzung. Die Aktivität ist in der Event-Spalte aufgeführt. Die übrigen Spalten zeigen die Werte, die in den Spalten der dynamischen Verwaltungssichten zurückgegeben werden.

In diesem Szenario wird angenommen, dass die tempdb-Datenbank mit 872 Seiten in nicht zugeordneten Blöcken und 100 Seiten in von Benutzerobjekten reservierten Blöcken startet. Die Sitzung ordnet 100 Seiten einer Benutzertabelle zu und hebt anschließend die Zuordnung dieser Seiten vollständig wieder auf. Die ersten 8 Seiten befinden sich in einem gemischten Block. Die übrigen 2 Seiten befinden sich in einem einheitlichen Block.

Ereignis	dm_db_file_space_usage unallocated_extent_page_count column	dm_db_file_space_usage user_object_reserved_page_count column	dm_db_session_space_usage and dm_db_task_space_usage user_object_alloc_page_count column	dm_db_session_space_usage and dm_db_task_space_usage user_object_dealloc_page_count column
Starten	872	100	0	0
Zuordnen von Seite 1 aus dem vorhandenen gemischten Block	872	100	1	0
Zuordnen der Seiten 2 bis 8: ein neuer gemischter Block wird verbraucht	864	80	8	0
Zuordnen der Seite 9: ein neuer einheitlicher Block wird verbraucht	856	108	16	0
Zuordnen von Seite 10 aus dem vorhandenen einheitlichen Block	856	108	16	0
Aufheben der Zuordnung von Seite 10 aus dem vorhandenen einheitlichen Block	856	108	16	0
Aufheben der Zuordnung von Seite 9 und des einheitlichen Blocks	864	100	16	8
Aufheben der Zuordnung von Seite 8	864	100	16	9
Aufheben der Zuordnung der Seiten 7 bis 1 und Zuordnungsaufhebung für den gemischten Block	872	100	16	16

Siehe auch

Freigeben über