How to view how much tempdb space _mssql a query in SQL Server

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 because of a large number of parallel, sorting and other operations require a large amount of memory space, each query at the beginning will be estimated by SQL Server how much memory, in the specific implementation of the process, if the grant of insufficient memory, You will need to add the extra parts by TempDB, which is called spill to tempdb.

The following statement allows you to observe how much reading and writing 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_ Files as DBF
JOIN  sys.dm_io_virtual_file_stats (2, NULL) as FS on
    fs.file_id = dbf.file_id
WHERE  dbf.type_desc = ' ROWS '

--here put the statement you want to measure

SELECT tempdb_read_mb = (SUM (num_of_bytes_read)-@read)/1024./1 024., 
    tempdb_write_mb = (SUM (num_of_bytes_written)-@write)/1024./1024.,
    internal_use_mb = 
      (
      SELECT internal_objects_alloc_page_count/128.0
      from  sys.dm_db_task_space_usage
      WHERE  session_id = @ @SPID
      )
from  tempdb.sys.database_files as DBF
JOIN  sys.dm_io_virtual_file_ Stats (2, NULL) as FS on
    fs.file_id = dbf.file_id
WHERE  dbf.type_desc = ' ROWS '

The results of the tempdb usage resulting from a bad query recently seen at a customer are as follows:

Use this query to help you understand how much tempdb is used by a statement.