1

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?

3
  • Is the read wrapped in a transaction? – Allan S. Hansen Dec 10 '15 at 14:15
  • I don't believe so. Is that necessary for a block? – Brendan Hill Dec 10 '15 at 23:41
  • It's not necessary for a block, but I've seen transactions and rollbacks handled incorrectly from code causing the 'blank' command issue; and "hidden" transactions are a common issue for blocking. – Allan S. Hansen Dec 11 '15 at 6:46

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.