How can I find what locks are involved in a process

Question

I am running a SQL transaction with a bunch of statements in it.

The transaction was causing other processes to deadlock very occasionally, so I removed some of the things from the transaction that weren't really important. These are now done separately before the transaction.

I want to be able to compare the locking that occurs between the SQL before and after my change so that I can be confident the change will make a difference.

I expect more locking occurred before because more things were in the transaction.

Are there any tools that I can use? I can pretty easily get a SQL profile of both cases.

I am aware of things like sp_who, sp_who2, but the thing I struggle with for those things is that this is a snapshot in a particular moment in time. I would like the full picture from start to finish.

Try the bible of Deadlock Troubleshooting: http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx :-) — mellamokb, Sep 22 '11 at 05:18
"so I removed some of the things from the transaction that weren't really important" - perhaps you shopuld identify 'exactly' what is causing the locking...If you had code that wasn't necessary in the transaction, why was it there in the first place? — Mitch Wheat, Sep 22 '11 at 05:20
mellamokb - Thanks, but I have already used that. I have found a deadlock and understand the cause. I have made changes to 'remove' the deadlock, I think anyway. But I just want to compare the locking as I don't want to move this into production and just hope for the best. — peter, Sep 22 '11 at 20:59
SQL version - 2008 R2. Mitch - not sure the relevance of your question. 'Why was it there in the first place' because like everybody else the first time you develop something you don't necessarily know the full requirements or implication of what you are doing. To think otherwise would be naive. The software has been in production for more than 6 months so it is well understood. It is scaling quite nicely, but needs to scale more. — peter, Sep 22 '11 at 21:01

score 1 · Accepted Answer · answered Sep 22 '11 at 07:32

1

You can use SQL Server Profiler. Set up a profiler trace that includes the Lock:Acquired and Lock:Released events. Run your "before" query. Run your "after" query. Compare and contrast the locks taken (and types of locks). For context, you probably still want to also include some of the statement or batch events also, to see which statements are causing each lock to be taken.

answered Sep 22 '11 at 07:32

Damien_The_Unbeliever

234,701
27
340
448

First time I did it there were some hidden columns I hadn't selected so I couldn't group the locks together or understand what the locks referred to. Repeat... – peter Sep 22 '11 at 22:01
Still wasn't able to group the locks very well in all cases, mostly I could though. I could see a difference between my before and after leading me to a positive conclusion. – peter Sep 23 '11 at 03:19

Pranav · Answer 2 · 2011-09-22T06:30:01.827

you can use in built procedure:-- sp_who2

sp_who2 also takes a optional parameter of a SPID. If a spid is passed, then the results of sp_who2 only show the row or rows of the executing SPID.

for more detail info you can check: master.dbo.sysprocesses table

SELECT * FROM master.dbo.sysprocesses where spid=@1

below code shows reads and writes for the current command, along with the number of reads and writes for the entire SPID. It also shows the protocol being used (TCP, NamedPipes, or Shared Memory).

CREATE PROCEDURE sp_who3
(
    @SessionID int = NULL
)
AS
BEGIN
SELECT
    SPID                = er.session_id
    ,Status             = ses.status
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,BlkBy              = er.blocking_session_id
    ,DBName             = DB_Name(er.database_id)
    ,CommandType        = er.command
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )
    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
    ,ElapsedMS          = er.total_elapsed_time
    ,CPUTime            = er.cpu_time
    ,IOReads            = er.logical_reads + er.reads
    ,IOWrites           = er.writes
    ,LastWaitType       = er.last_wait_type
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,transaction_isolation =
        CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
WHERE @SessionID IS NULL OR er.session_id = @SessionID
AND er.session_id > 50
ORDER BY
    er.blocking_session_id DESC
    ,er.session_id

END

How can I find what locks are involved in a process

2 Answers2