Troubleshooting Concurrency Issues

Concurrency issues can account for performance issues. In other words, whether a user of your database solutions is experiencing poor query performance because of inadequate hardware resources or contention, the end result is the same-poor query performance.

Your role as a DBA is to isolate whether the poor query performance is due to inadequate hardware resources, as covered in Chapter 1, or due to a concurrency issue.

So, where do you start? Well…what are your database users doing when there are concurrency issues? Waiting. So, why not start with SQL Server 2005 waits?

When a process connected to SQL Server 2005 tries to access a resource that is unavailable, it has to wait. The process is placed in a resource wait list until the resource is available. You can see this information in a number of ways.

The sys.sysprocesses system table returns information about the various processes connected to your SQL Server 2005 instance and returns the following information about the waits of these processes:

• waittype

• waittime

• lastwaittype

• waitresource

Note 

The sys.sysprocesses system table is being deprecated in a future release of SQL Server. Microsoft recommends you use the sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests DMVs instead.

In SQL Server 2005 Microsoft has added a DMV, called sys.dm_os_wait_stats, that will allow you to view aggregated information about all the waits experienced by all processes connected to your SQL Server 2005 instance since it was started. It makes it easier to diagnose potential concurrency issues.

The sys.dm_os_wait_stats DMV returns the following statistical information:

• wait_type

• waiting_tasks_count

• wait_time_ms

• max_wait_time_ms

• signal_wait_time

Note 

signal_wait_time represents the difference between the time the waiting thread was signaled and when it started running.

Don’t forget that the statistical information is cumulative because either the SQL Server 2005 instance was started or the statistical information was reset.

Tip 

You can reset the wait statistics through the DBCC SQLPERF ('sys.dm_os_ wait_stats' , CLEAR) statement.

So if you want to keep the statistical information for benchmarking/baseline purposes, you should export the statistical information from the sys.dm_os_wait_stats DMV to a table or some other format, such as a Microsoft Excel spreadsheet, before restarting your SQL Server 2005 instance.

So, how do you recognize a concurrency issue? Database users of course will be complaining, but they are always complaining anyway! At the database engine you should be able to observe some of the following:

• Nonzero values in the blocked column of the sys.sysprocesses system table.

• Nonzero values for the BlkBy column of the sp_who2 system stored procedure.

• Large values for you waittype column of the sys.sysprocesses system table.

• Attention events.

• Your SQL Server 2005 instance might not appear to be under stress as processes are waiting (which does not consume hardware resources). In this case, you might observe some of the following:

  • Low values for the processor- and memory-related performance object counters

  • Low processor utilization

  • Low disk utilization

  • Low values for the CPUTime and DiskIO columns of the sp_who2 system stored procedure

  • Low values for the cpu and physical_io columns of the sys.sysprocesses system table

All of these are indicative of some sort of blocking going on. If you are unsure, you can use a blocker script, such as sp_blocker_pss80, to find out.

PSS Head Blocker Script

Microsoft Product Support Services (PSS) provides a stored procedure called sp_blocker_pss80 that you can use to troubleshoot concurrency issues. When executed, the stored procedure gathers the following information:

• Start time

• Connections (sys.sysprocesses system table)

• Lock resources (sys.syslockinfo system table)

• Resource waits (DBCC SQLPERF(WAITSTATS))

• Blocked and blocking processes (DBCC INPUTBUFFER)

• End time

When you suspect a concurrency issue, you can execute the sp_blocker_pss80 stored procedure in an infinite loop to help you resolve the potential concurrency issue:

 WHILE (666=666) BEGIN     EXECUTE master.dbo.sp_blocker_pss80     WAITFOR DELAY '00:00:15' END

You can download the T-SQL script for the sp_blocker_pss80 stored procedure from the “How to monitor blocking in SQL Server 2005 and in SQL Server 2000” Knowledge Base article located at http://support.microsoft.com/kb/271509.

SQL Server 2005 uses two different mechanisms to control concurrency in the database engine:

• Latches

• Locks

You’ll now learn how you troubleshoot latches and locks, before finishing up with troubleshooting deadlocks.

Troubleshooting Latches

Think of a latch as a lightweight synchronization object that is used by SQL Server 2005 internally to control access to internal data structures, control index concurrency, and control access to rows in data pages. Latches do not have the overhead of locks, so you have less overhead on the SQL Server 2005 database engine.

Latches are categorized into different classes such as ACCESS_METHODS_HOBT, BUFFER, DATABASE_CHECKPOINT, FILE_MANAGER, LOG_MANAGER, and MSQL_TRANSACTION_MANAGER.

Tip 

To see the different latch classes, you can execute SELECT latch_class FROM sys.dm_os_latch_stats.

Latches are held only for the duration of the operation required, unlike locks that can be held for the duration of the transaction.

Latch waits occur when a latch request cannot be granted to a thread because of another thread holding an incompatible latch on the same resource.

Monitoring Latches

Monitoring latches to determine user activity and resource usage can help you identify performance bottlenecks. You should examine the relative number of latch waits and wait times to determine whether there is excessive latch contention.

Performance Object Counters

The SQLServer : Latches performance object has a number of counters that you can use to monitor latches:

• Average Latch Wait Time (ms)

• Latch Waits/sec

• Number of SuperLatches

• SuperLatch Demotions/sec

• SuperLatch Promotions/sec

• Total Latch Wait Time (ms)

DMVs

The sys.dm_os_latch_stats DMV returns information about latch waits organized by latch class. The sys.dm_os_latch_stats DMV tracks only latch waits, so if a latch request was immediately granted or failed, it will not contribute to the DMV’s statistics.

The sys.dm_os_latch_stats DMV returns the following statistical information:

• latch_class

• waiting_request_count

• wait_time_ms

• max_wait_time_ms

Tip 

A high value for the max_wait_time_ms value might indicate an internal deadlock.

An important consideration is that the statistical information is cumulative since either the SQL Server 2005 instance was started or the statistical information was reset.

Tip 

You can reset the wait statistics through the DBCC SQLPERF ('sys. dm_os_ latch_stats', CLEAR) statement.

