April 19, 2010 at 9:11 am
Hi all - as the title suggests I am after some guidelines as to how to measure the impact blocking and locking may be having on my database server.
I have run the blocked process report and dead lock graphs in SQL profiler and I am happy with the information I have received, but what kind of things are an indicator of blocking and locking when it comes to using perfmon?
Many thanks
April 21, 2010 at 1:54 pm
I wouldn't use perfmon, I would stick with profiler. For a real time query you can also use:
SELECT --top 1 Object_Name(resource_associated_entity_id),
CASE WHEN resource_type = 'Object' THEN OBJECT_NAME(resource_associated_entity_id)
WHEN resource_type in ('PAGE','KEY') THEN OBJECT_NAME(p.object_id)
ELSE CAST(resource_associated_entity_id as Varchar(30))
END AS [Object_Name],
tl.request_session_id
,tl.resource_database_id
,tl.resource_associated_entity_id
,tl.resource_type
,tl.resource_description
,tl.request_mode
,tl.request_status
FROM sys.dm_tran_locks tl
LEFT JOIN sys.partitions p
ON tl.resource_associated_entity_id = p.hobt_id
AND tl.resource_type in ('PAGE','KEY')
WHERE resource_database_id = db_id()
AND resource_type <> 'DATABASE'
-- AND request_session_id = 114
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply