0

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?

  • 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 This Blog and try to find out more details what process is blocking. Look for ghost cleanup – Shanky Jan 3 '17 at 9:04
  • @Shanky Thank you. The articles are very helpful indeed. How can I make sure that ghost cleanup is causing the problem and what is the remedy if so? – Stackoverflowuser Jan 3 '17 at 11:01
  • If ghost cleanup is really what is blocking you can query sys.dm_exec_requests to see command column. Read about disabling Ghost Cleanup – Shanky Jan 3 '17 at 13:01
  • Is there any way to pinpoint the root cause after the fact? DMVs are good while the session/transacation/request etc. is in progress but not after that (Don't get me wrong; I am well aware you are way more experienced than I am.) I highly suspect some system thread is causing the blocking because the spid is always less than 50 but what that thread is doing is a mystery to me at the moment. – Stackoverflowuser Jan 3 '17 at 13:16
  • What I make of from the blog posts that I read is, albeit I might be wrong, ghost clean-up hammers on the IO. The wait type I experience does not indicate this kind of behaviour. – Stackoverflowuser Jan 3 '17 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.