SQL Server LCK_M_S only happens in production
I have a stored procedure that is called by a SQL Server 2012 report that is taking an age to run in production compared to development because of a blocking session lck_m_s
The stored procedure runs instantaneously when executed in SQL Server Management Studio and also works well when called as part of the report from a dev laptop via Visual Studio.
When the report is uploaded to the production server this blocking issue appears.
How can I find out what is causing the lck_m_s issue when in production?
Answer
Execute this query when the problem happens again:
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
It will give you the spid of the session that caused blocking, on which resource was blocked, and text of the most rcent query for that session. This should give you a solid starting point.