合 MSSQL如何定位大量占用tempdb的SQL语句

发布日期 2023年4月28日 · 已更新 2024年5月16日

0 185 3

Tags： MSSQL SQL Server 慢SQL tempdb

👉 本文共约1176个字，系统预计阅读时间或需5分钟。

简介
查看tempdb记录的分配情况
补充tempdb相关sql
tempdb空间使用跟踪（不要用sp_spaceused)
获取每个会话对tempdb用户对象和内部对象的使用空间
查占用 tempdb的sql
示例
job定期收集tempdb的使用情况（每隔10分钟收集1次，占用大于1GB的会话情况）
查询存储获取tmpdb的占用（从2017开始）
参考

简介

若SQL在运行，则直接去查询会话即可。

若SQL已完成，则可以通过sys.dm_db_session_space_usage查询，但是时间长也不会保存

若2017版本，则可以使用查询存储来获取，若其它版本可以通过JOB定时来获取。

查看tempdb记录的分配情况

如果查询上面的DMV距事件发生时间太久，可能就查不到了，要尽快查看

use tempdb
go
SELECT top 10 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.login_name,t3.login_time,t3.last_request_start_time,t3.status,t3.total_elapsed_time
from sys.dm_db_session_space_usage  t1
inner join sys.dm_exec_sessions as t3
on t1.session_id = t3.session_id
where (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)
order by t1.internal_objects_alloc_page_count desc

SELECT top 100 t1.session_id,                                                   
t1.internal_objects_alloc_page_count*8/1024 internal_objects_alloc_mb, 
t1.user_objects_alloc_page_count*8/1024 user_objects_alloc_mb,
t1.internal_objects_dealloc_page_count*8/1024 internal_objects_dealloc_mb ,
t1.user_objects_dealloc_page_count*8/1024 user_objects_dealloc_mb,
((t1.internal_objects_alloc_page_count+t1.user_objects_alloc_page_count)- 
(t1.internal_objects_dealloc_page_count+t1.user_objects_dealloc_page_count))*8/1024 now_tempdb_mb,
t3.login_name,t3.login_time,t3.last_request_start_time,t3.status,t3.total_elapsed_time
from sys.dm_db_session_space_usage  t1
inner join sys.dm_exec_sessions as t3
on t1.session_id = t3.session_id
where (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)
and t3.status not in ('sleeping')
order by (t1.internal_objects_alloc_page_count+t1.user_objects_alloc_page_count)- 
(t1.internal_objects_dealloc_page_count+t1.user_objects_dealloc_page_count) desc,
(t1.internal_objects_alloc_page_count+t1.user_objects_alloc_page_count) desc

use tempdb

SELECT top 10 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.login_name,t3.login_time,t3.last_request_start_time,t3.status,t3.total_elapsed_time

from sys.dm_db_session_space_usage t1

inner join sys.dm_exec_sessions as t3

on t1.session_id = t3.session_id

where (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)

order by t1.internal_objects_alloc_page_count desc

SELECT top 100 t1.session_id,

t1.internal_objects_alloc_page_count*8/1024 internal_objects_alloc_mb,

t1.user_objects_alloc_page_count*8/1024 user_objects_alloc_mb,

t1.internal_objects_dealloc_page_count*8/1024 internal_objects_dealloc_mb ,

t1.user_objects_dealloc_page_count*8/1024 user_objects_dealloc_mb,

((t1.internal_objects_alloc_page_count+t1.user_objects_alloc_page_count)-

(t1.internal_objects_dealloc_page_count+t1.user_objects_dealloc_page_count))*8/1024 now_tempdb_mb,

t3.login_name,t3.login_time,t3.last_request_start_time,t3.status,t3.total_elapsed_time

from sys.dm_db_session_space_usage t1

inner join sys.dm_exec_sessions as t3

on t1.session_id = t3.session_id

where (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)

and t3.status not in ('sleeping')

order by (t1.internal_objects_alloc_page_count+t1.user_objects_alloc_page_count)-

(t1.internal_objects_dealloc_page_count+t1.user_objects_dealloc_page_count) desc,

(t1.internal_objects_alloc_page_count+t1.user_objects_alloc_page_count) desc

有四个关键信息：

session_id：可以查询该session的相关信息
internal_objects_alloc_page_count：分配给session内部对象的数据页
internal_objects_dealloc_page_count：已经释放的数据页
login_name：该session的登录名
1. 从internal_objects_alloc_page_count可以看出，给session分配了133200页，计算一下大约是1G，假如和告警时tempdb增长的大小一致，执行时间也接近，基本就可以断定是这个session引起的。
  select 133200*8/1024/1024 as [size_GB]
  1
  select 133200*8/1024/1024 as [size_GB]