So if you want to keep the statistical information for benchmarking/baseline purposes, you should export the statistical information from the sys.dm_os_latch_stats DMV to a table or some other format, such as an Excel spreadsheet, before restarting your SQL Server 2005 instance.

The sys.dm_os_wait_stats DMV covered earlier also contains statistical information relevant to latches. There are 24 different latch wait types that are monitored (see Table 2.2).

Tip 

Another way to see the different latch wait types available to you is by executing SELECT wait_type FROM sys.dm_os_wait_stats WHERE wait_type LIKE '%latch%'.

Table 2.2: SQL Server 2005 Wait Types
Open table as spreadsheet

Wait Type	Description
LATCH_DT	Destroy latch
LATCH_EX	Exclusive latch
LATCH_KP	Keep latch
LATCH_NL	Null latch
LATCH_SH	Shared latch
LATCH_UP	Update latch
PAGEIOLATCH_DT	Destroy buffer page I/O latch
PAGEIOLATCH_EX	Exclusive buffer page I/O latch
PAGEIOLATCH_KP	Keep buffer page I/O latch
PAGEIOLATCH_NL	Null buffer page I/O latch
PAGEIOLATCH_SH	Shared buffer page I/O latch
PAGEIOLATCH_UP	Update buffer page I/O latch
PAGELATCH_DT	Destroy buffer page latch
PAGELATCH_EX	Exclusive buffer page latch
PAGELATCH_KP	Keep buffer page latch
PAGELATCH_NL	Null buffer page latch
PAGELATCH_SH	Shared buffer page latch
PAGELATCH_UP	Update buffer page latch

So let’s finish up with an example. The following query shows page I/O latch wait statistics:

 SELECT wait_type,        waiting_tasks_count,        max_wait_time_ms FROM   sys.dm_os_wait_stats WHERE  wait_type like 'PAGEIOLATCH%' ORDER BY wait_type

This has the following example output:

 wait_type      waiting_tasks_count  max_wait_time_ms ---------      -------------------  ---------------- PAGEIOLATCH_DT 0                    0 PAGEIOLATCH_EX 5419                 690 PAGEIOLATCH_KP 0                    0 PAGEIOLATCH_NL 0                    0 PAGEIOLATCH_SH 18961                8332 PAGEIOLATCH_UP 520                  312

Resolving Latch Issues

Latch-specific problems tend to be reasonably rare. A lot of the time they indicate a lack of resources, but they could also indicate some sort of hardware problem. Watch out for latch timeouts for I/O operations that are taking too long to complete. If you get any latch-specific error messages or you have determined that your SQL Server 2005 instance is experiencing excessive latch waits or timeouts, you can try the following:

• Determine whether your SQL Server 2005 instance is experiencing any hardware bottlenecks.

• Check for any logged errors:

  • Error logs

  • Event logs

  • Hardware vendor logs

• Run any hardware vendor diagnostic tools.

• Reduce the workload on your SQL Server 2005 instance:

  • Tune your queries.

  • Turn off database autoshrink.

  • Turn off data autogrowth.

• Check to see whether turning off any of the following SQL Server 2005 configuration parameters helps:

  • Lightweight pooling

  • Priority boost

  • Set working set size

• Otherwise, you’ll be calling Microsoft Product Support!

Troubleshooting Locks

The key to troubleshooting locks is to understand the different types of lockable objects and types of locks that are available in SQL Server 2005. Although the basics for the lock manager and locking have not substantially changed since SQL Server 2000, each iteration of SQL Server has evolved from the previous release, so ensure you are familiar with SQL Server 2005’s locking architecture, and don’t assume anything from your knowledge of SQL Server 2000.

SQL Server 2005 Lock Architecture

The lock manager in SQL Server 2005 manages locks (funny that). It does this through an internal in-memory structure known as the lock hash table. The amount of memory allocated to this lock hash table is based on the amount of memory available on your SQL Server 2005 instance. Of course, this is a simplified view-very simplified.

Note 

You can of course override the default amount of memory allocated to lock-ing if you need to do so. You do this via trace flags, a topic not covered in detail in this book.

One of the more important “decisions” the lock manager has to make is what lock grain to use for locking data. What are the trade-offs between using a finer-grained lock versus a coarser-grained lock?

As an example, let’s imagine you want to modify a hundred records in a table that contains a million records. What lock grain should you use? The benefit of using a finer-grained lock (think of a row-level lock, as an example, placed on the hundred records) is that you create less contention within the table (users can access the rest of the records in the table), but you consume more lock resources (a hundred locks). Whereas a coarser-grained lock (table-level lock) uses less resources (one lock) but creates greater contention (no one else could access the table while you modified your hundred records).

SQL Server 2005 uses a lock escalation strategy, which means that the lock manager will escalate a finer-grained lock to a coarser-grained lock automatically as required.

Once a transaction has started, the lock manager can decide to escalate the locks used to a coarser grain so as to improve performance and conserve memory resources. The following thresholds trigger this lock escalation:

• Five thousand locks have been acquired on a single object for a single T-SQL statement.

  Note 

  If lock escalation cannot occur because of lock conflicts, SQL Server 2005 will attempt lock escalation again for every 1,250 new locks acquired.

• Memory consumed by locking exceeds 40 percent of non-AWE memory when the locks configuration option is set to 0.

• Memory consumed by locking exceeds 40 percent of allocated memory when the locks configuration option is set to a value of nonzero.

Note 

These thresholds can change in a service pack.

SQL Server 2005 does not escalate row-level locks to page-level locks but directly to table-level locks. Likewise, page-level locks are always escalated to table-level locks.

Tip 

You can monitor lock escalation in SQL Server Profiler through the Lock:Escalation event.

Before you look at the different types of locks available in SQL Server 2005, we need to discuss transaction isolation levels.

Transaction Isolation Levels

This is stock-standard stuff for any concurrent environment such as any relational database, and SQL Server 2005 is no exception. Basically we’re talking about the “I” in “ACID,” or how transactions are isolated from each other to ensure data consistency.

