On Monday, the first of February, 2010, I will be speaking at the Baltimore SQL Server User Group meeting at 7:00 PM EST. My topic will be SQL Server Locking and Blocking Made Simple. In it, I will discuss:
- The need for locking
- The consequences of blocking
- How SQL Server locks resources
- How blocking affects performance
- Influencing locks with granularity hints
- Setting Transaction Isolation Levels
Here is a sample slide from the presentation. You can download the entire presentation here.
During the presentation, I will regularly use the following script to view the locks being held by SQL Server.
–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’;
I hope you’ll join me there.
If you’re interested in having me speak at your local user group, please send me an email or direct message me in twitter. I’d love to talk with you.
