监测谁用了SQL Server的Tempdb空间

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,独享型 2核4GB
简介: 原文: 监测谁用了SQL Server的Tempdb空间 转自:http://blogs.msdn.com/b/apgcdsd/archive/2011/02/11/sql-server-tempdb.aspx Tempdb 系统数据库是一个全局资源,供连接到 SQL Server 实例的所有用户使用。
原文: 监测谁用了SQL Server的Tempdb空间

转自:http://blogs.msdn.com/b/apgcdsd/archive/2011/02/11/sql-server-tempdb.aspx

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的申请.

go

select getdate()

-- join 语句做完以后internal objects page数目应该下降

go

 

那用什么脚本可用监视上面的行为呢?下面的脚本就可以监视和发现当前的Tempdb使用者。这个脚本需要在使用tempdb的语句开始运行之前开始。(读者当然可以根据自己的喜好,修改这个脚本。)

脚本首先用“dbcc showfilestats”语句查询当前tempdb的总体使用量。再查询sys.dm_db_file_space_usage视图,得到Tempdb里当前总共有多少用户对象、内部对象、以及版本存储。然后查询sys.dm_db_session_space_usage和sys.dm_exec_sessions,找到当前使用Tempdb的所有连接。最后通过sys.dm_exec_sql_text,找到这些连接正在运行的语句。

 

use tempdb                                                               

-- 每隔1秒钟运行一次,直到用户手工终止脚本运行

while 1=1                                                               

begin                                                                   

select getdate()                                                        

-- 从文件级看tempdb使用情况

dbcc showfilestats                                                      

-- Query 1

-- 返回所有做过空间申请的session信息

Select 'Tempdb' as DB, getdate() as Time,                                                        

    SUM (user_object_reserved_page_count)*8 as user_objects_kb,         

    SUM (internal_object_reserved_page_count)*8 as internal_objects_kb, 

    SUM (version_store_reserved_page_count)*8  as version_store_kb,     

    SUM (unallocated_extent_page_count)*8 as freespace_kb               

From sys.dm_db_file_space_usage                                         

Where database_id = 2                                                    

-- Query 2

-- 这个管理视图能够反映当时tempdb空间的总体分配

SELECT t1.session_id,                                                   

t1.internal_objects_alloc_page_count,  t1.user_objects_alloc_page_count,

t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count,

t3.*

from sys.dm_db_session_space_usage  t1 ,                               

-- 反映每个session累计空间申请

sys.dm_exec_sessions as t3

-- 每个session的信息

where

t1.session_id = t3.session_id

and (t1.internal_objects_alloc_page_count>0

or t1.user_objects_alloc_page_count >0

or t1.internal_objects_dealloc_page_count>0

or t1.user_objects_dealloc_page_count>0)

-- Query 3

-- 返回正在运行并且做过空间申请的session正在运行的语句

SELECT t1.session_id,                                                    

st.text                                                        

from sys.dm_db_session_space_usage as t1,                               

sys.dm_exec_requests as t4                                              

CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st                   

 where  t1.session_id = t4.session_id                                       

   and t1.session_id >50                                                

and (t1.internal_objects_alloc_page_count>0

or t1.user_objects_alloc_page_count >0

or t1.internal_objects_dealloc_page_count>0

or t1.user_objects_dealloc_page_count>0)                                              

waitfor delay '0:0:1'                                                    

end                          

  

图1

 

在运行这个脚本的连接(连接B)里(图1),我们选择好“Result to File”。先开始运行它,指定输出文件路径。然后,我们再运行连接A(图2)。连接A运行结束后,手工停止连接B的运行。

  

图2

 

在连接A的结果中(),可以得到四个时间。图片上的例子,是:

11:39:36.513     -- 开始创建temp table

11:39:38.920 – 开始删除temp table

11:39:40.937 – 开始查询

11:39:45.733 – 查询结束

 

 连接B生成的是一个文本文件。利用一些有“列出所有包含某个特定字符串”行功能的编辑器工具,可以把每个命令结果挑出来。

 从连接B生成的文本文件里所有dbcc showfilestats的结果(图3),可以看出tempdb的使用空间有过两次增长(从23到210,从47到118),中间有一次下降(从210到47)。

 

图3

  

从连接B生成的文本文件里所有Query 1的结果(图3),我们可以看到有三段时间,user object和internal object空间有申请和释放动作。它们分别是11:39:36 – 11:39:37 (user_objects_kb增长),11:39:40 – 11:39:41 (user_objects_kb下降),11:39:40 – 11:39:43(internal_objects_kb增长)。

 

图4

  

从Query 2的结果(图4)可以看到Connection A在这三个时间段都处于运行状态。

  

图5

  

根据时间,可以从Query 3的结果(图5)里找到Connection A当时正在运行的语句。例如在11:39:40 – 11:39:43(internal_objects_kb增长)这段时间里,一直都在运行下面这句话:

 

图6

  

从上面的结果可以看出,连接A的语句中,用tempdb最多的时间点在11:39:41和11:39:42之间,连接正在做图6里面的那条查询语句。SQL Server需要空间存放一些内部对象,来完成Inner Join。

相关实践学习
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
27天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
1月前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
253 1
|
2月前
|
SQL API 流计算
实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
175 1
|
5天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
11天前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
21 2
|
16天前
|
SQL 存储 测试技术
|
15天前
|
SQL 机器学习/深度学习 搜索推荐
SQL SERVER 转换失败
【6月更文挑战第25天】
|
20天前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之如何迁移SQL Server
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
28天前
|
SQL 存储 关系型数据库
关系型数据库中的SQL Server
【6月更文挑战第11天】
55 3
|
27天前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程