Here’s a bit of a backgrounder first, though. Fundamentally, any concurrent environment that allows concurrent transactions potentially faces the following data anomalies:

• Dirty reads   A dirty read involves reading data that has been modified by another transaction that has not yet been committed.

• Nonrepeatable reads   A nonrepeatable read occurs when a transaction reads some data and then goes off and does some other processing, and when it comes back to the original data that was read, it has changed. In other words, the data has changed between the first and subsequent read operations. Simple!

• Phantom values   A phantom value occurs when, again, a transaction reads some data and goes off and does some other processing, and then when it rereads the original data, a new value has appeared-a phantom. In other words, the data has had a new record inserted between the first and subsequent read operations. Again, simple!

As an aside, ANSI has defined a number of transaction isolation levels (TILs) that basically control how transactions can be isolated from each other in a concurrent environment. The TILs are cumulative, which means that a higher level does everything that the previous level did plus more.

Table 2.3 shows the equivalent SQL Server 2005 isolation level. You will lock at what the isolation levels protect against when we cover the SQL Server 2005 isolation levels.

Table 2.3: ANSI Transaction Isolation Levels
Open table as spreadsheet

ANSI TIL	SQL Server 2005 Equivalent
0	READ UNCOMMITTED
1	READ COMMITTED
2	REPEATABLE READ
3	SERIALIZABLE

So as you can see, SQL Server 2005 supports all four ANSI TILs. In fact, it has since SQL Server 7.0. What is new in SQL Server 2005 is that there are a few new “variations” of them. So, let’s go through the isolation levels supported by SQL Server 2005:

• Read uncommitted   Read uncommitted isolation allows transactions to read uncommitted data. The transaction basically ignores any acquired locks and does not issue any shared locks for read operations.

  Warning 

  Be careful with using read uncommitted isolation mode. We see a lot of database solutions out there (especially web-based) that use read uncommitted isolation because they want to improve performance. This is not the correct reason to be going to read uncommitted isolation; you are reading dirty data, after all!

• Read committed   Read committed isolation prevents dirty reads from occurring. A transaction will have to wait until an incompatible lock is released. With read committed isolation, read operations acquire shared locks for the duration of the read operation.

• Read committed isolation does not prevent nonrepeatable reads or phantom values.

  Note 

  Read committed isolation is the default behavior for SQL Server 2005.

• Read committed snapshot   Read committed snapshot isolation (RCSI) is a completely new addition to SQL Server 2005. RCSI SQL Server 2005 uses row versioning based on the data’s previously committed value to prevent dirty reads. No row or page locks are required. Obviously, the benefit is that transactions do not have to wait for existing transactions to complete.

  Note 

  Read committed snapshot isolation is invoked at the database level via the ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON statement.

• Repeatable read   Repeatable read isolation prevents dirty reads and nonrepeatable reads from occurring. Under repeatable read isolation, SQL Server 2005 holds shared locks for the duration of the transaction, thus guaranteeing that a read is repeatable.

• Repeatable read isolation does not prevent phantom values.

• Serializable   Serializable isolation prevents dirty reads, nonrepeatable reads, and phantom values. With serializable isolation, SQL Server 2005 places range locks on the data being read, thus preventing insertion of new data until the transaction completes.

  • Snapshot   Snapshot isolation (SI) also prevents dirty reads, nonrepeatable reads, and phantom values. It does this by taking a snapshot of the data at the time the transaction was started.

Note 

Snapshot isolation needs to be invoked at both the data level via the ALTER DATABASE ... SET ALLOW_SNAPSHOT_ISOLATION ON and the session level using the SET TRANSACTION ISOLATION LEVEL SNAPSHOT.

Note 

The difference between SI and RCSI (if you are curious) is that with RCSI you work with data that was committed at the beginning of the T-SQL statement, whereas with SI you work with data that was committed at the beginning of the T-SQL transaction.

Table 2.4 summarizes the data anomalies prevented by the various isolation modes supported by SQL Server 2005.

Table 2.4: Data Anomalies Prevented by Isolation Level
Open table as spreadsheet

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom
Read Uncommitted	No	No	No
Read Committed and Read Committed Snapshot	Yes	No	No
Repeatable Read	Yes	Yes	No
Serializable Snapshot	Yes	Yes	Yes

The following syntax shows you how to change the isolation level at the session level:

 SET TRANSACTION ISOLATION LEVEL     { READ UNCOMMITTED     | READ COMMITTED     | REPEATABLE READ     | SNAPSHOT     | SERIALIZABLE     } [ ; ]

Alternatively, the following example shows you how to change the UnitedNations database to support RCSI:

 ALTER DATABASE UnitedNations SET READ_COMMITTED_SNAPSHIT ON

Warning 

Be careful with going to higher isolation levels because you are effectively creating greater contention. You are placing “stronger” locks and/or holding them for a greater period of time.

Warning 

Be especially careful with using the new variations of RCSI and SI. Make sure you understand the implications of reading “snapshot” data.

SQL Server 2005 Locks

Turn your attention to the SQL Server 2005 lock architecture, which falls under the auspices of the lock manager. You will find that with each release of SQL Server Microsoft has, at the least, modified the way in which locking behaves.

We’ll start by talking about the various entities that can lock objects inside the SQL Server 2005 database engine. Table 2.5 shows the different types of entities that can request a lock from the lock manager.

Table 2.5: SQL Server 2005 Entities That Can Request Locks
Open table as spreadsheet

Entity	Description
CURSOR	A cursor
EXCLUSIVE_TRANSACTION_WORKSPACE	Exclusive part of the transaction workspace
TRANSACTION	A transaction
SESSION	A user session
SHARED_TRANSACTION_WORKSPACE	Shared part of the transaction workspace

As you would expect, different types of objects can be locked inside the SQL Server 2005 database engine. If you are familiar with earlier version of SQL Server, you will notice that some of the lockable objects, such as the HOBT, are new to SQL Server 2005.

Table 2.6 shows the different types of locks supported by SQL Server 2005.

Table 2.6: SQL Server 2005 Resource Types
Open table as spreadsheet

