Risoluzione dei problemi relativi allo spazio su disco insufficiente in tempdb

Articolo
12/15/2008

Questo argomento fornisce le procedure e le indicazioni che consentono di individuare e di risolvere i problemi causati da uno spazio su disco insufficiente nel database tempdb. L'esaurimento dello spazio disponibile in tempdb può provocare interruzioni significative nell'ambiente di produzione di SQL Server e può impedire alle applicazioni di completare le operazioni in esecuzione.

Requisiti di spazio di tempdb

Il database di sistema tempdb è una risorsa globale disponibile a tutti gli utenti connessi a un'istanza di SQL Server. Il database tempdb viene utilizzato per archiviare oggetti utente, oggetti interni e archivi delle versioni. In SQL Server 2005, tempdb richiede più spazio su disco rispetto alle versioni precedenti di SQL Server. Per ulteriori informazioni, vedere Pianificazione delle capacità per tempdb.

È possibile utilizzare la vista a gestione dinamica sys.dm_db_file_space_usage per monitorare lo spazio su disco utilizzato da oggetti utente, oggetti interni e archivi delle versioni nei file tempdb. Per monitorare l'allocazione delle pagine o l'attività di deallocazione in tempdb a livello di sessione o di attività, è inoltre possibile utilizzare le viste a gestione dinamica sys.dm_db_session_space_usage e sys.dm_db_task_space_usage. Queste viste consentono di identificare le query, le tabelle temporanee o le variabili di tabella che utilizzano una grande quantità di spazio su disco in tempdb.

Diagnostica dei problemi relativi allo spazio su disco in tempdb

Nella tabella seguente vengono elencati i messaggi di errore relativi allo spazio su disco insufficiente nel database tempdb. Questi errori sono disponibili nel log degli errori di SQL Server e possono inoltre essere restituiti nelle applicazioni in esecuzione.

Errore	Situazioni in cui viene generato
1101 o 1105	Una sessione deve allocare spazio in tempdb.
3959	L'archivio delle versioni è pieno. In genere viene visualizzato nel log dopo un errore 1105 o 1101.
3967	L'archivio delle versioni viene compattato perché tempdb è pieno.
3958 o 3966	Una transazione non riesce a trovare il record della versione richiesta in tempdb.

I problemi di spazio su disco in tempdb vengono segnalati anche quando per il database è impostato l'aumento automatico delle dimensioni e le dimensioni del database aumentano rapidamente.

Monitoraggio dello spazio su disco in tempdb

Negli esempi seguenti viene illustrato come rilevare la quantità di spazio disponibile in tempdb nonché lo spazio utilizzato dell'archivio delle versioni e dagli oggetti utente e interni.

Rilevamento dello spazio libero in tempdb

La query seguente restituisce il numero totale di pagine libere e lo spazio libero totale in megabyte (MB) disponibile in tutti i file di 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;

Rilevamento della quantità di spazio utilizzata dall'archivio delle versioni

La query seguente restituisce il numero totale di pagine e lo spazio totale in MB utilizzati dall'archivio delle versioni in 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;

Rilevamento della transazione con l'esecuzione più prolungata

Se l'archivio delle versioni utilizza una grande quantità di spazio in tempdb, è necessario determinare quale sia la transazione con l'esecuzione più prolungata. Per ordinare in un elenco le transazioni attive in base alla transazione con l'esecuzione più prolungata, è possibile utilizzare la query seguente.

SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions 
ORDER BY elapsed_time_seconds DESC;

Una transazione con esecuzione prolungata non correlata a un'operazione sugli indici in linea richiede un archivio delle versioni di grandi dimensioni. Tale archivio contiene tutte le versioni create dall'avvio della transazione. Le transazioni per la creazione di indici in linea possono richiedere tempi lunghi per il completamento, ma per le operazioni sugli indici in linea viene utilizzato un archivio delle versioni separato. Queste operazioni non impediscono pertanto di rimuovere le versioni di altre transazioni. Per ulteriori informazioni, vedere Utilizzo delle risorse di controllo delle versioni delle righe.

Rilevamento della quantità di spazio utilizzata dagli oggetti interni

La query seguente restituisce il numero totale di pagine e lo spazio totale in MB utilizzati dagli oggetti interni in 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;

Rilevamento della quantità di spazio utilizzata dagli oggetti utente

La query seguente restituisce il numero totale di pagine e lo spazio totale utilizzati dagli oggetti utente in 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;

Rilevamento della quantità di spazio totale (libero e utilizzato)

