如何监测谁用了SQL Server的Tempdb空间(三)
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC
WAITFOR DELAY '0:0:1'
END
www.2cto.com
在运行这个脚本的连接（连接B）里，我们选择好“ 将结果保存到文本”。先开始运行它，指定输出文件路径。然后，我们再运行连接A。连接A运行结束后，手工停止连接B的运行。
www.2cto.com
连接B生成的是一个文本文件。文本里面可以看出tempdb的使用空间有过增长和下降。结果我在这里不做过多的分析，有兴趣的可以尝试一下
如果没有太好的分析，可以创建几个表，然后把查询结果插入到表里面，然后对表进行分析。从而来确定导致你tempdb 暴增的原因。下面附上建表的脚本，供大家参考使用
[sql]
-- 1
-- 创建表 tb_showfilestats 记录 DBCC showfilestats 返回的信息
IF OBJECT_ID('tb_showfilestats' , 'U') IS NOT NULL
DROP TABLE tb_showfilestats
GO
CREATE TABLE tb_showfilestats
(
id INT IDENTITY(1,1) PRIMARY KEY,
Fileid INT ,
FileGroup INT ,
TotalExtents INT ,
UsedExtents INT ,
Name VARCHAR(100) ,
FILENAME VARCHAR(255)
)
GO
-- 2
-- 创建表 tb_db_file_space_usage 记录 所有做过空间申请的 session 信息
IF OBJECT_ID('tb_db_file_space_usage' , 'U') IS NOT NULL
DROP TABLE tb_db_file_space_usage
GO
CREATE TABLE tb_db_file_space_usage
(
id INT IDENTITY(1,1) PRIMARY KEY,
database_name VARCHAR(50) ,
InDate DATETIME ,
user_objects_mb DECIMAL(18,4) ,
internal_objects_mb DECIMAL(18,4) ,
version_store_kb DECIMAL(18,4) ,
freespace_mbFILENAME DECIMAL(18,4)
)
GO
-- 3
-- 创建表 tb_db_session_space_usage 记录正在运行的 tempdb 空间的总体分配
IF OBJECT_ID('tb_db_session_space_usage' , 'U') IS NOT NULL
DROP TABLE tb_db_session_space_usage
GO
CREATE TABLE tb_db_session_space_usage
(
id INT IDENTITY(1,1) PRIMARY KEY,
session_id INT ,
internal_objects_alloc_page_count INT ,
user_objects_alloc_page_count INT ,
internal_objects_dealloc_page_count INT ,
user_objects_dealloc_page_count INT ,
login_time DATETIME ,
login_name VARCHAR(100) ,
host_name NVARCHAR(128) ,
nt_domain NVARCHAR(128) ,
nt_user_name NVARCHAR(128) ,
program_name NVARCHAR(128) ,
status VARCHAR(50) ,
client_interface_name NVARCHAR(32) ,
cpu_time INT ,
memory_usage INT ,
total_scheduled_time INT ,
total_elapsed_time INT ,
last_request_start_time DATETIME ,
last_request_end_time DATETIME ,
reads INT ,
writes INT ,
logical_reads INT ,
is_user_process BIT ,
row_count BIGINT ,
prev_error INT ,
original_security_id VARBINARY(85) ,
original_login_name NVARCHAR(128) ,
last_successful_logon DATETIME ,
last_unsuccessful_logon DATETIME ,
unsuccessful_logons BIGINT ,
group_id INT
)
GO
-- 4 www.2cto.com
-- 记录正在运行并且做过空间申请的session正在运行的语句.
IF OBJECT_ID('tb_db_sql_text' , 'U') IS NOT NULL
DROP TABLE tb_db_sql_text
GO
CREATE TABLE tb_db_sql_text
(
id INT IDENTITY(1,1) PRIMARY KEY,
session_id VARCHAR(50) ,
text VARCHAR(MAX),
InDate DATETIME
)
GO
-- 5
-- 记录正在运行的活动的空间使用情况以及语句内容和执行计划.
IF OBJECT_ID('tb_task_space_