2. 从internal_objects_dealloc_page_count可以看到占用tempdb的数据已经释放了。
3. 从login_name可知道操作人（这就是实名用户的好处之一，可以很快追踪使用者，是内部人员操作）。
4. 从session_id可以查询最后一条执行的语句。

select p.*,s.text
from master.dbo.sysprocesses p
cross apply sys.dm_exec_sql_text(p.sql_handle) s
where spid = 647

select p.*,s.text

from master.dbo.sysprocesses p

cross apply sys.dm_exec_sql_text(p.sql_handle) s

where spid = 647

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

最后查出来这是一个select into的语句

补充tempdb相关sql

tempdb空间使用跟踪（不要用sp_spaceused)

-- Tempdb库文件的使用情况
SELECT SUM(user_object_reserved_page_count) * 8 as usr_obj_kb,
       SUM(internal_object_reserved_page_count) * 8 as internal_obj_kb,
       SUM(version_store_reserved_page_count) * 8 as version_store_kb,
       SUM(unallocated_extent_page_count) * 8 as freespace_kb,
       SUM(mixed_extent_page_count) * 8 as mixedextent_kb
  FROM sys.dm_db_file_space_usage;

-- Tempdb库文件的使用情况

SELECT SUM(user_object_reserved_page_count) * 8 as usr_obj_kb,

SUM(internal_object_reserved_page_count) * 8 as internal_obj_kb,

SUM(version_store_reserved_page_count) * 8 as version_store_kb,

SUM(unallocated_extent_page_count) * 8 as freespace_kb,

SUM(mixed_extent_page_count) * 8 as mixedextent_kb

FROM sys.dm_db_file_space_usage;

获取每个会话对tempdb用户对象和内部对象的使用空间

-- 排查用户对象和内部对象使用空间异常的问题
select
t1.session_id ,
(t1. internal_objects_alloc_page_count + task_internal_alloc+t1. user_objects_alloc_page_count + task_user_alloc)*8 as [SPACE Allocated FOR ALL Objects (in KB)] ,
(t1. internal_objects_alloc_page_count + task_internal_alloc )*8 AS [SPACE Allocated FOR Internal Objects (in KB)],
(t1. internal_objects_dealloc_page_count + task_internal_dealloc )*8 as [SPACE Deallocated FOR Internal Objects (in KB)],
(t1. user_objects_alloc_page_count + task_user_alloc )*8 as [SPACE Allocated FOR USER Objects (in KB)],
(t1. user_objects_dealloc_page_count + task_user_dealloc )*8 as [SPACE Deallocated FOR USER Objects (in KB)],
DB_NAME( t1.database_id ) AS [ DATABASE NAME ],
t3.HOST_NAME AS [ System NAME ] ,
t3.program_name AS [ Program NAME ] ,
t3.login_name AS [ USER NAME ],
t3.STATUS ,
t3.cpu_time AS [ CPU TIME(IN milisec) ],
t3.total_scheduled_time AS [ Total Scheduled TIME(IN milisec) ],
t3.total_elapsed_time AS [ Elapsed TIME(IN milisec) ],
(t3. memory_usage * 8 ) AS [ Memory USAGE (IN KB) ]
from sys .dm_db_session_space_usage as t1,
( select session_id,
sum(internal_objects_alloc_page_count ) as task_internal_alloc,
sum(internal_objects_dealloc_page_count ) as task_internal_dealloc,
sum(user_objects_alloc_page_count ) as task_user_alloc,
sum(user_objects_dealloc_page_count ) as task_user_dealloc
from sys .dm_db_task_space_usage group by session_id) as t2,
sys.dm_exec_sessions as t3
where t1. session_id = t2 .session_id
and t2. session_id = t3 .session_id
and t1. session_id > 50
order by [SPACE Allocated FOR ALL Objects (in KB)] desc;

