Feeds:
Posts
Comments

Archive for the ‘Analysis’ Category

Some time ago I was dealing with a performance analysis on a SQL server, so I was using a combination of tools and scripts trying to find the root cause. After that, I decided to move a step forward and customize the sp_who2 that SQL 2005 introduced. What I wanted to combine on the same query was the processes running and, if there’s a lock, I wanted to know the statement causing it.

After playing around a little, I came with this stored procedure. The parameters that you can pass to it are just two: Show only transactions that are locked and show the application that executed the transaction. I excluded some columns that the normal sp_who2 shows because my goal was to quickly find statements that cause locks. Feel free to change it at will. This query can be improved, so if you want to use it as a starting point for more robust scripts.

CREATE PROCEDURE [dbo].[pla_who]
@blockedOnly bit = 0,
@ShowApp bit = 0
as
declare @cmd nvarchar(max)
set @cmd = ‘
select
sp.spid as ”SPID”,
sp.status as ”Status”,
sp.loginame as ”Login”,
sp.hostname as ”Host Name”,
sp.blocked as ”Bloked By SPID”,
sd.name as ”DB Name”,

if(@ShowApp = 1)
set @cmd = @cmd + ‘ sp.program_name as ”Application”,

set @cmd = @cmd + ‘case sp.blocked
when 0 then null
else
(select SUBSTRING(text, COALESCE(NULLIF((select stmt_start/2 FROM sys.sysprocesses WHERE spid = sp.blocked AND ecid = 0), 0), 1), CASE (select CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END FROM sys.sysprocesses WHERE spid = sp.blocked AND ecid = 0) WHEN -1 THEN DATALENGTH(text) ELSE ((select CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END FROM sys.sysprocesses WHERE spid = sp.blocked AND ecid = 0) – (select stmt_start/2 FROM sys.sysprocesses WHERE spid = sp.blocked AND ecid = 0)) END ) from sys.dm_exec_sql_text((SELECT [sql_handle] FROM master.sys.sysprocesses WHERE spid = sp.blocked AND ecid = 0)))
end as ”Locking Statement”,
case sp.blocked
when 0 then null
else
(select [text] as ”Full Query” from sys.dm_exec_sql_text((SELECT [sql_handle] FROM master.sys.sysprocesses WHERE spid = sp.blocked AND ecid = 0)))
end as ”Full Proc or Query”
from sys.sysprocesses sp
left outer join sys.databases sd on sp.dbid = sd.database_id

if (@blockedOnly = 1)
set @cmd = @cmd + ‘where sp.blocked 0’
else
set @cmd = @cmd + ‘where sp.blocked >= 0’

set @cmd = @cmd + ‘order by sp.spid’
exec(@cmd)
go

Advertisements

Read Full Post »