October 3, 2007 at 5:20 am
Hi All,
I am trying to monitor the lock mode types held during various transactions and want to know if there is a way of doing this? I can run the transaction in QA with a begin tran and a sp_lock, then do a rollback, but the locks shown are just for that one time run through. I can monitor locks aquired/released in profiler but this doesnt show me (i dont think!) the lock mode. What im trying to find is what scenario a lock is being escalated to a table lock and locking out another process.
Any feedback/advice would be great.
Thanks
John
October 4, 2007 at 2:04 pm
Use the Profiler You can configure it to watch for pretty much anything the session might do.
October 4, 2007 at 2:17 pm
Profiler will not be able to tell you the lock types. You can use it to watch for locks aquired/released and locks escalated but not lock type. Keep in mind that if you go this route, the profiler traces will be huge. For what you are wanting to do, you may want to write your own process to poll the locks.
Keep in mind that I just threw this together, but something like this may get you a start. Watch out for the table size and you may want to add indexes. Also, watch your poll time as you don't want to interfere with production performance. Hopefully, you have a test system you can use.
CREATE TABLE Locks(spid int, dbid int, ObjId varchar(15), IndId int, Type varchar(10), Resource varchar(20), Mode varchar(10), Status varchar(10), PollTime datetime DEFAULT GETDATE())
INSERT INTO Locks(spid,dbid,ObjId,IndId,Type,Resource,Mode,Status)
EXEC sp_lock
October 4, 2007 at 2:46 pm
Thanks John, i will give that a go in dev and see what i can get from it, i kinda went down this route first and then decided there must be a better way, but there doesnt seem to be 🙂
October 4, 2007 at 2:50 pm
Sorry, Don't have a 2000 instance anymore to work with, but with the 2005 profiler you CAN see both the MODE and the TYPE of the locks aquired.
/****************************************************/
/* Created by: SQL Server Profiler */
/* Date: 10/04/2007 04:48:37 PM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
-- SQL Server Yukon specific events will not be scripted
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 24, 7, @on
exec sp_trace_setevent @TraceID, 24, 15, @on
exec sp_trace_setevent @TraceID, 24, 8, @on
exec sp_trace_setevent @TraceID, 24, 32, @on
exec sp_trace_setevent @TraceID, 24, 1, @on
exec sp_trace_setevent @TraceID, 24, 9, @on
exec sp_trace_setevent @TraceID, 24, 41, @on
exec sp_trace_setevent @TraceID, 24, 2, @on
exec sp_trace_setevent @TraceID, 24, 6, @on
exec sp_trace_setevent @TraceID, 24, 10, @on
exec sp_trace_setevent @TraceID, 24, 14, @on
exec sp_trace_setevent @TraceID, 24, 22, @on
exec sp_trace_setevent @TraceID, 24, 26, @on
exec sp_trace_setevent @TraceID, 24, 3, @on
exec sp_trace_setevent @TraceID, 24, 11, @on
exec sp_trace_setevent @TraceID, 24, 4, @on
exec sp_trace_setevent @TraceID, 24, 12, @on
exec sp_trace_setevent @TraceID, 24, 13, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 2, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 4a102da6-e82b-4070-b8a5-12388e1b5507'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
October 4, 2007 at 2:51 pm
Yea, I don't think there is if you really want the lock types. But then again, I've been wrong before.....:Whistling:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply