How to view the amount of TempDB space used in a query in SQLSERVER, sqlservertempdb

How to view the amount of TempDB space used in a query in SQLSERVER, sqlservertempdb

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

Internal use (sorting, hash join, work table, etc)
External Use (temporary tables, table variables, etc)
Row Version Control (Optimistic Concurrency Control)
 
For internal use, some complicated queries require a large amount of memory space because they involve a large number of parallel, sorting, and other operations, at the beginning of each query, SQL Server estimates the amount of memory required. During the execution process, if the allocated memory is insufficient, the extra memory needs to be processed by TempDB, this is the so-called Spill to TempDB.

The following statement can be used to observe the number of reads and writes caused by a query 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 dbfjoin sys. dm_io_virtual_file_stats (2, NULL) as fs on fs. file_id = DBF. file_idWHERE DBF. type_desc = 'rows '-- put the statement to be measured here 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'

The results of TempDB usage caused by bad queries recently seen by a customer are as follows:

 
You can use this query to learn how many TempDB statements are used.

How to query the SQL server tempdb in ram size and change the tempdb in ram

SQL Server 2000 uses SQL Server Cache Buffer Manager for processing TempDB, instead of using TempDB In RAM like SQL Server 7.0. In this way, In high concurrency, because SQL Server background processes cannot be scheduled in a timely manner, the capacity of TempDB increases rapidly (from the initial 8 MB to the 800 MB during performance testing ), as a result, the Cache Hit Ration decreases rapidly, resulting in the surging Pages Reads/Sec and Pages Writes/Sec of TempDB. The increase in TEMPDB also leads to the increase in memory usage. The use of memory and physical space resources leads to a reduction in the overall performance of the system. Solution: regularly contract the tempdb database. You can use the following methods. It is best to make a scheduled execution plan. method 1. Restart the SQL SERVER service ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev ', SIZE = SIZE to be reduced) alter database tempdb modify file (NAME = 'templog', SIZE = SIZE to be reduced) method 2. sp_spaceused @ updateusage = true dbcc shrinkdatabase (tempdb, '% ') method 3. dbcc shrinkfile (tempdev, 'size to be shrunk') dbcc shrinkfile (templog, 'size to be shrunk ')


How to "lose weight" for tempdb in SQL Server"

SQL Server automatically creates a database named tempdb for use as a workspace. When you CREATE a temporary TABLE during storage, for example (CREATE TABLE # MyTemp ), the SQL database engine creates the table in the tempdb database no matter which database you are using.
In addition, when you sort large result sets, such as using order by, group by, UNION, or executing a nested SELECT statement, if the data volume exceeds the system memory capacity, the SQL database engine creates a workbook in tempdb. When you run dbcc reindex or add a cluster sequence to an existing table, the SQL database engine also uses tempdb. In fact, any alter table command for large tables will eat a lot of disk space in tempdb.
Ideally, SQL will automatically clear and destroy these temporary tables after the specified operation is completed. However, many problems may cause errors. For example, if your code creates a transaction but fails to be executed or re-run, these orphan objects will be left in tempdb. In addition, when running dbcc check on large databases, it will consume a lot of space. You will often find that tempdb is much larger than expected, you may even receive an error message indicating that the SQL is about to run out of disk space.
You have many ways to correct this situation, but in the long run, you need to perform other steps to ensure normal use.
The easiest way to "lose weight" for tempdb is to disable the SQL database engine and restart it. However, this may be difficult for important tasks. On the other hand, if you are in an unbearable state, I suggest you tell your boss the bad news and start the operation.
If you are lucky to have another disk that can be used to store tempdb, you can perform the following operations:
USE master
GO
Alter database tempdb modify file (name = tempdev, filename = 'newdrive: Path empdb. mdf ')
GO
Alter database tempdb modify file (name = templog, filename = 'newdrive: Path emplog. ldf ')
GO
There are also three attributes for tempdb that should be checked: Automatic growth tag, initial size, and recovery mode. The following are tips for these attributes:
Auto-increment flag: Remember to set this flag to True.
Initial size: the initial size of tempdb should be set based on common workloads. If many users use group by, order by, or aggregate large tables, your common workload will be quite large. If the server is offline, you may need to check whether the log files and data files are on the same disk. If so, you should transfer them to the new disk, you only need to specify the corresponding database and use the same command.
Recovery mode: Set the recovery mode to True, which means that SQL will automatically intercept the log files of tempdb (after each table is used) and find the recovery mode used by tempdb, run the following command:
Select databasepropertyex ('tempdb', 'recovery ')
There are three options for recovery mode: simple, complete, or bulk-logged. To change the settings, run the following command:
Alter database tempdb SET RECOVERY SIMPLE
These steps can optimize the tempdb used in your system. In addition to solving disk space problems, you will also find that the SQL Server system performance is improved .... Remaining full text>