-- 会话使用tempdb情况
SELECT b.session_id AS [ SESSION ID ],
       DB_NAME(a.database_id) AS [ DATABASE Name ],
       HOST_NAME AS [ System Name ],
       program_name AS [ Program Name ],
       login_name AS [ USER Name ],
       status,
       cpu_time AS [ CPU TIME(in milisec) ],
       total_scheduled_time AS [ Total Scheduled TIME(in milisec) ],
       total_elapsed_time AS [ Elapsed TIME(in milisec) ],
       (memory_usage * 8) AS [ Memory USAGE(in KB) ],
       (user_objects_alloc_page_count * 8) AS [ SPACE Allocated FOR USER Objects(in KB) ],
       (user_objects_dealloc_page_count * 8) AS [ SPACE Deallocated FOR USER Objects(in KB) ],
       (internal_objects_alloc_page_count * 8) AS [ SPACE Allocated FOR Internal Objects(in KB) ],
       (internal_objects_dealloc_page_count * 8) AS [ SPACE Deallocated FOR Internal Objects(in KB) ],
       CASE is_user_process
         WHEN 1 THEN
          'user session'
         WHEN 0 THEN
          'system session'
       END AS [ SESSION Type ],
       row_count AS [ ROW COUNT ]
  FROM sys.dm_db_session_space_usage a
 INNER join sys.dm_exec_sessions b
    ON a.session_id =
       b.session_id;

-- 排查用户对象和内部对象使用空间异常的问题

select

t1.session_id ,

(t1. internal_objects_alloc_page_count + task_internal_alloc+t1. user_objects_alloc_page_count + task_user_alloc)*8 as [SPACE Allocated FOR ALL Objects (in KB)] ,

(t1. internal_objects_alloc_page_count + task_internal_alloc )*8 AS [SPACE Allocated FOR Internal Objects (in KB)],

(t1. internal_objects_dealloc_page_count + task_internal_dealloc )*8 as [SPACE Deallocated FOR Internal Objects (in KB)],

(t1. user_objects_alloc_page_count + task_user_alloc )*8 as [SPACE Allocated FOR USER Objects (in KB)],

(t1. user_objects_dealloc_page_count + task_user_dealloc )*8 as [SPACE Deallocated FOR USER Objects (in KB)],

DB_NAME( t1.database_id ) AS [ DATABASE NAME ],

t3.HOST_NAME AS [ System NAME ] ,

t3.program_name AS [ Program NAME ] ,

t3.login_name AS [ USER NAME ],

t3.STATUS ,

t3.cpu_time AS [ CPU TIME(IN milisec) ],

t3.total_scheduled_time AS [ Total Scheduled TIME(IN milisec) ],

t3.total_elapsed_time AS [ Elapsed TIME(IN milisec) ],

(t3. memory_usage * 8 ) AS [ Memory USAGE (IN KB) ]

from sys .dm_db_session_space_usage as t1,

( select session_id,

sum(internal_objects_alloc_page_count ) as task_internal_alloc,

sum(internal_objects_dealloc_page_count ) as task_internal_dealloc,

sum(user_objects_alloc_page_count ) as task_user_alloc,

sum(user_objects_dealloc_page_count ) as task_user_dealloc

from sys .dm_db_task_space_usage group by session_id) as t2,

sys.dm_exec_sessions as t3

where t1. session_id = t2 .session_id

and t2. session_id = t3 .session_id

and t1. session_id > 50

order by [SPACE Allocated FOR ALL Objects (in KB)] desc;

-- 会话使用tempdb情况

SELECT b.session_id AS [ SESSION ID ],

DB_NAME(a.database_id) AS [ DATABASE Name ],

HOST_NAME AS [ System Name ],

program_name AS [ Program Name ],

login_name AS [ USER Name ],

status,

cpu_time AS [ CPU TIME(in milisec) ],

total_scheduled_time AS [ Total Scheduled TIME(in milisec) ],

total_elapsed_time AS [ Elapsed TIME(in milisec) ],

(memory_usage * 8) AS [ Memory USAGE(in KB) ],

(user_objects_alloc_page_count * 8) AS [ SPACE Allocated FOR USER Objects(in KB) ],

(user_objects_dealloc_page_count * 8) AS [ SPACE Deallocated FOR USER Objects(in KB) ],

(internal_objects_alloc_page_count * 8) AS [ SPACE Allocated FOR Internal Objects(in KB) ],

(internal_objects_dealloc_page_count * 8) AS [ SPACE Deallocated FOR Internal Objects(in KB) ],

CASE is_user_process

WHEN 1 THEN

'user session'

WHEN 0 THEN

'system session'

END AS [ SESSION Type ],

row_count AS [ ROW COUNT ]

FROM sys.dm_db_session_space_usage a

INNER join sys.dm_exec_sessions b

ON a.session_id =

b.session_id;

查占用 tempdb的sql

