We are experiencing periodic slowdowns of our SQL Server 2014.
Today I identified what appears to be the cause, in the SQL Activity Monitor.
Process 61 has:
Task State - blank
Command - blank
Wait time - 0
Blocked by - blank
Head block - 1 <-- ZOMG!!
Several other processes had:
Task State: SUSPENDED
Command: SELECT
Wait Time: 1000000+
Blocked By: 61 (or some blocking chain leading to 61)
The SQL for process 61 head blocker was a simple query (EntityFramework generated) doing what should be a fast indexed lookup on a single table:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[ClientCareDocumentTemplateId] AS [ClientCareDocumentTemplateId],
....
[Extent1].[LastUpdatedByAuditLogId] AS [LastUpdatedByAuditLogId]
FROM [dbo].[ClientCareDocument] AS [Extent1]
WHERE [Extent1].[Id] = 185238
Why on earth would this query, allegedly not even doing anything, having no task state, having 0 wait time, be blocking other queries?
How can we avoid these occurring in future?
