Risoluzione dei problemi relativi allo spazio su disco insufficiente 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;

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;

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.

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;

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;

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

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

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.

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;

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;

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.