SELECT es.host_name , es.login_name , es.program_name,
st.dbid as QueryExecContextDBID, DB_NAME(st.dbid) as QueryExecContextDBNAME, st.objectid as ModuleObjectId,
SUBSTRING(st.text, er.statement_start_offset/2 + 1,(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE er.statement_end_offset 
END - er.statement_start_offset)/2) as Query_Text,
tsu.session_id ,tsu.request_id, tsu.exec_context_id, 
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time, er.cpu_time, er.total_elapsed_time, er.reads,er.writes, 
er.logical_reads, er.granted_query_memory
FROM sys.dm_db_task_space_usage tsu inner join sys.dm_exec_requests er 
 ON ( tsu.session_id = er.session_id and tsu.request_id = er.request_id) 
inner join sys.dm_exec_sessions es ON ( tsu.session_id = es.session_id ) 
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0
ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) 
DESC

SELECT es.host_name , es.login_name , es.program_name,

st.dbid as QueryExecContextDBID, DB_NAME(st.dbid) as QueryExecContextDBNAME, st.objectid as ModuleObjectId,

SUBSTRING(st.text, er.statement_start_offset/2 + 1,(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE er.statement_end_offset

END - er.statement_start_offset)/2) as Query_Text,

tsu.session_id ,tsu.request_id, tsu.exec_context_id,

(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,

(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,

er.start_time, er.command, er.open_transaction_count, er.percent_complete, er.estimated_completion_time, er.cpu_time, er.total_elapsed_time, er.reads,er.writes,

er.logical_reads, er.granted_query_memory

FROM sys.dm_db_task_space_usage tsu inner join sys.dm_exec_requests er

ON ( tsu.session_id = er.session_id and tsu.request_id = er.request_id)

inner join sys.dm_exec_sessions es ON ( tsu.session_id = es.session_id )

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st

WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) > 0

ORDER BY (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count)+(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count)

DESC

示例


USE tempdb;
GO

-- 创建一个测试表
CREATE TABLE #TestTable (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50)
);

-- 向测试表中插入大量数据，占用 TempDB 空间
DECLARE @i INT = 600000;
WHILE @i <= 900000
BEGIN
    INSERT INTO #TestTable (ID, Name)
    VALUES (@i, 'TestName' + CAST(@i AS NVARCHAR(10)));
    SET @i = @i + 1;
END;

select count(*) from #TestTable;

create index idx1 on #TestTable(name);

-- 监视 TempDB 文件的使用情况

SELECT a.file_id,a.type,a.type_desc,
    name , 
    physical_name ,
    CAST((size*1.0/128) as DECIMAL(10, 2) ) size_MB ,
    CAST(FILEPROPERTY(name, 'SpaceUsed') / 128.0 as DECIMAL(10, 2) )  AS [SpaceUsed_MB],
    growth ,
    a.is_percent_growth,
    (CASE
            WHEN growth = 0 THEN '固定大小，不自动增长'
            WHEN growth > 0 AND is_percent_growth = 0  
            THEN '每次增长'+CAST(growth/128 as varchar)+'MB'
            ELSE '百分比增长'
        end )  growth_MB, 
    (CASE   
        WHEN  max_size=0 THEN 'Autogrowth is off.'
        WHEN  max_size=-1 and growth>0 THEN '无限制'
        WHEN  max_size=-1 and growth=0 THEN (CAST(cast(size*1.0/128 as numeric(10,0)) as varchar ))+'MB'
        ELSE  cast((max_size/128) as varchar)
    end) as max_size_MB
FROM tempdb.sys.database_files a
union all

SELECT null,null,'',
    '' , 
    '' ,
    sum(CAST((size*1.0/128) as DECIMAL(10, 2) )) size_MB ,
    sum(CAST(FILEPROPERTY(name, 'SpaceUsed') / 128.0 as DECIMAL(10, 2) ))  AS [SpaceUsed_MB],
    '',
    '' ,
    '',
    '' growth_MB
FROM tempdb.sys.database_files a
;

USE tempdb;

-- 创建一个测试表

CREATE TABLE #TestTable (

ID INT PRIMARY KEY,

Name NVARCHAR(50)

);

-- 向测试表中插入大量数据，占用 TempDB 空间

DECLARE @i INT = 600000;

WHILE @i <= 900000

BEGIN

INSERT INTO #TestTable (ID, Name)

VALUES (@i, 'TestName' + CAST(@i AS NVARCHAR(10)));

SET @i = @i + 1;

END;

select count(*) from #TestTable;

create index idx1 on #TestTable(name);

-- 监视 TempDB 文件的使用情况

SELECT a.file_id,a.type,a.type_desc,

name ,

physical_name ,

CAST((size*1.0/128) as DECIMAL(10, 2) ) size_MB ,

CAST(FILEPROPERTY(name, 'SpaceUsed') / 128.0 as DECIMAL(10, 2) ) AS [SpaceUsed_MB],