Resource Type	Description
_TOTAL	Information for all locks
ALLOCATION_ UNIT	Allocation unit
APPLICATION	Application-specific resource
DATABASE	The entire database
EXTENT	Eight contiguous pages (64KB)
FILE	Database file
HOBT	Heap or B-tree; an allocation unit used to describe a heap or B-tree
KEY	Row lock within an index used to protect key ranges in serializable transactions
METADATA	Catalog information about an object
OBJECT	Database object
PAGE	Data or index page (8KB)
RID	Row identifier, which represents a single row within a table
TABLE	Entire table, including the indexes

The various SQL Sever 2005 entities can request different types of locks depending on the type of operation they want to perform on an object within the SQL Server 2005 database engine. Table 2.7 shows the different types of request modes available in SQL Server 2005.

Table 2.7: SQL Server 2005 Lock Request Modes
Open table as spreadsheet

Request Mode	Description
BU	Bulk-Update lock
I	Intent lock
IS	Intent-Shared lock
IU	Intent-Update lock
IX	Intent-Exclusive lock
RangeS_S	Shared Range-Shared resource lock
RangeS_U	Shared Range-Update resource lock
RangeI_N	Insert Range-Null resource lock
RangeI_S	Insert Range-Shared resource lock
RangeI_U	Insert Range-Update resource lock
RangeI_X	Insert Range-Exclusive resource lock
RangeX_S	Exclusive Range-Shared resource lock
RangeX_U	Exclusive Range-Update resource lock
RangeX_X	Exclusive Range-Exclusive resource lock
S	Shared lock
Sch-M	Schema-Modification lock
Sch-S	Schema-Stability lock
SIU	Shared Intent-Update lock
SIX	Shared Intent-Exclusive lock
U	Update lock
UIX	Update Intent-Exclusive lock
X	Exclusive lock

Generally speaking, read operations require a shared lock, whereas data modifications require exclusive locks. The names imply that you can have multiple shared locks on the same resource, whereas you can have only one exclusive lock on a resource. Obviously, you don’t want people accessing data that you are currently modifying-not ordinarily anyway.

Another factor is the duration that locks are held on a resource. Again, generally speaking, the lock manager holds locks for read operations only for the duration of the read operation itself, whereas for data modifications the locks are held for the entire transaction. This is an important distinction!

To tie it all together, we need to show the compatibility of the locks within the SQL Server 2005 database engine. A lock is said to be compatible in SQL Server 2005 if the lock that a particular transaction is requesting can be acquired while the other lock is being held by another transaction.

Table 2.8 show the SQL Server 2005 lock compatibility. A C indicates a conflict, an I indicates illegal, and an N indicates no conflict. Finally, Table 2.9 shows that different types of lock requests in SQL Server 2005.

Table 2.8: SQL Serer 2005 Lock Compatibility
Open table as spreadsheet

	NL	SCH-S	SCH-M	S	U	X	IS	IU	IX	SIU	SIX	UIX	BU	RS-S	RS-U	RI-N	RI-S	RI-U	RI-X	RX-S	RX-U	RX-X
NL	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N
SCH-S	N	N	C	N	N	N	N	N	N	N	N	N	N	I	I	I	I	I	I	I	I	I
SCH-M	N	C	C	C	C	C	C	C	C	C	C	C	C	I	I	I	I	I	I	I	I	I
S	N	N	C	N	N	C	N	N	C	N	C	C	C	N	N	N	N	N	C	N	N	C
U	N	N	C	N	C	C	N	C	C	C	C	C	C	N	C	N	N	C	C	N	C	C
X	N	N	C	C	C	C	C	C	C	C	C	C	C	C	C	N	C	C	C	C	C	C
IS	N	N	C	N	N	C	N	N	N	N	N	N	C	I	I	I	I	I	I	I	I	I
IU	N	N	C	N	C	C	N	N	N	N	N	C	C	I	I	I	I	I	I	I	I	I
IX	N	N	C	C	C	C	N	N	N	C	C	C	C	I	I	I	I	I	I	I	I	I
SIU	N	N	C	N	C	C	N	N	C	N	C	C	C	I	I	I	I	I	I	I	I	I
SIX	N	N	C	C	C	C	N	N	C	C	C	C	C	I	I	I	I	I	I	I	I	I
UIX	N	N	C	C	C	C	N	C	C	C	C	C	C	I	I	I	I	I	I	I	I	I
BU	N	N	C	C	C	C	C	C	C	C	C	C	N	I	I	I	I	I	I	I	I	I
RS-S	N	I	I	N	N	C	I	I	I	I	I	I	I	N	N	C	C	C	C	C	C	C
RS-U	N	I	I	N	C	C	I	I	I	I	I	I	I	N	C	C	C	C	C	C	C	C
RI-N	N	I	I	N	N	N	I	I	I	I	I	I	I	C	C	N	N	N	N	C	C	C
RI-S	N	I	I	N	N	C	I	I	I	I	I	I	I	C	C	N	N	N	C	C	C	C
RI-U	N	I	I	N	C	C	I	I	I	I	I	I	I	C	C	N	N	C	C	C	C	C
RI-X	N	I	I	C	C	C	I	I	I	I	I	I	I	C	C	N	C	C	C	C	C	C
RX-S	N	I	I	N	N	C	I	I	I	I	I	I	I	C	C	C	C	C	C	C	C	C
RX-U	N	I	I	N	C	C	I	I	I	I	I	I	I	C	C	C	C	C	C	C	C	C
RX-X	N	I	I	C	C	C	I	I	I	I	I	I	I	C	C	C	C	C	C	C	C	C

Table 2.9: SQL Server 2005 Lock Request Status
Open table as spreadsheet

Lock Request Status	Description
GRANT	The lock was granted to process.
WAIT	The process is being blocked by another process.
CNVT	The lock is being converted to another type of lock.

Monitoring Locks

