[Reprint] How to see how much tempdb space a query uses

Http://www.cnblogs.com/CareySon/p/3910337.html

The following script allows you to view the space of a query's useful tempdb.

The first step is to find out what the current tempdb utility space is,

The second step is to execute a query statement

The third step is to use the yourselves tempdb space-the first step to tempdb practical space.

DECLARE @read BIGINT, @write BIGINT; SELECT @read=SUM (num_of_bytes_read), @write=SUM (Num_of_bytes_written) from Tempdb.sys.database_files as Dbfjoin sys.dm_io_virtual_file_stats (2, NULL) As FS on fs.file_id=Dbf.file_idwhere Dbf.type_desc='ROWS'--here, 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.0From sys.dm_db_task_space_usage WHERE session_id=@ @SPID) From Tempdb.sys.database_files as Dbfjoin sys.dm_io_virtual_file_stats (2, NULL) As FS on fs.file_id=Dbf.file_idwhere Dbf.type_desc='ROWS'

View Code

[Reprint] How to see how much tempdb space a query uses