--// Este looping infinito deve ser rodado no Query Analyzer ou Job, o tempo definido é de 1 minuto, mas pode ajustar para segundos alterando o valor para 0:00:05 (5 segundos)
While 1=1 begin
exec sp_dba_lock
waitfor delay '0:01'
end
--// Rodar essa procedure no banco MASTER
alter proc sp_dba_lock
as
DECLARE @ID int
if (select count(*) from sysprocesses where blocked > 0)>0 begin
IF OBJECT_ID('tempdb..#sp_who2') IS NOT NULL
DROP TABLE #sp_who2
IF OBJECT_ID('tempdb..#sp_who2') IS NULL
BEGIN
CREATE TABLE #sp_who2
(SPID INT,
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy INT NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) NULL,
CPUTime_ms BIGINT NULL,
waittime_ms BIGINT,
waitresource NVARCHAR(1000),
lastwaittype NVARCHAR(1000),
memusage_pages BIGINT,
DiskIO_RW INT NULL,
LastBatch DATETIME,--VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL,
SPID2 INT,
dt_atualizacao SMALLDATETIME DEFAULT GETDATE())
END
INSERT INTO #sp_who2
(SPID ,status,Login,HostName,BlkBy,DBName,Command,
CPUTime_ms, waittime_ms, waitresource, lastwaittype,
memusage_pages,
DiskIO_RW,
LastBatch
,ProgramName
,SPID2)
SELECT
spid
,p.status
,convert(sysname, rtrim(p.loginame)) as loginname
,p.hostname
,blocked
,db.name
,p.cmd
,p.cpu
,p.waittime
,waitresource
,lastwaittype
,memusage
,physical_io
,p.last_batch
,p.program_name
,spid
from master.dbo.sysprocesses p (nolock)
join master.dbo.sysdatabases db (nolock) on p.dbid=db.dbid
print'--// Analyze of lock process'
SELECT SPID,BlkBy,Login,ProgramName,DBName,Command,HostName,LastBatch,CPUTime_ms,DiskIO_RW,status,SPID2
FROM #sp_who2
where blkby <> 0
print'--// -- Analyze which is causing the lock process!!!'
SELECT SPID,BlkBy,Login,ProgramName,DBName,Command,HostName,LastBatch,CPUTime_ms,DiskIO_RW,status,SPID2
FROM #sp_who2 a
where exists
(select 1 from #sp_who2 b
where blkby <> 0
and a.spid=b.blkby)
order by 2
print'--// Details which is causing the lock process'
set @id = (SELECT top 1 blkby FROM #sp_who2 where blkby <> 0)
dbcc inputbuffer(@id)
print'--// -- Show the cost of this process in CPU'
SELECT top 50 spid, cast(1.0*CPUTime_ms/case when datediff(s,lastbatch,getdate())=0 then 1 else datediff(s,lastbatch,getdate()) end/1000 as decimal(8,3)) CPU_rate, CPUTime_ms,lastbatch,
Login,ProgramName,DBName,Command,HostName,DiskIO_RW,LastBatch,status,BlkBy,SPID2
FROM #sp_who2
where login not like 'sa'
and lastbatch > dateadd(mi,-5,getdate())
order by 2 desc
print'--// -- Show the cost of this process in Memory'
SELECT top 20 spid,memusage_pages,CPUTime_ms,cast((DiskIO_RW*100.0)/(case CPUTime_ms when 0 then 1 else CPUTime_ms end)/100.0 as decimal(18,2)) rate,
Login,ProgramName,DBName,Command,HostName,DiskIO_RW,LastBatch,status,BlkBy,SPID2
FROM #sp_who2
where login not like 'sa'
order by 2 desc
print'--// -- Show the cost of this process in i/o Disk'
SELECT top 20 spid,cast((DiskIO_RW*100.0)/(case CPUTime_ms when 0 then 1 else CPUTime_ms end)/100.0 as decimal(18,2)) rate,
memusage_pages,Login,ProgramName,DBName,Command,HostName,CPUTime_ms,DiskIO_RW,LastBatch,status,BlkBy,SPID2
FROM #sp_who2
where login not like 'sa'
order by 2 desc
print'--// -- Show the occurrence in type of wait'
SELECT
lastwaittype, count(*) no_ ,sum(waittime_ms)/1000.0 wait_seg
FROM
#sp_who2
WHERE
login <> 'sa'
GROUP BY
lastwaittype
ORDER BY
SUM(waittime_ms) DESC, COUNT(*) DESC
print'--// --Show the process in waitresource or waittime_ms'
SELECT
spid,Login,ProgramName,DBName,
Command,HostName,lastwaittype, waittime_ms waittime_ms, waitresource,
cast((DiskIO_RW*100.0)/((case CPUTime_ms when 0 then 1 else CPUTime_ms end)/100.0) as decimal(18,2)) rate,
memusage_pages memusage_pages, LastBatch,status,BlkBy,SPID2
FROM
#sp_who2
WHERE (waittime_ms >0) or len(lastwaittype) >0 and login <> 'sa'
--and lastwaittype in ('LATCH_EX','CXPACKET')'
ORDER BY
waittime_ms desc,
lastwaittype,
(DiskIO_RW*100.0)/(case CPUTime_ms when 0 then 1 else CPUTime_ms end)/100.0 DESC
,memusage_pages DESC
print'--Show process from the database: AgileCloud_Live '
SELECT
spid,
waittime_ms,
CAST((DiskIO_RW*100.0)/(CASE CPUTime_ms WHEN 0 THEN 1 ELSE CPUTime_ms END)/100.0 AS DECIMAL(18,2)) AS rate,
Login,
ProgramName,
DBName,
Command,
HostName,
CPUTime_ms,
memusage_pages,
DiskIO_RW,
LastBatch,
status,
BlkBy,
SPID2
FROM
#sp_who2 a
WHERE
dbname in ('AgileCloud_Live')
ORDER BY
cputime_ms DESC
SELECT
HostName,
spid,
CAST((DiskIO_RW*100.0)/(CASE CPUTime_ms WHEN 0 THEN 1 ELSE CPUTime_ms END)/100.0 AS DECIMAL(18,2)) AS rate,
waittime_ms,
Login,
ProgramName,
DBName,
Command,
CPUTime_ms,
DiskIO_RW,
LastBatch,
status,
BlkBy,
SPID2
FROM
#sp_who2 a
ORDER BY
LastBatch DESC,
login,
(DiskIO_RW*100.0) / (CASE CPUTime_ms WHEN 0 THEN 1 ELSE CPUTime_ms END) / 100.0 DESC,
waittime_ms DESC
end
else
begin
print '-- // Don`t have Lock(s) at: ' + convert(varchar,getdate())
end