深扒Tempdb-----ETL效率

前情：迁移后的BI数据库服务器，做了集群，开启了一个库的cdc，每天的job都无法顺利跑完，使用

 

use master
go

create procedure sp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int

create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0

IF @@ERROR<>0 RETURN @@ERROR
-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message

-- 循环开始


while @intCounter <= @intCountProperties


begin-- 取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end

-- 循环指针下移
set @intCounter = @intCounter + 1
end

drop table #tmp_lock_who

return 0
end

------执行语句
exec sp_who_lock

go

create procedure sp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int

create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0

IF @@ERROR<>0 RETURN @@ERROR
-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message

-- 循环开始


while @intCounter <= @intCountProperties


begin-- 取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end

-- 循环指针下移
set @intCounter = @intCounter + 1
end

drop table #tmp_lock_who

return 0
end

------执行语句
exec sp_who_lock

使用上述sql很快的查询出存在堵塞以及死锁。

 

在tempdb执行sp_helpfile 显示多个进程对同一资源的争夺，且当前tempdb的mdb>10G。

use tempdb
go
sp_helpfile

执行下方sql，查询争夺同一块资源的session_id执行的sql

 

 

 

 

--当前正在执行的语句  
SELECT  
der.[session_id],der.[blocking_session_id],  
sp.lastwaittype,sp.hostname,sp.program_name,sp.loginame,  
der.[start_time] AS '开始时间',  
der.[status] AS '状态',  
dest.[text] AS 'sql语句',  
DB_NAME(der.[database_id]) AS '数据库名',  
der.[wait_type] AS '等待资源类型',  
der.[wait_time] AS '等待时间',  
der.[wait_resource] AS '等待的资源',  
der.[logical_reads] AS '逻辑读次数'  
FROM sys.[dm_exec_requests] AS der  
INNER JOIN master.dbo.sysprocesses AS sp ON der.session_id=sp.spid  
CROSS APPLY  sys.[dm_exec_sql_text](der.[sql_handle]) AS dest  
WHERE [session_id]>50 AND session_id<>@@SPID  
ORDER BY der.[session_id]  
GO  
   
--是否堵塞  
SELECT spid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran  
,status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,text  
FROM master.dbo.sysprocesses p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) s  
WHERE blocked > 0 OR spid IN(SELECT blocked FROM master.dbo.sysprocesses WHERE blocked > 0)  
go   
   
--是否有未提交事物  
select spid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran    
,status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,text    
from master.dbo.sysprocesses p cross apply sys.dm_exec_sql_text(p.sql_handle) s    
where open_tran <> 0    
go   

结合tempdb情况，重新分配tempdb文件