用动态管理视图监控SQL Server TempDB的教程在这篇文章中，我们将学习如何通过动态管理视图检测哪些操作会导致

在这篇文章中，我们将学习如何通过动态管理视图检测哪些操作会导致SQL Server tempdb被填满。

SQL Server tempdb的用途是什么？

Tempdb是系统数据库之一，它被SQL Server中的许多活动用来临时存储数据。例如，当用户创建一个临时表或声明一个表变量时，这些表所包含的数据将被存储在SQL Server tempdb数据库中。同时，tempdb可以被数据库引擎用于各种内部活动。下面的列表显示了使用tempdb数据库的最知名的操作。

常用表表达式（CTE）
临时表和表变量
哈希连接
触发器
游标
GROUP BY和ORDER BY语句
游标
在线索引
快照隔离级别

正如所见，tempdb承担了各种关键功能的责任，可能会影响到数据库引擎的性能。另一方面，在管理不善的SQL数据库系统中，tempdb数据库开始突然增长，如果不能准确检测到目标问题，这个问题可能会造成混乱的危机。现在让我们来学习一种方法，它有助于检测哪些操作会填满tempdb数据库。

首先看：sys.dm_db_file_space_usage

动态管理视图（DMVs）和函数（DMFs）被用来获取关于SQL Server活动的详细信息。该 sys.dm_db_file_space_usage 是DMV中的一个，它有助于监控关于SQL Server数据库的空间使用信息。该视图返回有关数据库文件的空间使用信息。该视图的主要特点是返回提供当前使用情况的时间点数据。下面的查询返回关于tempdb数据库的四个重要空间使用信息。

SELECT 
(SUM(unallocated_extent_page_count)*1.0/128) AS [Free space(MB)]
,(SUM(version_store_reserved_page_count)*1.0/128)  AS [Used Space by VersionStore(MB)]
,(SUM(internal_object_reserved_page_count)*1.0/128)  AS [Used Space by InternalObjects(MB)]
,(SUM(user_object_reserved_page_count)*1.0/128)  AS [Used Space by UserObjects(MB)]
FROM tempdb.sys.dm_db_file_space_usage;

空闲空间

未分配的空间报告了tempdb数据库中的可用空间。同时，这个值可以在tempdb数据库的属性上看到。

Tempdb database properties

通过VersionStore使用的空间

行versioning隔离级别允许我们克服读和写操作之间的冲突问题。在这些隔离级别中，工作原理是基于在tempdb中存储先前提交的行版本。version_store_reserved_page_count表示分配给版本存储的总页数。

提示：在SQL Server 2019中启用加速数据库恢复（ADR）功能将改变这种行为，行版本被保存在数据库文件组中。*

内部对象使用的空间

SQL Server使用SQL Server tempdb数据库，在执行查询时临时存储一些数据，以完成一些内部活动。如，表spool操作者在语句执行过程中会产生一个输入数据的副本，这个输入数据被存储在SQL Server tempdb中。下面的查询将在tempdb数据库中分配一些空间，因为有表spool操作符。

SELECT  TOP 150 SO.AccountNumber FROM
Sales.SalesOrderHeader SO
INNER  JOIN Sales.SalesOrderDetailEnlarged SD
ON SO.ModifiedDate = SD.ModifiedDate

tempdb数据库因为内部操作而使用的空间显示在internal_object_reserved_page_count列中。

用户对象使用的空间

最后，user_object_reserved_page_count 列表明当我们使用表变量、临时表和类似的用户对象时，分配了多少页。

按会话级别跟踪SQL Server TempDB的空间使用情况

dm_db_session_space_usage 是另一个 DMV，可以用来跟踪SQL Server tempdb中按会话级别分配和删除的页面数量。在这个有用的DMV的帮助下，我们可以发现在会话关闭之前，会话分配和删除了多少页。这个视图的主要优点是直接指向有问题的会话。现在让我们来看看这个视图在一个示例查询中的使用细节。首先，我们将启用会话的IO统计，然后启用实际的执行计划。

作为第二步，我们将执行查询，并点击消息标签，开始分析IO统计。

