How to see how much tempdb space a query uses

Tag: Data read-write URI Object memory virtual ima pressure sum

During the recent tuning process for customers, it was found that there was a lot of pressure on the customer's tempdb, which was found to be caused by the sheer use of certain statements for tempdb.

In SQL Server, tempdb is primarily responsible for the following three types of situations:

• Internal use (sort, hash join, work table, etc.)
• External use (Temporary tables, table variables, etc.)
• Row version control (optimistic concurrency control)

For internal use, some of the more complex queries involved in a large number of parallel, sort, and other operations require a lot of memory space, each query at the beginning will be estimated by SQL Server how much memory, in the specific execution process, if the grant of insufficient memory, The extra part needs to be handled by TempDB, which is known as spill to tempdb.

The following statement allows you to see how much read and write a query has caused to tempdb:

DECLARE @Read BIGINT, @Write BIGINT;SELECT @Read =SUM (Num_of_bytes_read), @Write =SUM (Num_of_bytes_written)From Tempdb.sys.database_filesAs DBFJOIN Sys.dm_io_virtual_file_stats (2,NULL)As FSOn fs.file_id = dbf.file_idWHERE Dbf.type_desc =' ROWS '
--Put the statement that needs to be measured
SELECT TEMPDB_READ_MB = (sum (num_of_bytes_read)-@Read)/1024x768./1024x768., TEMPDB_WRITE_MB = (SUM (Num_of_bytes_written)-@Write)/ 1024x768./1024x768, INTERNAL_USE_MB = ( SELECT internal_objects_alloc_page_count/128.0 from Sys.dm_db_task_space_usa GE WHERE session_id = @ @SPID) from tempdb.sys.database_files as DBF JOIN Sys.dm_io_virtual_file_sta TS (2, NULL) as FS on fs.file_id = dbf.file_id WHERE dbf.type_desc = ' ROWS ' 

The results of tempdb usage resulting from a bad query that was recently seen in a customer are as follows:

Using this query can help you understand how much tempdb is used by a statement.

How to see how much tempdb space a query uses