sys.dm_exec_requests question

Question

Post reply

sys.dm_exec_requests question

sandra pastrana

SSC Eights!

Points: 979
More actions
April 14, 2008 at 4:27 pm

#205706

I am not sure how to interpret wait_resource in sys.dm_exec_requests. I am not sure how to use the information dispayed on this column. Any help will be grately appreciated.
Thank you in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 1

It's really only important if the value is NOT zero.

Are you seeing a lot of blocking?

DAB

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If there is a non-zero wait time in the wait_time column and there is a non-null wait type in the wait type column, then this column gives details on exactly what resource the wait is on.

What it is depends on what the wait type is. Many of the wait types have no wait resource. If the wait is on a page latch, the the resource will show a page ID. If the wait is for a locked resource, then the resource will show the object, etc.

Does this help?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sandra pastrana SSC Eights! Points: 979 More actions · Answer 3

Thank you for your reply.

I have a few suspended requests, waiting on PAGEIOLATCH_SH.

the wait resource column reads something like 9:1:4505200.

My question, how do I know that 9:1:4505200 is

9 is the dbid, I am not sure what the one stands for or what the rest of the number stands for either.

Thank you again for any imput you might have

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 4

9:1:4505200

database:file:page

Database 9 (select * from sys.databases where database_id = 9)

File 1 (select * from master.sys.master_files where database_id = 9 and file_id = 1)

Page 4505200

To see what object (table) uses that page:

DBCC TRACEON(3604)
DBCC PAGE (9, 1, 4505200, 0)
DBCC TRACEOFF(3604)

In the output, look for the m_objId value. Use the OBJECT_NAME function to get the table name:

SELECT OBJECT_NAME(m_objId value,database_id)

So, if you see the following in the DBCC PAGE output:

m_objId (AllocUnitId.idObj) = 1101963002

You could get the table name with:

SELECT OBJECT_NAME(1101963002, 9)

As to what causes latch waits, and what to do about them, check out the 'SQL Server 2005 Waits and Queues' doc from Microsoft, available at:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx

(Any of you other DBAs out there who have not yet read this doc should go get it as well.)

-Eddie

Eddie Wuerch
MCM: SQL