April 14, 2002 at 5:31 pm
What are the best tools to analyze database locks and the contention on them etc. I am currently using MS SQL profiler but is there anything else that anyone uses?
Thanks in advance for any advice.
Regards,
Richard Santos
April 14, 2002 at 6:22 pm
This is one of the hardest things to do and I personally have not seen a third party tool that does all that much better (anyone seen one?). Anyway the only other thing right with SQL is the trace flags but they really don't tell much in the way of anything usefull even with the extended details on. The best thing is to use profiler with limited collection and only on the database you are checking (not all). If anyone knows of a tool that does better and I don't have to know when the deadlock occurrs, let me know too.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 30, 2002 at 3:38 am
Compile this procedure this will give you enough information about locks.......
Cheers,
Prakash
drop proc ph_blocked_details
go
create proc ph_blocked_details as
DECLARE @BLOCKED_SPID INT,
@Blocking_SPID INT,
@HOSTNAME_BlockedProccess varchar(50),
@PROGRAM_NAME_BlockedProccess varchar(50),
@HOSTNAME_BlockingProccess varchar(50),
@PROGRAM_NAME_BlockingProccess varchar(50),
@dbNameOfBlocked varchar(4000),
@dbNameOfBlocking varchar(4000),
@statusOfBlocked varchar(4000),
@statusOfBlocking varchar(4000),
@loginameOfBlocked varchar(4000),
@loginameOfBlocking varchar(4000),
@EventInfoOfBlocked varchar(4000),
@EventInfoOfBlocking varchar(4000),
@sql_statement nvarchar(4000)
set nocount on
IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##LockedTableDetails' )
DROP TABLE ##LockedTableDetails
IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##LockedTableEventDetails' )
DROP TABLE ##LockedTableEventDetails
IF EXISTS(SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##LockedTableEventDetailsBlocking_SPID' )
DROP TABLE ##LockedTableDetails
CREATE TABLE ##LockedTableDetails(SPID SMALLINT,HOSTNAME CHAR(10), BLOCKED SMALLINT,
PROGRAM_NAME CHAR(400), HOSTPROCESS CHAR(8), CMD CHAR(16),
BLOCKER_HOST CHAR(10) NULL)
INSERT INTO ##LockedTableDetails
exec ph_blocked
if @@rowcount <>0
BEGIN
DECLARE C2 CURSOR FOR
SELECT spid,BLOCKED FROM ##LockedTableDetails
IF @@ERROR<>0
Begin
ROLLBACK
-- RETURN -1
End
OPEN C2
IF @@ERROR<>0
Begin
ROLLBACK
-- RETURN -1
End
WHILE 0=0
BEGIN
FETCH C2 INTO
@BLOCKED_SPID,@Blocking_SPID
IF @@FETCH_STATUS <> 0
BREAK
--select @BLOCKED_SPID,@Blocking_SPID
set @sql_statement = 'CREATE TABLE ##LockedTableEventDetails(SPID INT default ' + convert(varchar,@BLOCKED_SPID) + ', EventType CHAR(400), Parameters CHAR(8), EventInfo varCHAR(4000))'
EXEC SP_EXECUTESQL @sql_statement
set @sql_statement = 'INSERT INTO ##LockedTableEventDetails (EventType, Parameters, EventInfo) EXEC (''DBCC INPUTBUFFER ( ' + convert(varchar,@BLOCKED_SPID) + ')with NO_INFOMSGS'' ) '
EXEC SP_EXECUTESQL @sql_statement
set @sql_statement = 'CREATE TABLE ##LockedTableEventDetailsBlocking_SPID(SPID INT default ' + convert(varchar,@Blocking_SPID) + ', EventType CHAR(400), Parameters CHAR(8), EventInfo varCHAR(4000))'
EXEC SP_EXECUTESQL @sql_statement
set @sql_statement = 'INSERT INTO ##LockedTableEventDetailsBlocking_SPID (EventType, Parameters, EventInfo) EXEC (''DBCC INPUTBUFFER ( ' + convert(varchar,@Blocking_SPID) + ') with NO_INFOMSGS '') '
EXEC SP_EXECUTESQL @sql_statement
SELECT @HOSTNAME_BlockedProccess=HOSTNAME, @PROGRAM_NAME_BlockedProccess=PROGRAM_NAME, @EventInfoOfBlocked =b.EventInfo ,@EventInfoOfBlocking = c.EventInfo
FROM ##LockedTableDetails a,##LockedTableEventDetails b , ##LockedTableEventDetailsBlocking_SPID c
where a.spid=b.spid and a.blocked=c.spid and a.spid = @BLOCKED_SPID
select @HOSTNAME_BlockingProccess = HOSTNAME,@PROGRAM_NAME_BlockingProccess = PROGRAM_NAME,@dbNameOfBlocking=dbid, @statusOfBlocking= status, @loginameOfBlocking=loginame FROMMASTER..SYSPROCESSES B with(nolock) WHERE B.SPID = @Blocking_SPID
select @dbNameOfBlocked=dbid, @statusOfBlocked= status, @loginameOfBlocked=loginame FROMMASTER..SYSPROCESSES B with(nolock) WHERE B.SPID = @BLOCKED_SPID
SELECT @dbNameOfBlocked=NAME FROM MASTER..SYSDATABASES WHERE DBID=@dbNameOfBlocked
SELECT @dbNameOfBlocking=NAME FROM MASTER..SYSDATABASES WHERE DBID=@dbNameOfBlocking
select 'Blocked id is ' + convert(varchar,@BLOCKED_SPID) + CHAR(13) + 'Run by program ' + rtrim(@PROGRAM_NAME_BlockedProccess) + CHAR(13) + 'From the host ' + @HOSTNAME_BlockedProccess
+ CHAR(13) + 'Database Name : ' + @dbNameOfBlocked + CHAR(13) + 'Current Status : ' + @statusOfBlocked + CHAR(13) + 'Login name : ' + @loginameOfBlocked
+ CHAR(13) + 'Fired command is : ' + @EventInfoOfBlocked
as [Blocked Process Details]
select 'Blocking id is ' + convert(varchar,@Blocking_SPID) + CHAR(13) + 'Run by program ' + rtrim(@PROGRAM_NAME_BlockingProccess) + CHAR(13) + 'From the host ' + rtrim(@HOSTNAME_BlockingProccess)
+ CHAR(13) + 'Database Name : ' + @dbNameOfBlocking + CHAR(13) + 'Current Status : ' + @statusOfBlocking + CHAR(13) + 'Login name : ' + @loginameOfBlocking
+ CHAR(13) + 'Fired command is : ' + @EventInfoOfBlocking
as [Blocking Process Details]
drop table ##LockedTableEventDetails
drop table ##LockedTableEventDetailsBlocking_SPID
END
CLOSE C2
DEALLOCATE C2
DROP TABLE ##LockedTableDetails
END
GO
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
April 30, 2002 at 8:38 am
Here is a link to the stored proc that microsoft recommends:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q251004
This is for sql 7 but there is a link to the proc for sql2k. I had to use this for one of my clients and you really need to monitor closely(have profiler running at the same time) because even thought this gives you a lot of info, its best to 'catch' the process while its occurning. This proc has a cryptic output thats a little hard to read, if you decide to use it, I could help you read it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply