MSSQL’de Uzun Süren Querileri Tespit Etmek
Merhaba
Bu prosedür axaptada özellikle çok uzun süren querileri tespit etmenizi sağlar. Bu sayede kilitlenmeleri tespit edip hangi kullanıcıdan veya queriden kaynaklandığını tespit edebilirsiniz. Umarım işinize yarar.
İndirmek için buraya tılayabilirsiniz.
CREATE PROCEDURE [bmssa].[q_processes] AS
set nocount on
declare
@spid smallint,
@blocked smallint,
@waittype binary,
@waittime INT,
@lastwaittype nchar(32),
@waitresource nchar(50),
@dbid smallint,
@uid smallint,
@cpu int,
@physical_io bigint,
@memusage int,
@last_batch datetime,
@login_time datetime,
@open_tran smallint,
@status nchar(30),
@hostname nchar(50),
@program_name nchar(50),
@cmd nchar(16),
@nt_domain nchar(50),
@nt_username nchar(50)
declare @objectID as int
select @objectID = object_id(‘[tempdb]..[#tmpProcesses]‘)
if exists (select * from tempdb..sysobjects where id = @objectID)
drop table #tmpProcesses
create table #tmpProcesses(
spid smallint,
blocked smallint,
waittype binary,
waittime INT,
lastwaittype nchar(32),
waitresource nchar(50),
dbid smallint,
uid smallint,
cpu int,
physical_io bigint,
memusage int,
last_batch datetime,
login_time datetime,
open_tran smallint,
status nchar(30),
hostname nchar(50),
program_name nchar(50),
cmd nchar(16),
nt_domain nchar(50),
nt_username nchar(50))
DECLARE processes_cursor CURSOR FOR
select spid,blocked,waittype,waittime,lastwaittype,waitresource,dbid,uid,cpu,physical_io,
memusage,last_batch,login_time,open_tran,
status,hostname,program_name,cmd,nt_domain,nt_username
from master..sysprocesses (nolock)
where spid > 50 and (status = ‘runnable’ or open_tran > 0 or waittime > 0 or blocked > 0)
and dbid <> 1 and spid <> @@spid
order by cpu desc
OPEN processes_cursor
FETCH NEXT FROM processes_cursor
INTO @spid,@blocked,@waittype,@waittime,@lastwaittype,@waitresource,@dbid,
@uid,@cpu,@physical_io,@memusage,@last_batch,@login_time,@open_tran,@status,
@hostname,@program_name,@cmd,@nt_domain,@nt_username
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #tmpProcesses (spid,blocked,waittype,waittime,lastwaittype,waitresource,dbid,uid,cpu,
physical_io,memusage,last_batch,login_time,open_tran,status,hostname,program_name,
cmd,nt_domain,nt_username)
values (@spid,@blocked,@waittype,@waittime,@lastwaittype,@waitresource,@dbid,
@uid,@cpu,@physical_io,@memusage,@last_batch,@login_time,@open_tran,@status,
@hostname,@program_name,@cmd,@nt_domain,@nt_username)
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM master..sysprocesses (nolock) WHERE spid = @spid
SELECT @spid as spid, text FROM ::fn_get_sql(@Handle)
FETCH NEXT FROM processes_cursor
INTO @spid,@blocked,@waittype,@waittime,@lastwaittype,@waitresource,
@dbid,@uid,@cpu,@physical_io,@memusage,@last_batch,@login_time,@open_tran,
@status,@hostname,@program_name,@cmd,@nt_domain,@nt_username
END
CLOSE processes_cursor
DEALLOCATE processes_cursor
select * from #tmpProcesses
select getdate()
set nocount off
GO
