Geek Answers Handbook

SQL Server LCK_M_S occurs only in production

I have a stored procedure that is called in a SQL Server 2012 report that takes an age to run in production compared to development due to a lck_m_s blocking session

A stored procedure runs instantly when executed in SQL Server Management Studio, and also works well when called as part of a report from a dev laptop through Visual Studio.

When the report is uploaded to the production server, there is a problem with blocking.

How can I find out what causes lck_m_s in production?

+6
source share
2 answers

Run this query when the problem recurs:

 select * from sys.dm_os_waiting_tasks t inner join sys.dm_exec_connections c on c.session_id = t.blocking_session_id cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) as h1

This will give you the decline of the session that caused the lock on which the resource was locked, and the text of the rcent request itself for that session. This should give you a solid starting point.

+10
source

You have a couple of options

  • Set up a blocked process report. Basically, you set the system configuration of blocked process threshold (s) to a non-zero number of seconds and set up an event notification in the BLOCKED_PROCESS_REPORT event. You will receive an XML report every time a process is blocked more than your threshold. The disadvantage of this is that it will be for something blocked, and not just for your procedure, but you will get who keeps an incompatible lock in the report.

  • Set up an extended event session for your procedure to capture the lock_released event, where the duration is longer than you want to wait. The good news is that it is extremely focused and you can define a session so that you get very little noise. The disadvantage is that you won’t know what process the incompatible lock is holding (although you will get a fairly detailed description that the blocked resource will facilitate your investigation).

0
source

More articles:


All Articles