growth ,

a.is_percent_growth,

(CASE

WHEN growth = 0 THEN '固定大小，不自动增长'

WHEN growth > 0 AND is_percent_growth = 0

THEN '每次增长'+CAST(growth/128 as varchar)+'MB'

ELSE '百分比增长'

end ) growth_MB,

(CASE

WHEN max_size=0 THEN 'Autogrowth is off.'

WHEN max_size=-1 and growth>0 THEN '无限制'

WHEN max_size=-1 and growth=0 THEN (CAST(cast(size*1.0/128 as numeric(10,0)) as varchar ))+'MB'

ELSE cast((max_size/128) as varchar)

end) as max_size_MB

FROM tempdb.sys.database_files a

union all

SELECT null,null,'',

'' ,

sum(CAST((size*1.0/128) as DECIMAL(10, 2) )) size_MB ,

sum(CAST(FILEPROPERTY(name, 'SpaceUsed') / 128.0 as DECIMAL(10, 2) )) AS [SpaceUsed_MB],

'',

'' ,

'',

'' growth_MB

FROM tempdb.sys.database_files a

;

job定期收集tempdb的使用情况（每隔10分钟收集1次，占用大于1GB的会话情况）

USE [master]
GO
-- 创建用于存储会话信息的表
drop table master.dbo.TempdbSessionInfo
GO
select * into   master.dbo.TempdbSessionInfo from master.dbo.vw_session where 1=0;

-- 创建一个存储过程，用于检查占用 tempdb 空间大于1GB 的会话，并将信息插入到 TempdbSessionInfo 表中
CREATE PROCEDURE dbo.CheckTempdbSessions
AS
BEGIN
    INSERT INTO master.dbo.TempdbSessionInfo
SELECT *
FROM master.dbo.vw_session a
WHERE a.tempdb_alloc_size_GB>=1

END;
GO

-- 创建一个 SQL Server Agent 的作业，每隔10分钟执行一次上面的存储过程
USE msdb;
GO
EXEC dbo.sp_add_job @job_name = N'Check Tempdb Sessions Job';

EXEC dbo.sp_add_jobstep @job_name = N'Check Tempdb Sessions Job',
    @step_name = N'Run Check Tempdb Sessions',
    @subsystem = N'TSQL',
    @command = N'EXEC dbo.CheckTempdbSessions;' , 
    @database_name=N'master'
    ;

declare @job_id varchar(max)
select @job_id=sj.job_id  FROM msdb.dbo.sysjobs sj where sj.name='Check Tempdb Sessions Job';

EXEC msdb.dbo.sp_add_jobschedule @job_id=@job_id, 
    @name=N'Every 10 Minutes',
    @freq_type = 4,
    @freq_interval = 1,
    @freq_subday_type = 4,
    @freq_subday_interval = 10;

EXEC dbo.sp_add_jobserver @job_name = N'Check Tempdb Sessions Job';
GO

-- 启动作业
EXEC dbo.sp_start_job N'Check Tempdb Sessions Job';
GO

select * from  master.dbo.TempdbSessionInfo;

select  a.tempdb_alloc_size_GB ,a.tempdb_dealloc_size_GB,
a.ActualElapsedTime,a.last_wait_type,
a.spid,a.dbname,a.start_time,a.Internal_SQL_TEXT,
a.Parent_SQL_TEXT,
a.loginame,a.client_hostname,a.client_ip
from  master.dbo.TempdbSessionInfo a 
where  a.tempdb_alloc_size_GB>10 
order by a.tempdb_alloc_size_GB desc ;

USE [master]

-- 创建用于存储会话信息的表

drop table master.dbo.TempdbSessionInfo

select * into master.dbo.TempdbSessionInfo from master.dbo.vw_session where 1=0;

-- 创建一个存储过程，用于检查占用 tempdb 空间大于1GB 的会话，并将信息插入到 TempdbSessionInfo 表中

CREATE PROCEDURE dbo.CheckTempdbSessions

BEGIN

INSERT INTO master.dbo.TempdbSessionInfo

SELECT *

FROM master.dbo.vw_session a

WHERE a.tempdb_alloc_size_GB>=1

END;

-- 创建一个 SQL Server Agent 的作业，每隔10分钟执行一次上面的存储过程

USE msdb;

EXEC dbo.sp_add_job @job_name = N'Check Tempdb Sessions Job';

EXEC dbo.sp_add_jobstep @job_name = N'Check Tempdb Sessions Job',

@step_name = N'Run Check Tempdb Sessions',

@subsystem = N'TSQL',

