Blocking Queries aren't Active

SQL Server Administration
1

We had a strange disruption where Active Queries (entries in sys.dm_exec_requests) where showing Blocked Sessions that didn't show up in the Active Queries.

Always Sessions that are blocking other sessions have their queries appear in sys.dm_exec_requests. The CPU was also going wild at this time.

Any ideas how a Session blocking others can be absent from sys.dm_exec_requests ?

I tried to sp_who2 some of the sessions, but this didn't provide any insight.

2

Have you tried sp_whoisactive to diagnose these?

If yes, have you tried either of these options:

3

Kind of, yes. sp_whoisactive is based on sys.dm_exec_requests and sys.dm_exec_sql_text but is heavier than our query and when SQL Server is over-whelmed is proven to often just added to the over-whelming and hang. I use:

SELECT 
	CONVERT(varchar, getdate(), 20) [Current_Time],
	CASE WHEN r.Start_time >  getdate() THEN  -- Start time can be a split-second ahead of getdate() resulting in a negative, make this 0:
		convert(DECIMAL(10, 3), 0)
		WHEN getdate() - r.Start_time < 1 THEN -- Calculate running time when less than a day, otherwise there will be an overflow error:
		convert(DECIMAL(10, 3), datediff(ms, r.start_time, getdate()) / 1000.0)
		END Running_Time_Sec,
	DB_Name(Database_ID) Database_Name,
    OBJECT_SCHEMA_NAME(s.objectID, database_id) [Schema],
	object_name(s.objectID, database_id) Object_Name,
	r.Session_ID Session_ID,
	CASE WHEN Blocking_Session_ID = 0 THEN NULL ELSE Blocking_Session_ID END Blocked_By,
	SUBSTRING(TEXT, (statement_start_offset / 2) + 1, (
		(CASE WHEN statement_end_offset <= 0 THEN DATALENGTH(TEXT)
		 ELSE statement_end_offset END - statement_start_offset) / 2) + 1) AS Active_Statement,
	left(s.TEXT, 2000) Text_Start,  -- This line may truncate the statement
	Percent_Complete,
	wait_type,
	r.Start_Time,
	*
FROM 
	sys.dm_exec_requests r
	CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
	--CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) -- Uncomment to see Execution Plan
ORDER BY 
	R.Start_Time
4

Our situation was like in sp_whoisactive: Why Am I Blocked? but 54 doesn't appear. And to add to that we were flooded with these.

SQL Server 2014 btw

5

Do you have blocked process report configured? If yes, I'd recommend creating an extended event to capture blocking events fired by the blocked process report. It should catch details missing from your DMV queries.

That, and a few other suggestions are listed here, in case you haven't read it yet:

6

No, we haven't. We have DPA though

In any event - has anyone seen this - blocking caused by sessions that don't have an active SQL statement running?

7

Yes - this can happen where you would see the session_id in sys.dm_exec_sessions but no active requests (not in sys.dm_exec_requests).

These would be idle sessions with an open transaction, generally caused because they are waiting on either a commit or rollback.

8

hmmm I tried to simulate this. If I'm doing a Rollback it shows up as 'rollback transaction' rather than not showing up at all.

But if it's got an open transaction and doing nothing at all, than this scenario I describe happens.

I saw one article describing this as prior to a commit, the application does some application stuff.

So not sure what exactly was going on here but at least now I can understand how this is possible