For almost a year now, I’ve exchanged emails with the leaders of the Memphis SQL Server User Group, trying to find a time when our schedules would align themselves and I’d be able to join them for a meeting. After many attempts and several near misses, we’ve finally set a date!
This Thursday, February 11, 2010, I will be speaking on a SQL Server Locking & Blocking. I’ll talk about:
- Why databases lock resources
- The consequences of blocking
- How SQL Server manages locks
- Techniques for influencing locking behavior
- Using Transaction Isolation Levels
You can download the presentation materials here.
During my presentation, I’ll demonstrate how to analyze locks that SQL Server is holding using the sys.dm_tran_locks Dynamic Management View (DMV) as shown below.
–examine the resources
SELECT
resource_type
,(CASE
WHEN resource_type = ‘OBJECT’ THEN object_name(resource_associated_entity_id)
WHEN resource_type IN (‘DATABASE’, ‘FILE’, ‘METADATA’) THEN ‘N/A’
WHEN resource_type IN (‘KEY’, ‘PAGE’, ‘RID’) THEN (
SELECT
object_name(object_id)
FROM
sys.partitions
WHERE
hobt_id=resource_associated_entity_id)
ELSE
‘Undefined’
END) AS resource_name
,request_mode as lock_type
,resource_description
,request_status
,request_session_id
,request_owner_id AS transaction_id
FROM
sys.dm_tran_locks
WHERE
resource_type <> ‘DATABASE’;
If you live in or near Memphis, I hope you come out and join us for the meeting. I’d love to meet you. For more information, visit mem-pass.org.