@command = N'EXEC dbo.CheckTempdbSessions;' ,

@database_name=N'master'

;

declare @job_id varchar(max)

select @job_id=sj.job_id FROM msdb.dbo.sysjobs sj where sj.name='Check Tempdb Sessions Job';

EXEC msdb.dbo.sp_add_jobschedule @job_id=@job_id,

@name=N'Every 10 Minutes',

@freq_type = 4,

@freq_interval = 1,

@freq_subday_type = 4,

@freq_subday_interval = 10;

EXEC dbo.sp_add_jobserver @job_name = N'Check Tempdb Sessions Job';

-- 启动作业

EXEC dbo.sp_start_job N'Check Tempdb Sessions Job';

select * from master.dbo.TempdbSessionInfo;

select a.tempdb_alloc_size_GB ,a.tempdb_dealloc_size_GB,

a.ActualElapsedTime,a.last_wait_type,

a.spid,a.dbname,a.start_time,a.Internal_SQL_TEXT,

a.Parent_SQL_TEXT,

a.loginame,a.client_hostname,a.client_ip

from master.dbo.TempdbSessionInfo a

where a.tempdb_alloc_size_GB>10

order by a.tempdb_alloc_size_GB desc ;

查询存储获取tmpdb的占用（从2017开始）


-- 通过查询存储获取性能问题的SQL(执行时间、cpu、IO、tempdb、内存、日志等)
SELECT TOP 50
    qsq.query_id,
    qsq.object_id,
    object_name(qsq.object_id) ob_name,
    SUM(qrs.count_executions) AS sum_executions,
    SUM(qsq.count_compiles) AS sum_compiles,
    sum(qrs.avg_duration)/1000000 AS sum_duration_s,
    avg(qrs.avg_duration)/1000000 AS avg_duration_s,
   (CASE WHEN CEILING(sum(qrs.avg_duration)/1000000) < 36000
    THEN '0' ELSE '' END + RTRIM((cast(FLOOR(sum(qrs.avg_duration)/1000000/3600) as bigint)))
    + ':' + RIGHT('0' + RTRIM((cast(FLOOR(sum(qrs.avg_duration)/1000000) as bigint))%3600/60), 2)
    + ':' + RIGHT('0' + RTRIM((cast(CEILING(sum(qrs.avg_duration)/1000000) as bigint))%60), 2))   sum_duration_s2,
   (CASE WHEN CEILING(avg(qrs.avg_duration)/1000000) < 36000
    THEN '0' ELSE '' END + RTRIM((cast(FLOOR(avg(qrs.avg_duration)/1000000/3600) as bigint)))
    + ':' + RIGHT('0' + RTRIM((cast(FLOOR(avg(qrs.avg_duration)/1000000) as bigint))%3600/60), 2)
    + ':' + RIGHT('0' + RTRIM((cast(CEILING(avg(qrs.avg_duration)/1000000) as bigint))%60), 2))   avg_duration_s2,
    AVG(qrs.avg_logical_io_reads) AS avg_logical_io_reads,
    AVG(qrs.avg_logical_io_writes) AS avg_logical_io_writes,
    AVG(qrs.avg_physical_io_reads) AS avg_physical_io_reads,
    AVG(qrs.avg_cpu_time) AS avg_cpu_time,
    AVG(qrs.avg_query_max_used_memory*8/1024) AS avg_query_max_used_memory_mb,
    AVG(qrs.avg_rowcount) AS avg_rowcount,
    AVG(qrs.avg_log_bytes_used/1024/1024) AS avg_log_bytes_used_mb,
    AVG(qrs.avg_tempdb_space_used*8/1024) AS avg_tempdb_space_used_mb,  -- >= mssql 2017
    count(distinct qsp.plan_id) query_plan_count,
    CAST(DATEADD(HH, +8, qsq.last_execution_time) AS DATETIME) last_execution_time,
    qrs.execution_type_desc,
    (SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt
        WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,    
    TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2
        WHERE qsp2.query_id=qsq.query_id
        ORDER BY qsp2.plan_id DESC)) AS query_plan
FROM sys.query_store_query qsq  WITH(NOLOCK) 
JOIN sys.query_store_plan qsp  WITH(NOLOCK)  
on qsq.query_id=qsp.query_id
JOIN sys.query_store_runtime_stats qrs on 
    qsp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval qsrsi  WITH(NOLOCK) 
on  qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id
WHERE  1=1
-- and qsrsi.start_time >= DATEADD(HH, -24, SYSDATETIME())
 and DATEADD(HH, +8, qsrsi.start_time)   >='2024-05-14 18:00:00'
 and DATEADD(HH, +8, qsrsi.end_time)  <='2024-05-15 09:00:00'
 and DATEADD(HH, +8, qsq.last_execution_time) between '2024-05-14 18:00:00' and  '2024-05-15 09:00:00'
 and avg_duration>60*1000*1000 -- 60s
--and qsq.object_id IN (OBJECT_ID('US_KSRB')  ,OBJECT_ID('USPXGYB') )
-- and qsq.query_id=36284
GROUP BY qsq.query_id, qsq.object_id,qsq.last_execution_time,qrs.execution_type_desc
ORDER BY avg_duration_s DESC;
GO

-- select * from  sys.query_store_query qsq  where qsq.query_id=8389;
-- select * from sys.query_store_plan qsp where qsp.query_id=8389;
-- select * from sys.query_store_runtime_stats a where a.plan_id in (6616,6972);
-- select * from sys.query_store_runtime_stats_interval where runtime_stats_interval_id in(19,61)
-- select * from sys.query_store_wait_stats  a where a.plan_id in (00,6805) order by a.total_query_wait_time_ms desc;   -- @version>=2017

-- select CAST(DATEADD(HH, +8, a.start_time) AS DATETIME)  start_time,
--  CAST(DATEADD(HH, +8, a.end_time) AS DATETIME) end_time,
-- a.runtime_stats_interval_id 
-- from sys.query_store_runtime_stats_interval a

-- 通过查询存储获取性能问题的SQL(执行时间、cpu、IO、tempdb、内存、日志等)

SELECT TOP 50

qsq.query_id,

qsq.object_id,

object_name(qsq.object_id) ob_name,

SUM(qrs.count_executions) AS sum_executions,

SUM(qsq.count_compiles) AS sum_compiles,

sum(qrs.avg_duration)/1000000 AS sum_duration_s,

avg(qrs.avg_duration)/1000000 AS avg_duration_s,

(CASE WHEN CEILING(sum(qrs.avg_duration)/1000000) < 36000

THEN '0' ELSE '' END + RTRIM((cast(FLOOR(sum(qrs.avg_duration)/1000000/3600) as bigint)))

+ ':' + RIGHT('0' + RTRIM((cast(FLOOR(sum(qrs.avg_duration)/1000000) as bigint))%3600/60), 2)

+ ':' + RIGHT('0' + RTRIM((cast(CEILING(sum(qrs.avg_duration)/1000000) as bigint))%60), 2)) sum_duration_s2,

(CASE WHEN CEILING(avg(qrs.avg_duration)/1000000) < 36000

THEN '0' ELSE '' END + RTRIM((cast(FLOOR(avg(qrs.avg_duration)/1000000/3600) as bigint)))

+ ':' + RIGHT('0' + RTRIM((cast(FLOOR(avg(qrs.avg_duration)/1000000) as bigint))%3600/60), 2)

+ ':' + RIGHT('0' + RTRIM((cast(CEILING(avg(qrs.avg_duration)/1000000) as bigint))%60), 2)) avg_duration_s2,

AVG(qrs.avg_logical_io_reads) AS avg_logical_io_reads,

AVG(qrs.avg_logical_io_writes) AS avg_logical_io_writes,

AVG(qrs.avg_physical_io_reads) AS avg_physical_io_reads,

AVG(qrs.avg_cpu_time) AS avg_cpu_time,

AVG(qrs.avg_query_max_used_memory*8/1024) AS avg_query_max_used_memory_mb,

AVG(qrs.avg_rowcount) AS avg_rowcount,

AVG(qrs.avg_log_bytes_used/1024/1024) AS avg_log_bytes_used_mb,

AVG(qrs.avg_tempdb_space_used*8/1024) AS avg_tempdb_space_used_mb, -- >= mssql 2017

count(distinct qsp.plan_id) query_plan_count,

CAST(DATEADD(HH, +8, qsq.last_execution_time) AS DATETIME) last_execution_time,

qrs.execution_type_desc,

(SELECT TOP 1 qsqt.query_sql_text FROM sys.query_store_query_text qsqt

WHERE qsqt.query_text_id = MAX(qsq.query_text_id)) AS query_text,

TRY_CONVERT(XML, (SELECT TOP 1 qsp2.query_plan from sys.query_store_plan qsp2

WHERE qsp2.query_id=qsq.query_id

ORDER BY qsp2.plan_id DESC)) AS query_plan

FROM sys.query_store_query qsq WITH(NOLOCK)

JOIN sys.query_store_plan qsp WITH(NOLOCK)

