solving sql server problems for millions of dbas and developers since 2006



SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups MSSLQTips Giveaways MSSQLTips Advertising Options

MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Twitter Page MSSQLTips Google+ Page

SQL Product Highlight

Red Gate Software - SQL Monitor

SQL Server performance monitoring and alerting - SQL Monitor offers an easy entrance to advanced server monitoring with a simple design that's a refreshing change from the status quo. Red Gate have added custom metrics and user roles to the product without spoiling its ease-of-use, to help you answer that timeless question, 'How healthy are your servers?'

Learn more!








Locking and Blocking Scripts in SQL Server 2000 vs SQL Server 2005

By: | Read Comments | Print

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.



Related Tips: More

Problem
A common problem in many environments is locking and blocking.  Locking and blocking can cause performance problems and a poor user experience.  If this problem worsens, it can be escalated to deadlocking.  At a high level, these two problems are due to multiple processes trying to access or modify the same data and SQL Server's lock manager ensuring data integrity.  This problem can be corrected by a number of techniques which may include database design, consistent data access in stored procedures, shortening the transaction length, issuing dirty reads, etc.  What's really necessary is taking the first step to determine where the locking is occurring.  With all of the changes from SQL Server 2000 to 2005, what is the best way to find out what locks are being issued and by whom in SQL Server 2005?

Solution
In SQL Server 2000, a few different stored procedures and tables were used to find the number and types of locks being issued, which spid (system process identifier) the locks were associated with and which user\application issued the commands.  In SQL Server 2005, these same stored procedures are available, but Microsoft has also introduced some new dynamic views\functions which replace the system tables as a means to research the locking issues further.  Let's dive into these objects and scripts to take the crosswalk from SQL Server 2000 to 2005.

SQL Server 2000

In SQL Server 2000 the main stored procedure to find out which locks were issued was sp_lock.  Typically this command was followed by sp_who2 in order to find out more information about the spid in terms of the user, host name, application, CPU and memory utilization.  To drill into the spid more either DBCC INPUTBUFFER or fn_get_sql was issued to find out exactly what code was being issued.  If you could not find the data that you were looking for or built your own scripts, typically you drilled into the master.dbo.syslockinfo table and/or the master.dbo.sysprocesses table, although this was not always the recommended approach.  Here are some sample scripts:

SQL Server 2000 - Locking Related Objects
ID Object Example
1 sp_lock - System stored procedure to identify all of the locks issued across the SQL Server instance EXEC master.dbo.sp_lock
GO
2 sp_who and sp_who2 - System stored procedure to identify all of the processes across the SQL Server instance EXEC master.dbo.sp_who2
GO
3 master.dbo.syslockinfo - System table with the locks issued across the SQL Server instance SELECT req_spid AS 'spid',
DB_NAME(rsc_dbid) AS 'Database',
OBJECT_NAME(rsc_objid) AS 'Name',
rsc_indid AS 'Index',
rsc_text AS 'Description',
ResourceType = CASE WHEN rsc_type = 1 THEN 'NULL Resource'
WHEN rsc_type = 2 THEN 'Database'
WHEN rsc_type = 3 THEN 'File'
WHEN rsc_type = 4 THEN 'Index'
WHEN rsc_type = 5 THEN 'Table'
WHEN rsc_type = 6 THEN 'Page'
WHEN rsc_type = 7 THEN 'Key'
WHEN rsc_type = 8 THEN 'Extent'
WHEN rsc_type = 9 THEN 'RID (Row ID)'
WHEN rsc_type = 10 THEN 'Application'
ELSE 'Unknown'
END,
Status = CASE WHEN req_status = 1 THEN 'Granted'
WHEN req_status = 2 THEN 'Converting'
WHEN req_status = 3 THEN 'Waiting'
ELSE 'Unknown'
END,
OwnerType =
CASE WHEN req_ownertype = 1 THEN 'Transaction'
WHEN req_ownertype = 2 THEN 'Cursor'
WHEN req_ownertype = 3 THEN 'Session'
WHEN req_ownertype = 4 THEN 'ExSession'
ELSE 'Unknown'
END,
LockRequestMode =
CASE WHEN req_mode = 0 THEN 'No access '
WHEN req_mode = 1 THEN 'Sch-S (Schema stability)'
WHEN req_mode = 2 THEN 'Sch-M (Schema modification)'
WHEN req_mode = 3 THEN 'S (Shared)'
WHEN req_mode = 4 THEN 'U (Update)'
WHEN req_mode = 5 THEN 'X (Exclusive)'
WHEN req_mode = 6 THEN 'IS (Intent Shared)'
WHEN req_mode = 7 THEN 'IU (Intent Update)'
WHEN req_mode = 8 THEN 'IX (Intent Exclusive)'
WHEN req_mode = 9 THEN 'SIU (Shared Intent Update)'
WHEN req_mode = 10 THEN 'SIX (Shared Intent Exclusive)'
WHEN req_mode = 11 THEN 'UIX (Update Intent Exclusive)'
WHEN req_mode = 12 THEN 'BU. (Bulk operations)'
WHEN req_mode = 13 THEN 'RangeS_S'
WHEN req_mode = 14 THEN 'RangeS_U'
WHEN req_mode = 15 THEN 'RangeI_N'
WHEN req_mode = 16 THEN 'RangeI_S'
WHEN req_mode = 17 THEN 'RangeI_U'
WHEN req_mode = 18 THEN 'RangeI_X'
WHEN req_mode = 19 THEN 'RangeX_S'
WHEN req_mode = 20 THEN 'RangeX_U'
WHEN req_mode = 21 THEN 'RangeX_X'
ELSE 'Unknown'
END
FROM master.dbo.syslockinfo
GO
4 master.dbo.sysprocesses - System table with the process information across the SQL Server instance SELECT *
FROM master.dbo.sysprocesses
GO