Phew…that was a stretch! But what did you expect? Locking is always going to be a complex, if not difficult, subject. Thankfully, it is not difficult in SQL Server 2005 to detect what kinds of locks are being acquired in the database engine, what transactions are being blocked, the blocking transactions, and whether any deadlocks have occurred. So, we’ll now go through all the tools and techniques you can use to determine what is going on with your SQL Server 2005 instance as far as locks are concerned.

Activity Monitor

Perhaps the easiest way to look at what is going on with your SQL Server 2005 instance is to use the Activity Monitor utility within SSMS. It enables you to quickly and concisely see what processes are connected to your SQL Server 2005 instance, what locks they have acquired, or alternatively what objects they are waiting for locks to be released on. Figure 2.8 shows the Process Info page of the Activity Monitor.

Figure 2.8: Activity Monitor, Process Info page

Performance Object Counters

The SQLServer : Locks performance object has a number of counters that can be used for monitoring the lock manager:

• Average Wait Time (ms)

• Lock Requests/sec

• Lock Timeouts (timeout > 0)/sec

• Lock Timeouts/sec

• Lock Wait Time (ms)

• Lock Waits/sec

The resource types shown in Figure 2.6 represent an instance of each of these performance object counters.

SQL Server Profiler Event Classes

You can capture a rich set of information through SQL traces or SQL Server Profiler. The lock event classes will enable you to monitor the locks that are being acquired, canceled, or released. They also allow you to monitor lock escalation and whether your queries/transactions are timing out. Table 2.10 shows the lock event classes.

Table 2.10: Lock Event Classes
Open table as spreadsheet

Event Class	Description
Lock:Acquired	This event class indicates that a lock has been acquired.
Lock:Cancel	This event class indicates that a lock has been canceled.
Lock:Escalation	This event class indicates that a lock escalation has occurred.
Lock:Released	This event class indicates that a lock has been released.
Lock:Timeout	This event class indicates that a request for a lock on a resource has timed out because of an incompatible lock acquired by another transaction.
Lock:Timeout (timeout > 0)	This event class is the same as the Lock:Timeout event class except it does not include any event where the timeout value (@@LOCK_TIMEOUT) is 0.

Tip 

Watch out for excessive lock escalation using the Lock:Escalation event class.

System Stored Procedures

A number of system stored procedures have been with SQL Server since “forever” and will quickly show you what processes are running on your SQL Server 2005 instance, what locks they have acquired, or whether they are waiting for locks to be released. Table 2.11 shows the system stored procedures.

Table 2.11: SQL Server 2005 Locking System Stored Procedures
Open table as spreadsheet

Stored Procedure	Description
sp_who	Reports basic information similar to what the Activity Monitor shows.
sp_who2	Reports richer information compared to the sp_who system stored procedure. This is undocumented.
sp_lock	Reports basic information about locks.

The sp_who system stored procedure represents a quick way of determining whether a particular process is being blocked by another process. If users are complaining about slow transactions or unresponsive queries, try executing the following command to determine whether their process is being blocked by other processes:

 EXEC sp_who ACTIVE ; GO

The ACTIVE parameter excludes sessions that are waiting for the next command from a user connection. Look for a SPID value in the blk column, which would indicate that the process is being blocked by that SPID.

Note 

The sp_lock system stored procedure is being deprecated in a future release of SQL Server. You should use the sys.dm_tran_locks DMV instead.

Figure 2.9 shows a sample output of the sp_who system stored procedure. Figure 2.10 shows a sample output of the sp_who2 system stored procedure.

Figure 2.9: sp_who

Figure 2.10: sp_who2

DMVs

We have already discussed the sys.dm_os_wait_stats DMV and how it shows the various tasks that are waiting to acquire a particular lock on a resource before proceeding

The main DMV to query to see what the lock manager is up to is sys.dm_tran_locks. It returns all the currently active processes that have either had a lock granted to them or are waiting for a lock to be acquired. Table 2.12 shows the columns returned by the sys.dm_ tran_locks DMV.

Table 2.12: sys.dm_tran_locks DMV Columns
Open table as spreadsheet

Column	Description
resource_type	Lock request type. (See Table 2.6.)
resource_subtype	Lock request subtype. (Not all lock resource types have lock request subtypes.)
resource_database_id	Resource database ID.
resource_description	Resource database.
resource_associated_entity_id	Entity ID associated with the lock resource.
resource_lock_partition	Partitioned lock resource lock partition ID.
request_mode	Lock request mode. (See Table 2.7)
request_type	Lock request type.
request_status	Lock request status.
request_reference_count	Approximate number of times the requesting entity has requested this resource.
request_lifetime	Reserved.
request_session_id	Lock request’s session ID.
request_exec_context_id	Execution context ID of the process that currently owns this request.
request_request_id	Lock request ID.
request_owner_type	Lock request owner type. (See Table 2.5)
request_owner_id	Lock request owner ID.
request_owner_guid	Lock request owner GUID.
request_owner_lockspace_id	Reserved.
lock_owner_address	Internal data structure used to track lock request. (This is related to the resource_address column in the sys.dm_os_waiting_tasks DMV.)

Figure 2.11 shows sample output of the sys.dm_tran_locks DMV.

Figure 2.11: Output of sys.dm_tran_locks

We hope this ties together all of the theory that we have discussed. As you can see from the output, the DMV returns the resource type, request type, and request status, as shown in the earlier tables.

System Tables

For completeness sake, you can still query these backward-compatible SQL Server 2000 compatibility views:

• sys.sysprocesses

• sys.syslockinfo

Resolving Locking Issues

Remember that it is in the nature of a concurrent system to have contention issues, given enough concurrent users. Nevertheless, there are good programming techniques that your database developers can use to minimize such contention. Generally speaking, SQL Server 2005 knows best; by default, it finds the right balance between protecting data and allowing concurrent access to data.

Remember that it is a dynamic system, so it should adjust its behavior depending on the current activity and resources available. Lock escalation is really not a bad idea.

Nevertheless, you can use several techniques to help you resolve any locking problems that you are experiencing. What you will employ will obviously depend on the locking issues you are experiencing, be it running out of memory, excessive contention, and so on. These techniques include the topics in the following sections.

Warning 