La query seguente restituisce la quantità totale di spazio su disco utilizzata da tutti i file di tempdb.

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

Monitoraggio dello spazio utilizzato dalle query

Uno dei problemi più comuni relativi all'utilizzo dello spazio di tempdb è correlato alle query di grandi dimensioni. Queste query in genere utilizzano una grande quantità di spazio per gli oggetti interni, ad esempio le tabelle o i file di lavoro. Il monitoraggio dello spazio utilizzato dagli oggetti interni consente di conoscere la quantità di spazio utilizzata, ma non di individuare direttamente la query che utilizza tale spazio.

Di seguito sono illustrati alcuni sistemi tramite i quali è possibile identificare le query che utilizzano la maggior parte dello spazio di tempdb. Il primo sistema esamina i dati a livello di batch e richiede l'analisi di una quantità di dati inferiore rispetto al secondo sistema. Il secondo sistema consente di identificare la query, la tabella temporanea o la variabile di tabella specifica che utilizza lo spazio su disco, ma per ottenere i risultati è necessario raccogliere più dati.

Sistema 1: Informazioni a livello di batch

Se la richiesta di batch contiene un numero limitato di query e solo una di esse è una query complessa, è in genere sufficiente individuare il batch che utilizza lo spazio anziché la query specifica.

Per utilizzare questo sistema, è necessario configurare un processo di SQL Server Agent che esegue il polling dalle viste a gestione dinamica sys.dm_db_session_space_usage e sys.dm_db_task_space_usage utilizzando un intervallo di polling di pochi minuti. Nell'esempio seguente viene utilizzato un intervallo di polling di tre minuti. È necessario eseguire il polling da entrambe le viste perché sys.dm_db_session_space_usage non include l'allocazione dell'attività attiva corrente. Per calcolare il numero di pagine allocate tra gli intervalli è possibile calcolare la differenza tra le pagine allocate nei due intervalli di tempo.

Negli esempi seguenti vengono illustrate le query per il processo di SQL Server Agent.

A. Acquisizione dello spazio utilizzato dagli oggetti interni in tutte le attività attualmente in corso in ogni sessione.

Nell'esempio seguente viene creata la vista all_task_usage. Quando si esegue la query sulla vista, viene restituito lo spazio totale utilizzato dagli oggetti interni in tutte le attività attualmente in corso in 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

B. Acquisizione dello spazio utilizzato dagli oggetti interni nella sessione corrente sia per le attività in corso che per quelle completate

Nell'esempio seguente viene creata la vista all_session_usage. Quando si esegue la query sulla vista, viene restituito lo spazio utilizzato da tutti gli oggetti interni per le attività in corso e completate in 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

Si supponga che i set di risultati ottenuti eseguendo la query sulle viste a intervalli di tre minuti forniscano le informazioni seguenti.

Alle 17.00, la sessione 71 ha allocato 100 pagine e ha deallocato 100 pagine dall'inizio della sessione.
Alle 17.03, la sessione 71 ha allocato 20100 pagine e ha deallocato 100 pagine dall'inizio della sessione.

Se si analizzano queste informazioni, è possibile affermare che tra le due misure la sessione ha allocato 20.000 per gli oggetti interni e non ha deallocato alcuna pagina. Ciò indica un problema potenziale.

[!NOTA] Come amministratore di sistema, si può decidere di eseguire il polling con una frequenza maggiore di tre minuti. Se una query viene eseguita per un periodo inferiore ai tre minuti, è tuttavia possibile che utilizzi una quantità di spazio significativa in tempdb.

Per stabilire quale batch è in esecuzione in questo periodo di tempo, utilizzare SQL Server Profiler per acquisire le classi di evento RPC:Completed e SQL:BatchCompleted.

In alternativa all'utilizzo di SQL Server Profiler, è possibile eseguire DBCC INPUTBUFFER ogni tre minuti per tutte le sessioni, come illustrato nell'esempio seguente.

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;

Sistema 2: Informazioni a livello di query

L'esame del buffer di input o l'evento SQL:BatchCompleted di SQL Server Profiler a volte non consentono di individuare la query che utilizza la maggiore quantità di spazio su disco in tempdb. Per individuare la query è possibile utilizzare i sistemi seguenti, che tuttavia richiedono la raccolta di una quantità di dati maggiore rispetto alle procedure illustrate per il sistema 1.

Per utilizzare questi sistemi, configurare un processo di SQL Server Agent che esegue il polling dalla vista a gestione dinamica sys.dm_db_task_space_usage. L'intervallo di polling deve essere più breve rispetto a quello del sistema 1, ovvero una volta al minuto, perché sys.dm_db_task_space_usage non restituisce dati se la query (attività) non è in esecuzione.

