How to see how much tempdb space is used for a query

Recently, in the process of helping customers to tune, found that the customer's tempdb there is a very large pressure, after the investigation is found that some statements on the huge use of tempdb caused.

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.

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

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< C14/>dbf.type_desc = ' ROWS '
    
--here put the statement you want to measure
    
SELECT  tempdb_read_mb = (SUM (num_of_bytes_read)-@read)/ 1024./1024., 
        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.