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?