SQL Server 2005

In SQL Server 2005, these objects remain available but some are scheduled to be deprecated in SQL Server 2008 which means now is the time to make sure you transition your scripts prior to the SQL Server 2008 migration:

  • sp_lock - For backward compatibility only and expected to be deprecated in SQL Server 2008
    • Replacement (SQL Server 2005) = sys.dm_tran_locks
    • Expected Replacement (SQL Server 2008) = sys.syslock_information
  • sp_who and sp_who2 - Not expected to be deprecated in SQL Server 2008
  • master.dbo.syslockinfo - For backward compatibility only and expected to be deprecated in SQL Server 2008
    • Replacement  (SQL Server 2005) = sys.dm_tran_locks
    • Expected Replacement (SQL Server 2008) = sys.syslock_information
  • master.dbo.sysprocesses - For backward compatibility only and expected to be deprecated in SQL Server 2008
    • Replacement  (SQL Server 2005) = sys.dm_exec_connections, sys.dm_exec_sessions, sys.dm_exec_requests
      • Stay tuned for an upcoming tip on the mapping and opportunities with these three DMVs

With the sys.dm_tran_locks DMV in SQL Server 2005 functionally replacing sp_lock and master.dbo.syslockinfo a simple query can be issued to capture the pertinent locking related information.  The resource related information (ResourceSubType, ResourceType, Request_Owner_Type, etc.) has all be provided as a descriptive name as opposed to having to write case statements to decipher the values.  As such, this simplifies the overall querying as well as provides the opportunity to query supported objects and issue COUNT commands, determine specific lock types, etc.

SQL Server 2005 - master.sys.dm_tran_locks

-- Simple query to return all records and all columns
SELECT *
FROM master.sys.dm_tran_locks;
GO

-- Focused result set
SELECT resource_type, request_session_id, resource_database_id, resource_associated_entity_id, resource_subtype, resource_description, request_status, request_owner_type, request_mode
FROM sys.dm_tran_locks;
GO

-- Number of lock records per database
SELECT COUNT(*) AS 'NumberofLockRecords', DB_NAME(resource_database_id)
FROM master.sys.dm_tran_locks
GROUP BY resource_database_id;
GO

-- Query for specific lock types
SELECT resource_type, request_session_id, resource_database_id, resource_associated_entity_id, resource_subtype, resource_description, request_status, request_owner_type, request_mode
FROM sys.dm_tran_locks
WHERE resource_type IN ('PAGE', 'KEY', 'EXTENT', 'RID');
GO

SELECT tl.request_session_id, wt.blocking_session_id, DB_NAME(tl.resource_database_id) AS DatabaseName, tl.resource_type, tl.request_mode, tl.resource_associated_entity_id
FROM sys.dm_tran_locks as tl
INNER JOIN sys.dm_os_waiting_tasks as wt
ON tl.lock_owner_address = wt.resource_address;
GO

 

Next Steps

 



Related Tips: More | Become a paid author


Last Update: 10/25/2007

Share: Share 



Free SQL Server Learning

Free webcast - Introduction to SQL Azure


Demystify TempDB Performance and Manageability



Comments and Feedback:


Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email   Notify for updates
Comments
*Enter Code refresh code


 
Sponsor Information
Try the award winning SQL diagnostic manager as a free 14-day trial!

It takes just 5 minutes to connect your SQL Databases to source control. Got 5 minutes? Get started now.

What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant.

Get SQL Server Tips Straight from Kevin Kline.

Join the over million SQL Server Professionals who get their issues resolved daily.

Learn SQL Server 2012, Performance Tuning, Development, Administration, Replication and more - free webcasts

Copyright (c) 2006-2012 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups
Some names and products listed are the registered trademarks of their respective owners.

Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com