如何监测谁用了SQL Server的Tempdb空间？
Tempdb 系统数据库是一个全局资源，供连接到 SQL Server 实例的所有用户使用。在现在的 SQL Server 里，其使用频率可能会超过用户的想象。如果 Tempdb 空间耗尽，许多操作将不能完成。 作为一个支持工程师，会被经常问到象“我的 Tempdb 为什么这么大？”“是谁把我的 Tempdb 空间用完的？”在 SQL 2000 的时候，这个问题很难回答。好在 SQL 2005 以后，引入了一 张新的管理视图： sys.dm_db_file_space_usage 。通过查询这张视图，能了解 tempdb 的空间使用情况，能知道 tempdb 的空间是被哪一块对象使用掉的，是用户对象 (user_object_reserved_page_count 字段 ) ，还是系统对象（ internal_object_reserved_page_count 字段），还是版本存储区（ version_store_reserved_page_count 字段）。 在讨论 Tempdb 空间使用之前，我们先简单介绍一下通常什么操作会大量使用 Tempdb 。在 SQL 2005 和 SQL 2008 里，使用 Tempdb 空间的远远不止是临时表。常见的使用对象有： 用户对象 (user_object_reserved_page_count) 用户对象由用户显式创建。这些对象可以位于用户会话的作用域中，也可位于创建对象所用例程的作用域中。 可以是存储过程、触发器或用户定义函数。 用户对象可以是下列项之一： 用户定义的表和索引 系统表和索引 全局临时表和索引 局部临时表和索引 table 变量 表值函数中返回的表 内部对象 （ internal_object_reserved_page_count ） 内部对象是根据需要由 SQL Server 数据库引擎创建的，用于处理 SQL Server 语句。 内部对象可以在语句的作用域中创建和删除。 内部对象可以是下列项之一： 用于游标。 用于哈希联接或哈希聚合操作的查询。 某些 GROUP BY 、 ORDER BY 或 UNION 查询的中间排序结果。 版本存储 （ version_store_reserved_page_count ） 版本存储区主要用来支持 Snapshot 事务隔离级别，以及 SQL 2005 以后推出的一些其他提高数据库并发度的新功能。 由此可见， 光从用户发过来的语句本身，是很难判断这个连接的操作是否会使用 Tempdb 的。 一个典型的例子，就是某些查询。如果表格上有良好的索引做支持， SQL Server 不需要做 哈希联接 (Hash Join) ，那这个查询就不会用 Tempdb 。反之，如果表格很大，又没有好的索引，那 Tempdb 使用量就可能不小。 tempdb 空间使用的一大特点，是只有一部分对象，例如用户创建的临时表、 table 变量等，可以用 sys.allocation_units 和 sys.partitions 这样的管理视图来管理。许多内部对象和版本存储在这些管理视图里没有体现。所以， sp_spaceused 的结果和真实使用会有很大差异， tempdb 的空间使用是不能用 sp_spaceused 来跟踪的。必须借助 sys.dm_db_file_space_usage 这样的管理视图和管理函数，才能看到全貌。 下面以一个实例，讨论一下如何用 DBCC 命令、管理视图 (DMV) 以及管理函数 (DMF) 来监视是什么语句正在使用 tempdb 。 为了使结果简单，我们在测试之前先把 SQL Server 重起一次。 然后我们在 Management Studio 里做一个连接（连接 A ），将下面语句输入。这些语句会使用 tempdb 的空间。 select @@spid go use adventureworks go select getdate() go select * into #mySalesOrderDetail from Sales.SalesOrderDetail — 创建一个 temp table — 这个操作应该会申请 user objects page go waitfor delay ’0:0:2′ select getdate() go drop table #mySalesOrderDetail — 删除一个 temp table — 这个操作后 user object page 数量应该会下降 go waitfor delay ’0:0:2′ select getdate() go select top 100000 * from [Sales].[SalesOrderDetail] INNER JOIN [Sales].[SalesOrderHeader] ON [Sales].[SalesOrderHeader] .[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]; — 这里做了一个比较大的 join. — 应该会有 internal objects 的申请 .
Read the original post:
如何监测谁用了SQL Server的Tempdb空间？