SELECT 
Sh.AccountNumber ,SUM(Sh.SubTotal),Sd.CarrierTrackingNumber
FROM Sales.SalesOrderHeader Sh
INNER JOIN Sales.SalesOrderDetail Sd
ON Sd.SalesOrderID =Sh.SalesOrderID
CROSS APPLY
(
    SELECT MAX(th.Quantity)
    FROM Production.TransactionHistory AS Th
    WHERE 
        TH.ModifiedDate = Sd.ModifiedDate
    GROUP BY ()
) AS th (Quantity)
GROUP BY Sh.AccountNumber ,Sd.CarrierTrackingNumber
ORDER BY Sd.CarrierTrackingNumber

在IO统计的输出文本中，我们看到了WorkTable和WorkFile表名。这两个表是在执行查询的过程中，当SQL Server需要将一些数据临时存储到tempdb中时，在tempdb中生成的。

在我们的例子中，我们看到从tempdb数据库中读取了524页。这时，一个新的问题出现在我们的脑海中："在tempdb数据库中进行这种读取操作的原因是什么？"。这个问题的答案隐藏在查询的执行计划中。

从执行计划中可以看出，在排序操作符上有一个警告标志，当我们用鼠标悬停在这个操作符上时，我们可以找出一些关于这个警告标志的原因。警告部分给了我们大量的细节，我们的问题与tempdb溢出有关。

TempDB溢出。查询优化器估计执行一个查询需要多少内存，然后将该请求的内存分配给该查询。然而，不准确的估计导致内存请求比实际需要的少。在这种情况下，SQL Server注意到它需要的内存比它被授予的多，这就是为什么它决定使用tempdb数据库。这种机制的主要缺点是使用磁盘资源总是比使用缓冲区内存慢，所以它对查询性能有负面影响。

***提示：*我们可以监控tempdb的溢出，以用于扩展事件 sort_warning和hash_warning事件。这个事件可以捕捉到当一个排序或哈希连接操作执行tempdb溢出的时候

SQL extended event hash_warning and sort_warning

这个事件的输出结果将是这样的。

SQL Server extended event sample

我们可以在dm_db_session_space_usage的帮助下监控这个内部SQL Server tempdb的使用。

select * from sys.dm_db_session_space_usage
where session_id =126

查询商店收集了关于查询和查询计划的各种指标，并通过不同的报告向用户提供这些数据。消耗资源最多的查询显示消耗资源最多的查询。我们可以根据不同的指标来组织这份报告。

Query store Top Resource Consuming Queries

Temp Db内存（KB）的使用是其中一个指标，我们可以获得有关在SQL Server tempdb数据库上产生工作负载的查询的信息。默认情况下，该报告以图表形式显示，但我们可以将其改为网格视图。

Find the tempdb memory usage through the query store

点击网格图标将改变报告的视图。

avg temp db内存使用列显示，一个查询在SQL Server tempdb数据库上消耗了多少内存。我们的样本查询消耗了4352KB内存，这个值可以通过dm_db_session_space_usage 视图进行修正**。**这个视图显示了544个被分配给这个查询的数量，单页大小为8kb。因此，当我们将这两个值相交（544*8=4352）时，我们可以找到临时数据库的总内存使用值。

提示： 我们在执行计划的排序操作员上没有看到警告标志，因为查询存储显示了估计的执行计划和在执行查询过程中出现的tempdb溢出问题

按任务级别跟踪SQL Server TempDB空间使用情况

SQL Server可以对堆表进行并行插入操作，这个功能对临时表有效。另一方面，sys.dm_db_task_space_usage 返回关于并行查询的每个任务所分配和删除的页面数量的信息。例如，当我们看下面的查询估计查询计划时，我们看到优化器决定了一个并行查询计划。

CREATE TABLE #TempTest
(CNumber  VARCHAR(100))
GO
INSERT INTO #TempTest WITH(TABLOCK)
SELECT CarrierTrackingNumber FROM Sales.SalesOrderDetailEnlarged

在这个查询的执行过程中，sys.dm_db_task_space_usage 显示每个任务分配了多少页。

总结

SQL Server tempdb是能够影响数据库性能的关键系统数据库之一。了解哪些DMV可以帮助监控tempdb数据库将有助于解决任何与tempdb有关的空间使用问题。