sys.dm_exec_requests error for “blocking_session_id”
Posted by Simon Cho on 08/24/2011
Recently, I found that one of the DMV return error data when specific case.
sys.dm_exec_requests return error result of “blocking_session_id” data when CXPACKET is blocked by other session.
Here is the simulation
1. Create test data (if your server isn’t working as parallelism on #3, you need to increase top count)
| use test go if object_id(‘test1’) is not null select top 1000000 identity(int, 1,1) idx, c1.* into test1 from sys.syscolumns c1 cross join sys.syscolumns c2 cross join sys.syscolumns c3 go ALTER TABLE dbo.test1 ADD CONSTRAINT PK_test1 PRIMARY KEY CLUSTERED (idx) go |
2. Open new Query on your SSMS and Update one record to make lock processing without close the transaction. (Session 1)
| Use test Go begin transaction update test1 –rollback transaction |
3. Open another new Query on your SSMS and run parallelism query.(Session 2)
| set transaction isolation level read committed exec sp_configure ‘show advanced options’, 1 reconfigure with override exec sp_configure ‘max degree of parallelism’,0 reconfigure with override select @@SPID go select COUNT(*) from test1 where name like ‘%object%’ option (maxdop 0) |
Before run the query, please see the execution plan to make sure this query is working as parallelism.
And run the query. It should keep running due to open transaction
4. Compare sp_who2 result, sys.sysprocesses and sys.dm_exec_requests result.
| — 53 is the session id of #3 exec sp_who2 53 select spid, blocked from sys.sysprocesseswhere spid=53 select session_id, blocking_session_id, wait_type from sys.dm_exec_requests where session_id=53 |
Result of sys.dm_exec_requets
The reason is that #2 just blocked 1 record to update.
#3 query is heavy, so it’s running as parallelism.
But, one of parallelism query can’t finish it due to blocking process of update lock.
So, it’s waiting for updating.
In this case, the sys.dm_exec_requests should return blocking session id as 59.
But, it returns as 0.
It’s error data.
Thanks.
Simon Cho
Simon's SQL 
Simon Cho said
It’s sql 2005-2008 problem.
The simulation was tested on tested SQL 2008 R2 (SP1), 10.50.2500.
Jungsun Kim said
Thank you for the good information, Simon 🙂
Simon Cho said
Wow. Thank you for visiting here.
See you in PASS 2011. I’ll be also there.
Simon