1060
Part VIII: Performance Tuning and Optimization
Viewing all the locks is possible with the sys.dm_tran_locks DMV. The following query
joins with other DMVs to provide a complete picture of the locks in a database:SELECT
request_session_id as Spid,
Coalesce(s.name + '.' + o.name + isnull('.' + i.name,''),
s2.name + '.' + o2.name COLLATE SQL_Latin1_General_CP1_CI_AS,
db.name) AS Object,
l.resource_type as Type,
request_mode as Mode,
request_status as Status
FROM sys.dm_tran_locks l
LEFT JOIN sys.partitions p
ON l.resource_associated_entity_id = p.hobt_id
LEFT JOIN sys.indexes i
ON p.object_id = i.object_id
AND p.index_id = i.index_id
LEFT JOIN sys.objects o
ON p.object_id = o.object_id
LEFT JOIN sys.schemas s
ON o.schema_id = s.schema_id
LEFT JOIN sys.objects o2
ON l.resource_associated_entity_id = o2.object_id
LEFT JOIN sys.schemas s2
ON o2.schema_id = s2.schema_id
LEFT JOIN sys.databases db
ON l.resource_database_id = db.database_id
WHERE resource_database_id = DB_ID()
ORDER BY Spid, Object, CASE l.resource_type
When 'database' Then 1
when 'object' then 2
when 'page' then 3
when 'key' then 4
Else 5 endDealing with Deadlocks
A deadlock is a special situation that occurs when two or more processes are competing for
the same set of resources; each prevents the other from obtaining the source it needs to
complete its work.Deadlocks are not a relational database specifi c problem; they can occur in any system where
there is potential for resource contention, such as operating systems. However, because this is
SQL Server-specifi c literature, you focus on the deadlock nuances inside the database engine.Following is a simple example of a common deadlock scenario:■ (^) Transaction 1 has a lock on data A and needs to lock data B to complete its
transaction.
c47.indd 1060c47.indd 1060 7/31/2012 10:23:29 AM7/31/2012 10:23:29 AM
http://www.it-ebooks.info