SQL Server LCK_M_S only happens in production

Question

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 1

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.