How to check how much TempDB space is used by a query in SQLSERVER in the database

This article mainly shows you "how to view a query in SQLSERVER in the database how much TempDB space is used", the content is easy to understand, well-organized, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to check how much TempDB space is used in a query in SQLSERVER in the database" this article.

In SQL Server, TempDB is mainly used for the following three types of situations:

Internal use (sorting, hash join, work table, etc.) external use (temporary tables, table variables, etc.) row versioning (optimistic concurrency control) while for internal use, some more complex queries require a lot of memory space because they involve a large number of parallel, sorting and other operations, each query will be estimated by SQL Server at the beginning of how much memory is required, in the specific execution process If the granted memory is insufficient, the excess needs to be handled by TempDB, which is called Spill to TempDB.

You can observe how many reads and writes a query causes to TempDB through the following statement:

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 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 DBFJOIN sys.dm_io_virtual_file_stats (2 NULL) AS FS ON FS.file_id = DBF.file_idWHERE DBF.type_desc = 'ROWS' are all the contents of the article "how to check how much TempDB space is used by a query in SQLSERVER in the database" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!