Be careful when changing the default locking behavior of the SQL Server 2005 database engine. Make sure you understand the implications and impact of your potential changes. Remember that you are always making a trade-off between the overhead on the SQL Server 2005 database engine and concurrency of the data (or “accuracy” in the case of dirty reads, or even RCSI and SI).

Setting Database Options

You can set a number of database options that will have the effect of disabling locking for the database.

• READ_ONLY   Setting a database to read-only will ensure that the lock manager does not bother with maintaining locks within the database because there is no contention.

  Note 

  There is a common “myth” on newsgroups and other sources that making a file group read-only will disable locking for that particular file group. This is wrong, as can be easily tested.

• SINGLE_USER   Likewise, setting a database to single-user will ensure that the lock manager does not bother with maintaining locks within the database because there is no contention.

• READ_COMMITTED_SNAPSHOT   As discussed, setting the database to RCSI will make the SQL Server 2005 database engine use row versioning instead.

Changing Isolation Levels

Another potential technique that you can use to resolve any locking issues is to change the isolation level being used. All of the following isolation levels would improve the concurrency of your database solution because shared locks will not be acquired for read operations:

• Read Uncommitted

• Read Committed Snapshot

• Snapshot Isolation

But don’t forget that there is a trade-off, as discussed earlier, so be particularly careful with these options.

Tip 

Always try to use the lowest possible TIL you can afford to minimize the amount of contention that is created.

Disabling Lock Escalation

You can disable lock escalation altogether if you want to do so. You do this via the following trace flags:

• Trace flag 1211 disables lock escalation completely.

• Trace flag 1224 disables lock escalation based on the number of locks acquired but will enable lock escalation when more than 40 percent of the memory allocated to locks is exceeded or 40 percent of the non-AWE memory is consumed by locks.

Disabling Row-Level/Page-Level Locks

A little known feature in SQL Server 2005 (and SQL Server 2000) is the ability to disable row-level or page-level locking at the table or index option. You do this through the sp_indexoption system stored procedure.

The following example shows page-level locks being disallowed for the [Production] .[Product] table in the AdventureWorks database:

 USE AdventureWorks ; GO EXEC sp_indexoption     'Production.Product',     'DisallowPageLocks',     TRUE ; GO

Using Optimizer Hints

As with the query hints we discussed earlier, you can direct a number of table hints at the query optimizer that control locking. As an example, you could use table hints so that the SQL Server 2005 database engine uses page-level or table-level locks instead of row-level locks. This would reduce the amount of memory used for locking but would potentially create greater contention. As you can see, it’s always this trade-off between overhead on the SQL Server 2005 database engine and concurrency.

Table 2.13 shows the more common table hints that are available in SQL Server 2005 that will affect locking.

Tip 

Don’t forget the READPAST table hint. It is one that is not well remembered.

Table 2.13: SQL Server 2005 Table Hints
Open table as spreadsheet

Table Hint	Description
HOLDLOCK	Equivalent to SERIALIZABLE.
NOLOCK	Equivalent to read uncommitted isolation level.
NOWAIT	Equivalent to SET LOCK_TIMEOUT 0.
PAGLOCK	Use page-level locks.
READCOMMITTED	Equivalent to read committed isolation level dependent on RCSI.
READCOMMITTEDLOCK	Equivalent to read committed isolation level irrespective of RCSI.
READPAST	Indicates that rows locked by other transactions should be ignored.
READUNCOMMITTED	Equivalent to read uncommitted isolation level.
REPEATABLEREAD	Equivalent to repeatable read isolation level.
ROWLOCK	Use row-level locks.
SERIALIZABLE	Equivalent to serializable isolation level.
TABLOCK	Use table-level locks.
TABLOCKX	Use table-level exclusive locks.
UPDLOCK	Use update locks.
XLOCK	Use exclusive locks.

The following example shows a query where the [Production].[Product] table is queried. Any data that is locked will be ignored. This could return an “incomplete” result set in this case.

 USE Adventureworks ; GO SELECT * FROM [Production].[Product] WITH (READPAST)

Warning 

Overriding the optimizer via table or query hints is considered a “last resort,” so you should try other techniques first. Generally speaking, SQL Server 2005 does know best!

All of these strategies represent the more common ones that are implemented. They are just a starting point. Please appreciate that there are other “tricks up the sleeve” that the experienced DBA can utilize.

Real World Scenario-Caveat Emptor: Overriding the Optimizer

One consulting engagement I had was to tune an auctioning database solution for the wool industry in New Zealand. It was the usual story of poor performance-a legacy application that had been developed on an earlier version of SQL Server and had been upgraded as is, no in-house knowledge of SQL Server, and of course a history of hiring consultants. Of course, there was no documentation or any change management processes in place.

The performance was generally poor, queries were slow for an auctioning system, and there was evidence of both internal and external memory pressures.

In any case, I eventually found a lot of different problems, but a couple stuck in my mind. In particular, the contract developers (I think they came from a FoxPro background) had decided that they knew better than the SQL Server database engine and had put in optimizer hints for every single statement, especially ROWLOCK and a few others. Every single T-SQL statement!

In summary, I recommended removing all the optimizer hints. Performance was restored.

There are a number of morals here, including not always applying knowledge and experience from one database engine to another.

But be particularly careful of overriding the optimizer, make sure you document your reasons, and reinvestigate the reason whenever you upgrade SQL Server through a release, edition, or even service pack.

-Victor Isakov

Troubleshooting Deadlocks

Deadlocks occur when two or more processes each have some resource locked in SQL Server 2005 and they cannot “continue on” until they get access to the other process’s resources (which happen to be locked). It’s a catch-22 situation. These resources will wait potentially indefinitely, because SQL Server 2005’s default timeout is infinity!

Well, in the case of a deadlock occurring, SQL Server 2005 automatically chooses one process as the victim and kills it off automatically. A 1205 error will be generated. The victim’s transaction will be rolled back, whereas the other process is allowed to complete.

Note 

So, what transaction will be killed off? Well, there’s no simple answer, because there are always exceptions, but the transaction that has done the least amount of work will be killed off. Why? Think about it: there is less to roll back!

