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? Dec 10, 2015 at 14:15
  • I don't believe so. Is that necessary for a block? Dec 10, 2015 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. Dec 11, 2015 at 6:46

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.