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?