Figure 2.12 shows an example of a deadlock in SQL Server 2005 and how the SQL Server 2005 engine has chosen a particular process as a victim.

Figure 2.12: Resolving a deadlock

A lot of sites will not have too many problems with deadlocks; nevertheless, you should know how to monitor them if the need arises.

Monitoring Deadlocks

You do not have as rich a set of tools and options to monitor deadlocks as with other locking issues. But remember that you can still use the various techniques, such as the DBCC OPENTRAN command and the sys.dm_tran_locks DMV, that you have encountered elsewhere that relate to locking.

Having said that, SQL Server 2005 has some sexy new ways of monitoring deadlocks.

SQL Server Profiler Event Classes

You have a number of event classes available for your SQL Server Profiler/SQL traces. Table 2.14 shows these specific event classes.

Table 2.14: Deadlock Related Lock Event Classes
Open table as spreadsheet

Event Name	Description
Deadlock Graph	Provides an Extensible Markup Language (XML) description of a deadlock
Lock:Deadlock Chain	Is produced for each participant in a deadlock and captures additional information such as the owner, lock mode, owner, and resource type to help troubleshoot deadlocks
Lock:Deadlock	Indicates that a transaction has been rolled back as a deadlock victim because it tried to acquire a lock on a resource that caused a deadlock to occur

The sexy new event class is the deadlock graph, which returns a picture. Wow! Well, maybe not, but Microsoft made a big deal of it at all the TechEd events in 2006. In any case, Figure 2.13 shows a deadlock graph being captured in SQL Server Profiler.

Figure 2.13: Deadlock graph in SQL Server Profiler

Tip 

You can export the deadlock graph to an XML file (XDL) to share with your friends.

SQL Server Error Log

You can also optionally configure SQL Server 2005 to report additional deadlock information to the SQL Server error log. Two trace flags are relevant for configuring the SQL Server 2005 database engine in this way. Both these trace flags are global, which means they have to be enabled via the -T switch for the SQLSERVR.EXE service in SQL Server Configuration Manager.

Trace flag 1204 returns the queries, their resources, and the lock type that got deadlocked. The following shows an example of the output of trace flag 1204 in the SQL Server error log:

 Deadlock encountered .... Printing deadlock information Wait-for graph Node:1 KEY: 8:72057594045071360 (920111cc4128) CleanCnt:3 Mode:X     Flags: 0x0 Grant List 0: Owner:0x03BC6900 Mode: X        Flg:0x0 Ref:0 Life:02000000     SPID:53 ECID:0 XactLockInfo: 0x05F7A274 SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 2 Input Buf: Language Event: UPDATE Production.Product SET ListPrice = ListPrice * 1.2 Requested By:    ResType:LockOwner Stype:'OR'Xdes:0x051D7690 Mode: U     SPID:52 BatchID:0 ECID:0 TaskProxy:(0x05A2A374)     Value:0x3bbd820 Cost:(0/256180) Node:2 KEY: 8:72057594055622656 (010086470766) CleanCnt:2 Mode:X     Flags: 0x0  Grant List 0:    Owner:0x03BC6940 Mode: X        Flg:0x0 Ref:0     Life:02000000 SPID:52 ECID:0 XactLockInfo: 0x051D76B4    SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 2    Input Buf: Language Event: UPDATE Production.ProductListPriceHistory SET ListPrice = ListPrice * 1.1   Requested By:    ResType:LockOwner Stype:'OR'Xdes:0x05F7A250 Mode: U     SPID:53 BatchID:0 ECID:0 TaskProxy:(0x05CA6374)     Value:0x3bbd3e0 Cost:(0/101236) Victim Resource Owner:  ResType:LockOwner Stype:'OR'Xdes:0x05F7A250 Mode: U     SPID:53 BatchID:0 ECID:0 TaskProxy:(0x05CA6374)     Value:0x3bbd3e0 Cost:(0/101236)

Trace flag 1222 returns pretty much the same information but in a different order/format. We’ll let you examine the output to see the difference. The following shows an example of the output of trace flag 1204 in the SQL Server error log:

 deadlock-list  deadlock victim=process8796a8   process-list    process id=process8796a8 taskpriority=0 logused=101236    waitresource=KEY: 8:72057594055622656 (010086470766)    waittime=2468 ownerId=1583 transactionname=    user_transaction lasttranstarted=2007-03-20T22:46:30.843    XDES=0x64704d8 lockMode=U schedulerid=1 kpid=5596    status=suspended spid=54 sbid=0 ecid=0 priority=0    transcount=2 lastbatchstarted=2007-03-20T22:59:48.187    lastbatchcompleted=2007-03-20T22:46:30.873 clientapp=    Microsoft SQL Server Management Studio - Query    hostname=VAIOTX650P hostpid=3788 loginname=    VAIOTX650P\ace isolationlevel=read committed (2)    xactid=1583 currentdb=8 lockTimeout=4294967295    clientoption1=671090784 clientoption2=390200     executionStack frame procname=adhoc line=2 stmtstart=4 sqlhandle=    0x02000000d5a5f2017e2ec8dc1a7e3a7156d48038a31f5417 1 numeric(2,1))UPDATE [Production].[Product] set    [ListPrice] = [ListPrice]*@1 frame procname=adhoc line=2 stmtstart=4 sqlhandle=    0x020000007af3b50dd02114b4e23b2800ce631225b2675158 UPDATE Production.Product SET ListPrice = ListPrice * 1.2     inputbuf UPDATE Production.Product SET ListPrice = ListPrice * 1.2    process id=process879888 taskpriority=0 logused=256180    waitresource=KEY: 8:72057594045071360 (920111cc4128)    waittime=793437 ownerId=1470 transactionname=    user_transaction lasttranstarted=2007-03-20T22:46:13.983    XDES=0x52af378 lockMode=U schedulerid=1 kpid=5900    status=suspended spid=53 sbid=0 ecid=0 priority=0    transcount=2 lastbatchstarted=2007-03-20T22:46:37.263    lastbatchcompleted=2007-03-20T22:46:14.047 clientapp=    Microsoft SQL Server Management Studio - Query hostname=    VAIOTX650P hostpid=3788 loginname=VAIOTX650P\ace    isolationlevel=read committed (2) xactid=1470    currentdb=8 lockTimeout=4294967295    clientoption1=671090784 clientoption2=390200     executionStack frame procname=adhoc line=2 stmtstart=34 sqlhandle=    0x020000003f05e11a988aaacb1398c8ba136a53be22c8da7d UPDATE [Production].[ProductListPriceHistory] set     [ListPrice] = [ListPrice]*@1 frame procname=adhoc line=2 stmtstart=4 sqlhandle=    0x020000000fae5d186499b953acc5dc4ffb76a9f1e8a96b61 UPDATE Production.ProductListPriceHistory SET ListPrice = ListPrice * 1.1     inputbuf UPDATE Production.ProductListPriceHistory SET ListPrice = ListPrice * 1.1   resource-list    keylock hobtid=72057594055622656 dbid=8    objectname=AdventureWorks.Production.Product    indexname=PK_Product_ProductID id=lock3b71a00 mode=X    associatedObjectId=72057594055622656     owner-list owner id=process879888 mode=X     waiter-list waiter id=process8796a8 mode=U requestType=wait    keylock hobtid=72057594045071360 dbid=8 objectname=    AdventureWorks.Production.ProductListPriceHistory    indexname=PK_ProductListPriceHistory_ProductID_StartDate    id=lock3b75980 mode=X    associatedObjectId=72057594045071360     owner-list owner id=process8796a8 mode=X     waiter-list waiter id=process879888 mode=U requestType=wait