on qsq.query_id=qsp.query_id

JOIN sys.query_store_runtime_stats qrs on

qsp.plan_id = qrs.plan_id

JOIN sys.query_store_runtime_stats_interval qsrsi WITH(NOLOCK)

on qrs.runtime_stats_interval_id=qsrsi.runtime_stats_interval_id

WHERE 1=1

-- and qsrsi.start_time >= DATEADD(HH, -24, SYSDATETIME())

and DATEADD(HH, +8, qsrsi.start_time) >='2024-05-14 18:00:00'

and DATEADD(HH, +8, qsrsi.end_time) <='2024-05-15 09:00:00'

and DATEADD(HH, +8, qsq.last_execution_time) between '2024-05-14 18:00:00' and '2024-05-15 09:00:00'

and avg_duration>60*1000*1000 -- 60s

--and qsq.object_id IN (OBJECT_ID('US_KSRB') ,OBJECT_ID('USPXGYB') )

-- and qsq.query_id=36284

GROUP BY qsq.query_id, qsq.object_id,qsq.last_execution_time,qrs.execution_type_desc

ORDER BY avg_duration_s DESC;

-- select * from sys.query_store_query qsq where qsq.query_id=8389;

-- select * from sys.query_store_plan qsp where qsp.query_id=8389;

-- select * from sys.query_store_runtime_stats a where a.plan_id in (6616,6972);

-- select * from sys.query_store_runtime_stats_interval where runtime_stats_interval_id in(19,61)

-- select * from sys.query_store_wait_stats a where a.plan_id in (00,6805) order by a.total_query_wait_time_ms desc; -- @version>=2017

-- select CAST(DATEADD(HH, +8, a.start_time) AS DATETIME) start_time,

-- CAST(DATEADD(HH, +8, a.end_time) AS DATETIME) end_time,

-- a.runtime_stats_interval_id

-- from sys.query_store_runtime_stats_interval a

参考

https://blog.csdn.net/Hehuyi_In/article/details/103149146?spm=1001.2014.3001.5502
https://blog.csdn.net/kk185800961/article/details/43530153
SQLServer · 最佳实践 · TEMPDB的设计
 Track SQL Server TempDB Space Usage
https://thesqldude.com/2012/05/15/monitoring-tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessive-tempdb-space/

打赏赞(3)

标签： MSSQL SQL Server 慢SQL tempdb

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

1、声明：本网站有部分文章整合或转载自网络，文章著作权归属原作者或原团队；若有侵权，深表歉意，请联系本站站长进行处理，谢谢！！！
2、本站提供数据库的培训和考证业务，培训包含但不限于Oracle、MySQL、PostgreSQL、SQL Server、国产数据库等，考证包括但不限于Oracle OCP、Oracle OCM、MySQL OCP、PGCA、PGCE、PGCM等，私聊QQ646634621或微信dbaup66，非诚勿扰，谢谢！！！
3、麦老师新建了《西安DBA数据架构师》微信群，有志同道合的朋友可以私聊我入群，我们一起聊技术，聊生活，空了可以喝酒约饭。
4、为了方便大家更有针对性的讨论学习数据库，故新建数据库分类群，包括Oracle群、MySQL群、SQL Server群、PG&GP群、国产&信创群。志同道合的朋友可以入群聊技术聊人生，捣乱的勿进，喷子勿进，极端人士勿进，谢谢。

合 MSSQL如何定位大量占用tempdb的SQL语句

简介

查看tempdb记录的分配情况

补充tempdb相关sql

tempdb空间使用跟踪（不要用sp_spaceused)

获取每个会话对tempdb用户对象和内部对象的使用空间

查占用 tempdb的sql

示例

job定期收集tempdb的使用情况（每隔10分钟收集1次，占用大于1GB的会话情况）

查询存储获取tmpdb的占用（从2017开始）

参考

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

2024 年 11 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 MSSQL如何定位大量占用tempdb的SQL语句

简介

查看tempdb记录的分配情况

补充tempdb相关sql

tempdb空间使用跟踪（不要用sp_spaceused)

获取每个会话对tempdb用户对象和内部对象的使用空间

查占用 tempdb的sql

示例

job定期收集tempdb的使用情况（每隔10分钟收集1次，占用大于1GB的会话情况）

查询存储获取tmpdb的占用（从2017开始）

参考

相关文章

您可能还喜欢...

MSSQL中的幽灵数据删除进程、虚影清除进程（ghost cleanup）

MSSQL的服务器配置选项中的最大工作线程数（max worker threads）

MSSQL安装后需要调整的几个参数

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复