How can I find what locks are involved in a process

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 partic开发者_如何学JAVAular moment in time. I would like the full picture from start to finish.

---

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.

---

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