We hope you enjoyed examining it!

Resolving Deadlock Issues

Any concurrent environment will experience deadlocks. It’s unavoidable. Such is the nature of the beast. However, you can minimize deadlocks using the following techniques:

• Keep transactions as short as possible.

• Use the lowest transaction isolation level you can afford.

• Use optimizer hints.

• Access database objects in the same order.

• Avoid user interaction in transactions.

• Consider using RSCSI.

• Consider using SI.

• Consider using bound connections.

• Consider using MARS.

• Use appropriate indexing strategies.

Deadlock Priority

SQL Server 2005 automatically picks which transaction will be killed off in a deadlock scenario. Students always ask whether you can influence its decision. Well…yes, you can, at the session level, through the SET DEADLOCK_PRIORITY command.

Basically, SQL Server 2005 has 21 levels of deadlock priority, from –10 to 10. A lower priority level will be killed in preference of a higher-level priority, all things being equal.

The SET DEADLOCK_PRIORITY command supports the following deadlock priorities:

• LOW (–5)

• NORMAL (0)

• HIGH (5)

• A numeric value

Tip 

You can see a session’s deadlock priority through the deadlock_priority column of the sys.dm_exec_sessions DMV.

So, you can use the SET DEADLOCK_PRIORITY HIGH command in the “more important” stored procedures and batches that you would rather not be killed off if a deadlock occurs.

Note 

In earlier versions of SQL Server, you had only the LOW deadlock priority, so you could only write stored procedures and batches that were the preferred victim.

Let’s finish up by troubleshooting deadlocks in Exercise 2.4.

Exercise 2.4: Troubleshooting Deadlocks

You’ll examine how you can generate deadlock graphs in SQL Server Profiler for further analysis.

1. Open SQL Server Management Studio, and connect to your SQL Server 2005 instance using Windows authentication.

2. Start the SQL Server Profiler through the Tools menu.

3. Give the trace a name, as shown here.

   

4. Click the Events Selection tab.

5. Clear all the events, as shown here.

   

6. Click the Show All Events check box.

7. Expand the Locks events, and select the Deadlock Graph, Lock:Deadlock, and Lock:Dead-lock Chain events, as shown here.

   

8. Click the Run button.

9. Switch to SQL Server Management Studio, click the New Query button, and connect to your SQL Sever 2005 instance.

10. Type the following query into the query pane, and execute it:

     USE AdventureWorks ; GO BEGIN TRAN UPDATE Production.Product SET ListPrice = ListPrice * 1.1

11. Click the New Query button, and connect to your SQL Sever 2005 instance a second time.

12. Type the following query into the second query pane, and execute it:

     USE AdventureWorks ; GO BEGIN TRAN UPDATE Production.ProductListPriceHistory SET ListPrice = ListPrice * 1.2

13. Switch to the first query pane, modify the query as shown here, and then execute it. You should notice that the query does not complete because the [Production] .[ProductListPriceHistory] is locked by the second query pane.

     USE AdventureWorks ; GO /* BEGIN TRAN UPDATE Production.Product SET ListPrice = ListPrice * 1.1 */ UPDATE Production.ProductListPriceHistory SET ListPrice = ListPrice * 1.1

14. Switch to the second query pane, modify the query as shown here, and then execute it:

     USE AdventureWorks ; GO /* BEGIN TRAN UPDATE Production.ProductListPriceHistory SET ListPrice = ListPrice * 1.2 */ UPDATE Production.Product SET ListPrice = ListPrice * 1.2

15. This should cause a deadlock. You should see an error message similar to the one shown here. (If you do not, check the first query pane.)

     Msg 1205, Level 13, State 51, Line 2 Transaction (Process ID 69) was deadlocked on lock resources with    another process and has been chosen as the deadlock victim. Rerun    the transaction.

16. Switch to the SQL Server Profiler, and stop the trace.

17. Click the Deadlock Graph event class to examine the deadlock graph.

    

18. When you have finished, exit SQL Server Profiler.

19. Switch to SQL Server Management Studio.

20. Close the second query pane.

21. In the first query pane, modify the query as shown here, and then execute it:

     USE AdventureWorks ; GO /* BEGIN TRAN UPDATE Production.Product SET ListPrice = ListPrice * 1.1 UPDATE Production.ProductListPriceHistory SET ListPrice = ListPrice * 1.1 */ ROLLBACK TRAN

22. Exit SQL Server Management Studio.