Passa al contenuto

#temp tables…to drop or not to drop

In questo articolo andremo ad approfondire l’impatto che l’eliminazione di tabelle temporanee precedentemente create può avere sul database di sistema tempdb, introducendo il concetto di Contesa a livello di metadati (METADATA CONTENTION). 

Molto spesso, tipicamente all’interno di stored procedure, siamo abituati a creare tabelle temporanee e poi alla fine della procedura diligentemente provvediamo a fare il drop di queste tabelle (DROP TABLE #temptable). Sebbene questa possa sembrare una buona pratica per ripulire le risorse, è consigliabile non eliminare le tabelle temporanee alla fine della sessione, ma lasciare tale compito a SQL Server. 

Quando decidiamo di eliminare delle tabelle temporanee, SQL Server deve acquisire un latch (si tratta di un lock “leggero”) sulle tabelle di oggetti di sistema del tempdb per controllare e inserire dei metadati relativi appunto a queste tabelle temporanee. In un ambiente ad alta concorrenza, quando più sessioni tentano di eliminare molte tabelle temporanee, queste operazioni DDL devono rimanere in attesa per poter acquisire un PAGELATCH (_EX, _SH) sulle tabelle degli oggetti di sistema. Queste attese portano a fenomeni di contesa su pagine del database tempdb, impattando in modo significativo sulle performance. 

Osservando questa immagine, si può notare come tutte le eliminazioni cerchino di accedere alla tabella di sistema sysobjvalues, causando contesa sulla tabella. 

contesa tabella

In realtà ci sono 12 di queste tabelle di metadati a cui le operazioni DDL devono accedere, proprio come quella sopra. Ecco l'elenco delle 12 tabelle di sistema in cui si verifica una contesa a causa delle operazioni DDL.

tabelle di sistema

A questo punto vediamo concretamente un esempio di quanto stiamo argomentando. 

Creiamo questa stored procedure su un database di prova:

USE [prova]
GO

CREATE
	OR

ALTER PROC [dbo].[DropTempTable]
AS
BEGIN
	CREATE TABLE #TempTable (ID INT NULL)

	INSERT INTO #TempTable
	VALUES (1)

	DROP TABLE #TempTable
END
GO

Ora proveremo a generare molte esecuzioni di questa stored procedure in modo da simulare un ambiente ad alta concorrenza. 

Utilizziamo un comodo strumento per simulare situazioni di stress dell’istanza SQL Server: SQLQueryStress (https://github.com/ErikEJ/SqlQueryStress).

simulazioni di stress

Impostiamo il comando di esecuzione della stored procedure, configurando lo strumento per generare 1000 iterazioni su 50 threads simultanee. 

In contemporanea con l’esecuzione dello stress test utilizziamo questa query specifica per valutare eventuali fenomeni di PAGELATCH su database tempdb 

USE master
GO

SELECT er.session_id
	,er.wait_type
	,er.wait_resource
	,OBJECT_NAME(page_info.[object_id], page_info.database_id) AS [object_name]
	,page_info.page_type_desc
	,er.blocking_session_id
	,er.command
	,SUBSTRING(st.TEXT, (er.statement_start_offset / 2) + 1, (
			(
				CASE er.statement_end_offset
					WHEN - 1
						THEN DATALENGTH(st.TEXT)
					ELSE er.statement_end_offset
					END - er.statement_start_offset
				) / 2
			) + 1) AS statement_text
	,page_info.database_id
	,page_info.[file_id]
	,page_info.page_id
	,page_info.[object_id]
	,page_info.index_id
	,CASE 
		WHEN page_info.page_type_desc IN (
				'SGAM_PAGE'
				,'GAM_PAGE'
				,'PFS_PAGE'
				)
			AND WAIT_TYPE IN (
				'PAGELATCH_SH'
				,'PAGELATCH_UP'
				,'PAGELATCH_EX'
				)
			THEN 'ALLOCATION CONTENTION'
		WHEN page_info.page_type_desc IN (
				'DATA_PAGE'
				,'INDEX_PAGE'
				)
			AND WAIT_TYPE IN (
				'PAGELATCH_SH'
				,'PAGELATCH_UP'
				,'PAGELATCH_EX'
				)
			THEN 'METADATA CONTENTION'
		END AS allocation_type
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
CROSS APPLY sys.fn_PageResCracker(er.page_resource) AS r /*database ID, file ID, page ID SQL2019*/
CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, 'DETAILED') AS page_info /*replace dbcc page SQL2019*/
WHERE er.wait_type LIKE 'PAGELATCH%'
	AND command LIKE 'DROP TABLE'
GO

Il risultato che ci si presenta è il seguente:

pagelatch tempdb

Vediamo molti PAGELATCH_EX o _SH causati dal comando di DROP TABLE presente all’interno della nostra stored procedure. 

Ora proviamo a modificare la stored procedure commentando la riga relativa al DROP TABLE :

USE [prova]
GO

CREATE
	OR

ALTER PROC [dbo].[DropTempTable]
AS
BEGIN
	CREATE TABLE #TempTable (ID INT NULL)

	INSERT INTO #TempTable
	VALUES (1)
		--DROP TABLE #TempTable  
END
GO

e ripetiamo lo stress test con SQLQueryStress (sempre con impostazione 1000 iterazioni su 50 threads simultanee) e contemporaneamente eseguiamo la nostra query di verifica.

query di verifica

Nessun fenomeno di PAGELATCH generato, nel corso dello stress test. Le tabelle temporanee vengono eliminate in modo autonomo da SQL Server alla chiusura della sessione che le ha create. 

PS: in realtà anche il comando di CREATE TABLE #TempTable genera a sua volta dei PAGELATCH di contesa su tempdb. Tuttavia, se vogliamo utilizzare tabelle temporanee, non possiamo evitare il comando di CREATE. Possiamo invece evitare il comando di DROP e delegare questo automatismo a SQL Server. 

Dietro le quinte, SQL Server utilizza un algoritmo di Optimistic Latching per eliminare le tabelle in modo asincrono, il che significa che il processo di pulizia viene gestito in base al numero di operazioni di CREATE e ad alcuni altri fattori, in modo da evitare conflitti su tempdb. 


Conclusione 

In questo articolo abbiamo visto come verificare e ridurre possibili fenomeni di METADATA CONTENTION sul database tempdb. Esiste un altro fenomeno di contesa a livello di tempdb e si chiama Contesa a livello di allocazione (ALLOCATION CONTENTION) e riguarda alcune pagine particolari (PFS, GAM, SGAM), ma non è oggetto di questo articolo. 

Possiamo però facilmente capire l’importanza e la criticità del database tempdb a livello di performance generale del sistema e del motivo per cui è estremamente importante configurare tale database secondo le migliori best practice e cercare di utilizzarlo nel modo più opportuno. 

 

di Matteo Dal Bianco, pubblicato il 17 giugno 2025.

Hyla Festival 2025