【Sqlserver】渋滞問題解決の考え方
6959 ワード
背景:App userA:主にデータの入庫を担当する(スレッドが少ない);userB:データクエリーと検証を担当します(スレッドが多く、個人接続データベースクエリーが多い).userAとuserBの間にはロックがしばしば存在し、会社レベルではuserAが主体コア業務であり、userBが業務である.最良の改善策は、Appの改善前に、次のタイミングで問題を一時的に緩和する全タスクの計画実行です.-操作:kill user=userB、そしてデータベースのSQL-実行周期を塞ぐ:1 h/回-hostsとusernameを通じてプロセスを正確に検査し、appのプロセスを誤殺することを避ける.
以下のスクリプトを参照してください(感度が低下しています):
以下のスクリプトを参照してください(感度が低下しています):
USE master;
GO
DECLARE @SQL VARCHAR(MAX);
DECLARE @hostname varchar(20),
@proname varchar(200),
@loginame varchar(20),
@dbname varchar(20),
@spid int,
@blocked int,
@i int
set @i=300
while @i>0
begin
SET @SQL=''
-- mycursor,select
declare mycursor cursor for
with cte as (select replace(hostname,' ','') as hostname ,''''+replace(program_name,' ','')+'''' as program_name
, loginame, db_name(a.dbid) AS DBname,spid,blocked
from sys.sysprocesses as a with(nolock)
cross apply sys.dm_exec_sql_text(sql_handle) as b
where a.blocked>0 and sql_handle<>0x0000000000000000000000000000000000000000
and waittime>2000
and (replace(hostname,' ','') like 'N%' or replace(hostname,' ','') like 'S%') --hosts
and db_name(a.dbid)='yourDB'
and loginame='userB'
)
select replace(hostname,' ','') as hostname ,''''+replace(program_name,' ','')+'''' as program_name
, loginame, db_name(a.dbid) AS DBname,spid,blocked
from sys.sysprocesses as a with(nolock)
cross apply sys.dm_exec_sql_text(sql_handle) as b
where exists(select blocked from cte where cte.blocked=a.spid)
and not exists (select spid from cte where cte.spid=a.spid)
and (replace(hostname,' ','') like 'N%' or replace(hostname,' ','') like 'S%')
and db_name(a.dbid)='yourDB'
and loginame='userB'
union all
select * from cte
--
open mycursor
-- 2
fetch next from mycursor into @hostname,@proname,@loginame,@dbname,@spid,@blocked
while (@@fetch_status=0)
begin
SET @SQL=''
SET @SQL=@SQL+'KILL '+ cast(@spid as varchar(10))+';'
print @SQL
EXEC(@SQL);
--waitfor delay '0:0:1'
-- -
fetch next from mycursor into @hostname,@proname,@loginame,@dbname,@spid,@blocked
end
set @i=@i-1
waitfor delay '0:0:1'
--
close mycursor
--
DEALLOCATE mycursor
end