如何监视SQL Server tempdb数据库

When it comes to the monitoring of SQL Server system databases, the tempdb database is one of the most important for consideration, since it holds most of the internally created objects.

在监视SQL Server系统数据库时， tempdb数据库是需要考虑的最重要的数据库之一，因为它包含大多数内部创建的对象。

Beside some unique characteristics of the tempdb database itself (like version store, e.g.), which will be addressed later separately, its performance and space usage are crucial areas for monitoring (and tuning, as well), because the tempdb is the most active database and common resource in a SQL Server environment.

除了tempdb数据库本身的某些独特特征（例如版本存储 ）（将在以后单独解决）之外，它的性能和空间使用情况是监视（以及调整）的关键领域，因为tempdb是最活跃的数据库和SQL Server环境中的公共资源。

Generally, configuration and performance of the tempdb database depend heavily on the environment itself (physical resources), workload (amount of parallel operations like creation of temporary objects, e.g.) and applications which use SQL Server resources.

通常，tempdb数据库的配置和性能在很大程度上取决于环境本身（物理资源），工作负载（例如，创建临时对象的并行操作的数量）以及使用SQL Server资源的应用程序。

This article will focus only on basic tempdb monitoring techniques, regarding the tempdb disk space usage by its database files, along with some specific information about the database itself (creation date and recovery model), and review the space usage of tempdb specific elements like user objects, internal objects and version store.

本文仅关注基本的tempdb监视技术，涉及数据库文件对tempdb 磁盘空间的使用情况 ，以及有关数据库本身的一些特定信息（创建日期和恢复模型），并回顾tempdb特定元素（如user） 的空间使用情况对象，内部对象和版本存储。

监视tempdb数据库文件并收集其他信息 (Monitor tempdb database files and gather other information)

To monitor the SQL Server tempdb database statistics, you can execute the following query. The first part of the query reveals the currently allocated size of data file, log file and space used by log file:

若要监视SQL Server tempdb数据库统计信息，可以执行以下查询。 查询的第一部分显示了数据文件，日志文件和日志文件使用的空间的当前分配大小：

 
--First part of the script
SELECT instance_name AS 'Database',
[Data File(s) Size (KB)]/1024 AS [Data file (MB)],
[Log File(s) Size (KB)]/1024 AS [Log file (MB)],
[Log File(s) Used Size (KB)]/1024 AS [Log file space used (MB)]
FROM (SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN
('Data File(s) Size (KB)',
'Log File(s) Size (KB)',
'Log File(s) Used Size (KB)')
AND instance_name = 'tempdb') AS A
PIVOT
(MAX(cntr_value) FOR counter_name IN
([Data File(s) Size (KB)],
[LOG File(s) Size (KB)],
[Log File(s) Used Size (KB)])) AS B
GO
--
--Second part of the script
SELECT create_date AS [Creation date],
recovery_model_desc [Recovery model]
FROM sys.databases WHERE name = 'tempdb'
GO
 

The second part shows exactly when the tempdb was created, and which recovery model it is utilizing

第二部分准确显示了tempdb的创建时间以及所使用的恢复模型

To get the total database size without details, use this query:

要获取没有详细信息的数据库总大小，请使用以下查询：

 
SELECT SUM(size)/128 AS [Total database size (MB)]
FROM tempdb.sys.database_files
 

Since SQL Server automatically creates the tempdb database from scratch on every system starting, and the fact that its default initial data file size is 8 MB (unless it is configured and tweaked differently per user’s needs), it is easy to review and monitor database files statistics by using the query above. In this case, the default initial size value is set, and the result shows that database expanded by a factor of five times, and this total database size points on light to medium activity in a longer time span of a specified SQL server where tempdb resides.

由于SQL Server会在每个系统启动时从头开始自动创建tempdb数据库，并且其默认初始数据文件大小为8 MB（除非根据每个用户的需求对其进行了配置和调整），因此易于查看和监视数据库文件通过使用上面的查询进行统计。 在这种情况下，将设置默认的初始大小值，结果表明数据库扩展了五倍，并且该数据库的总大小指向了tempdb所在的指定SQL服务器的较长时间范围内的轻度到中等活动。

If needed, as for any other regular database, users can monitor indexes contained in the tempdb database.

如果需要，对于任何其他常规数据库，用户可以监视 tempdb数据库中包含的索引 。

监视特定的tempdb对象空间使用情况 (Monitor the specific tempdb objects space usage)

These objects are:

这些对象是：

• database free space,
  数据库可用空间，

and three elements, unique to tempdb:

和tempdb特有的三个元素：

• internal objects – created by SQL Server to process queries (online index rebuild, system tables and view retrieval, hash join etc.)
  内部对象-由SQL Server创建以处理查询（在线索引重建，系统表和视图检索，哈希联接等）
• user objects – created by users (temporary tables, table variables, etc.)
  用户对象–由用户创建（临时表，表变量等）
• row versioning feature in SQL Server (triggers, e.g.) 行版本控制功能的应用程序（例如，触发）是必需的

Use the following query to obtain information on space usage by specific tempdb objects:

使用以下查询来获取有关特定tempdb对象的空间使用情况的信息：

 
SELECT 
(SUM(unallocated_extent_page_count)/128) AS [Free space (MB)],
SUM(internal_object_reserved_page_count)*8 AS [Internal objects (KB)],
SUM(user_object_reserved_page_count)*8 AS [User objects (KB)],
SUM(version_store_reserved_page_count)*8 AS [Version store (KB)]
FROM sys.dm_db_file_space_usage
--database_id '2' represents tempdb
WHERE database_id = 2
 

As previously mentioned, the tempdb used for the reference in this article is placed on SQL Server with generally light activity, and currently, none of the row versioning functionalities are used (Version store (KB) column).

如前所述，本文中用于参考的tempdb放置在SQL Server上，活动通常很少，并且当前未使用任何行版本控制功能（“ 版本存储（KB）”列）。

监视临时表空间使用情况 (Monitor temporary tables space usage)

Additionally, if a large amount of data is stored in temporary tables due to the higher SQL server activity, check the number of rows, and used/reserved space of each of temporary tables which had been created in a specific database:

此外，如果由于较高SQL Server活动而在临时表中存储了大量数据，请检查已在特定数据库中创建的每个临时表的行数以及已使用/保留的空间：

 
USE <database_name>
SELECT tb.name AS [Temporary table name],
stt.row_count AS [Number of rows], 
stt.used_page_count * 8 AS [Used space (KB)], 
stt.reserved_page_count * 8 AS [Reserved space (KB)] FROM tempdb.sys.partitions AS prt 
INNER JOIN tempdb.sys.dm_db_partition_stats AS stt 
ON prt.partition_id = stt.partition_id 
AND prt.partition_number = stt.partition_number 
INNER JOIN tempdb.sys.tables AS tb 
ON stt.object_id = tb.object_id 
ORDER BY tb.name
 

In this case, we used custom Test database. Temporary objects marked above in the screenshot are user-created tables.

在这种情况下，我们使用了自定义的Test数据库。 屏幕快照上方标记的临时对象是用户创建的表。

Note that all temporary objects will be removed on SQL Server service or machine restart, and information on them can be retrieved only during the active user session.

请注意，所有临时对象都将在SQL Server服务或计算机重新启动时删除，并且仅在活动用户会话期间才能检索有关它们的信息。

翻译自: https://www.sqlshack.com/monitor-sql-server-tempdb-database/