Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

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
drop table test1

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
set name=’test update’
where idx=25000

–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

Advertisements

3 Responses to “sys.dm_exec_requests error for “blocking_session_id””

  1. Simon Cho said

    It’s sql 2005-2008 problem.
    The simulation was tested on tested SQL 2008 R2 (SP1), 10.50.2500.

  2. Jungsun Kim said

    Thank you for the good information, Simon 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: