Sql-server – How to track blocking that happen for less than a second – SQL Server

performanceperformance-tuningquery-performancesql serversql server 2014

I am trying to troubleshoot a blocking issue which happens for less than a second.
The OLTP application is very sensitive and has to have an response time of less than 200ms for some transactions as per the agreed SLA.
We had some lock escalation issues with the new code release which we were able to solve with reducing the batch size in the updates.
Even with the small batch size, we suspect that the new sp is blocking the same rows that the OLTP transactions is updating.

I need to find the session that is getting blocked and the resource its waiting on. As per my understanding "blocked process threshold" can set for a minimum of 1 second and so this will not capture the blocking.

I am experimenting with wait_info and wait_completed x events.

Is there any other way we could track this. Thanks

Best Answer

As you are specifically interested in locking rather than general waits the locks_lock_waits extended event sounds more suitable.

With a filter on increment >= 200

CREATE EVENT SESSION [locks_lock_waits] ON SERVER 
ADD EVENT sqlserver.locks_lock_waits(
        ACTION(sqlserver.sql_text)
            WHERE  ( [sqlserver].[is_system] = 0
                     AND [increment] >= 200
                     AND [counter] <= 1000 ) 
    )
ADD TARGET package0.ring_buffer;

GO

ALTER EVENT SESSION [locks_lock_waits]  
ON SERVER  STATE = start;

The above gathers the statements waiting on locks for the threshold amount of time but doesn't give the specific lock resource.

I have never used this event and have no insight into how much overhead this session would cause on your production server.

I found this video on the topic. That does strongly recommend filtering on counter to reduce the number of events collected and I have done so above.

It also mentions an old legacy undocumented command

dbcc lock(StallReportThreshold, 200) -- 200 is threshold in ms

Which (if trace flag 3605 is enabled) dumps out limited information such as the below to the SQL Server error log.

Process 53 waited 6844 ms for S lock on RID: 2:1:120:2 result: OKWAIT

I just mention this in passing as extended events would be clearly preferable anyway as it is documented and much more powerful.

Related Solutions

Sql-server – Unpredicatable single insert performance on SQL Server table

I suspect this isn't a case of "the insert took 7 minutes" but rather "the insert was blocked for over 6 minutes." When an insert normally runs fine and then suddenly takes a long time, it is likely there is another cause, and highly unlikely that SQL Server suddenly got overwhelmed by your large table with too many indexes.

I treat this in more detail here but, essentially, you need to examine the system while this query is running long. Identify the session_id (SPID) and check sys.dm_exec_requests for blocking_session_id, which I think is most likely the issue; after that, check wait_type and last_wait_type. Most of the time, for duration outliers like this, one of those columns is going to help you track down the reason.

Sometimes, they might not have an active query in sys.dm_exec_requests - as I explain in the comments below, this is possible if someone started a transaction, made some statements against a table, then walked away without committing. You can see what object(s) that session has locked even if they aren't actively running a query.

DECLARE @SPID INT = <blocking_session_id from your "hung" query>;

-- see the command they ran last (might be related, might not)
DBCC INPUTBUFFER(@SPID);

-- see what that session has locked
SELECT s.name, o.name
  FROM sys.dm_tran_session_transactions AS tst
  INNER JOIN sys.dm_tran_locks AS tl
  ON tst.transaction_id = tl.request_owner_id
  INNER JOIN sys.objects AS o
  ON tl.resource_associated_entity_id = o.[object_id]
  INNER JOIN sys.schemas AS s
  ON o.[schema_id] = s.[schema_id]
  WHERE tst.session_id = @SPID;

Sql-server – How to Deal with Performace counters

From the documentation Connection Pooling is:

Connecting to a data source can be time consuming. To minimize the cost of opening connections, ADO.NET uses an optimization technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections

Basically the provider keeps connections open, even if the code that opened them closes them. The next time the provider needs a connection the already open one is used.

These open connections are grouped into pools. A new pool is created for each combination of connection string and credential used to access SQL Server.

Having two logins per second could mean that connection pooling isn't working correctly or it could mean that there are multiple connections using a different combination of connection strings and credentials.

Best Answer

Related Solutions

Sql-server – Unpredicatable single insert performance on SQL Server table

Sql-server – How to Deal with Performace counters

Related Question