LCK_M_S causing blocking with session id < 50

Question

We are experiencing high LCK_M_S wait times conflicting with idle blocking sessions whose ids are less than 50.

The waiting session is always a Select statement within a stored procedure.

The blocking session doesn't have any SQL statements and the monitoring tool we use does not show any logged last activity. All I can understand is it is always a session id less than 50 (25,27 or 30).

As can be seen below, blocked session uses an index hint about which I'm unsure whether it might be the culprit. I know it is not a good practice but someone had written it this way long ago and was not causing any problems until the last week.

The shortened and obfuscated version of the waiting statement is as follows:

SELECT 
  CASE  
      WHEN LEN(B)>200 THEN LEFT(B,200)+'...'  
      ELSE B  
   END B,  
   dbo.somedatefunction(E) AS E,  
FROM  
   (  
   SELECT ROW_NUMBER() OVER (ORDER BY A.H DESC) AS ROWNUMBERS,  
      A.A,  
      B,  
      TN.G,  
      OP.I + ' ' + OP.F AS F  
   FROM ABC AS A  
   WITH  
      ( 
         NOLOCK,  
         INDEX(IX_INDEX) 
      )  
   INNER JOIN DEF AS TN  
   WITH  
      (  
         NOLOCK 
      )  
   ON TN.G = A.G  
   LEFT JOIN H AS OP  
   WITH  
      (  
         NOLOCK 
      )  
   ON OP.J = A.J  
   WHERE Contains(A.K,@SomeVariable)  
  AND dateadd(hour,2,datediff(day,0,A.L)) < GETDATE()  
   ) AS AHK  
WHERE ROWNUMBERS > ((@variable-1) * @somevariable3)  
AND ROWNUMBERS <= @variable * @somevariable3 
FOR XML PATH('Result')

What might be causing this wait to occur?

Answer 1

From what you have said your procedure is trying to read data from a table and trying to take shared lock and is blocked. You can read https://www.sqlskills.com/help/waits/lck_m_s and try to find out more details what process is blocking.

If ghost cleanup is really what is blocking you can query sys.dm_exec_requests to see command column. Read about disabling Ghost Cleanup. - https://dba.stackexchange.com/users/42379