Nella query di polling, la vista definita nella vista a gestione dinamica sys.dm_db_task_space_usage viene unita in join con sys.dm_exec_requests per restituire le colonne sql_handle, statement_start_offset, statement_end_offset e 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

Se il piano di query è nella cache, è possibile recuperare in qualsiasi momento il testo Transact-SQL della query e il piano di esecuzione della query in formato Showplan XML. Per ottenere il testo Transact-SQL della query eseguita, utilizzare il valore sql_handle e la funzione a gestione dinamica sys.dm_exec_sql_text. Per ottenere il piano di esecuzione della query, utilizzare il valore plan_handle e la funzione a gestione dinamica sys.dm_exec_query_plan.

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

Se il piano della query non è nella cache, per ottenere il testo Transact-SQL e il piano di esecuzione della query, è possibile procedere in uno dei modi seguenti.

A. Utilizzo del metodo di polling

Eseguire il polling dalla vista all_query_usage e quindi eseguire la query seguente per ottenere il testo della query:

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 deve essere univoco per ogni singolo batch e pertanto non è necessario salvare le voci duplicate di sql_handle.

Per salvare l'handle del piano e il piano XML, eseguire la query seguente.

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. Utilizzo degli eventi di SQL Server Profiler

In alternativa al polling delle funzioni sys.dm_exec_sql_text e sys.dm_exec_query_plan, è possibile utilizzare gli eventi di SQL Server Profiler. Sono disponibili eventi di Profiler che consentono di acquisire il piano e il testo della query generato. Ad esempio l'evento 165 restituisce statistiche sulle prestazioni per la traccia, il testo SQL, i piani di query e statistiche relative alle query.

Monitoraggio dello spazio utilizzato dalle tabelle temporanee e dalle variabili di tabella

Per monitorare lo spazio utilizzato dalle tabelle e dalle variabili temporanee è possibile utilizzare un approccio simile a quello per il polling delle query. Le applicazioni che acquisiscono grandi quantità di dati utente all'interno di tabelle o variabili temporanee possono provocare problemi di utilizzo dello spazio in tempdb. Queste tabelle o variabili appartengono a oggetti utente. È possibile utilizzare le colonne user_objects_alloc_page_count e user_objects_dealloc_page_count della vista a gestione dinamica sys.dm_db_session_space_usage e utilizzare i sistemi descritti in precedenza.

Monitoraggio dell'allocazione e deallocazione di pagine per sessione

Nella tabella seguente sono illustrati i risultati restituiti dalle viste a gestione dinamica sys.dm_db_file_space_usage, sys.dm_db_session_space_usage e sys.dm_db_task_space_usage per una sessione specificata. Ogni riga rappresenta un'attività di allocazione o deallocazione eseguita in tempdb per una sessione specifica. L'attività è visualizzata nella colonna relativa all'evento. Nelle colonne rimanenti sono visualizzati i valori che verranno restituiti nelle colonne delle viste a gestione dinamica.

Per questo scenario, si supponga che il database tempdb contenga in origine 872 pagine in extent non allocati e 100 pagine in extent riservati per oggetti utente. Durante la sessione vengono allocate 10 pagine per una tabella utente, che vengono quindi deallocate. Le prime otto pagine appartengono a un extent misto e le due pagine rimanenti appartengono a un extent uniforme.

Evento	dm_db_file_space_usage colonna unallocated_extent_page_count	dm_db_file_space_usage colonna user_object_reserved_page_count	dm_db_session_space_usage e dm_db_task_space_usage colonna user_object_alloc_page_count	dm_db_session_space_usage e dm_db_task_space_usage colonna user_object_dealloc_page_count
Avvio	872	100	0	0
Allocazione della pagina 1 da un extent misto esistente	872	100	1	0
Allocazione delle pagine da 2 a 8: utilizzo di un nuovo extent misto	864	80	8	0
Allocazione della pagina 9: utilizzo di un nuovo extent uniforme	856	108	16	0
Allocazione della pagina 10 da un extent uniforme esistente	856	108	16	0
Deallocazione della pagina 10 da un extent uniforme esistente	856	108	16	0
Deallocazione della pagina 9 e dell'extent uniforme	864	100	16	8
Deallocazione della pagina 8	864	100	16	9
Deallocazione delle pagine da 7 a 1 e deallocazione su extent misto	872	100	16	16

Vedere anche

Condividi tramite