SQL Server Blocking and Deadlocks

The more blocking happens on SQL Server the less concurrency the system. A certain amount of short blocks are unavoidable but too many blocks for longer periods of time can degrade performance and increase the chances of SQL Server deadlocks.

SQL Server blocking

SQL Server blocking occurs when one connection (user process or application process) places a lock on a table (or a number of rows) and a second connection attempts to read or modify the data under the lock. Depending on the type of the lock, this can cause the second connection to wait until the first connection releases its lock. A blocked connection waits indefinitely for the blocking connection to release its lock.

The more blocking happens on the server the less concurrency the system achieves. A certain amount of blocking is unavoidable but too many blocks for longer periods of time can degrade the performance of SQL Server.

SQL Server deadlocks

The combination of two blocked connections where the first is blocking the second and the second is blocking the first is called a deadlock. Since deadlocks are not naturally resolved with time, SQL Server automatically kills one of the connection (Deadlock victim) once it detects a deadlock. This allows the other connection to continue with its transaction.

Although deadlocks can be caused by two short blocks (Fraction of a second), it is often the long blocks that increase the chances of a deadlock to happen.

Blocks escalating to deadlocks

The following diagram shows the sequence of events leading to a deadlock. Consider two applications (A1, A2) accessing two different table (T1, T2):

Event 1: A1 places a lock on T1 inside its transaction and continues to execute other statements

Event 2: A2 places a lock on T2 inside its transaction and continues to execute other statements

Event 3: A1 attempts to place a lock on T2 (Needs to access T2 before it can finish the transaction) but has to wait for A2 to release its lock

At this point, a block is created since A2 is blocking A1

Event 4: While A1 is waiting, A2 attempts to place a lock on T1 (Needs to access T1 before it can finish its own transaction)

A deadlock is created since two connections have blocked one another. SQL Server automatically resolves the deadlock by choosing one of the connections as a deadlock victim and killing it.

SQL Server Performance Counters

The most important performance counters to watch when tuning SQL Server performance are CPU usage, memory usage, cache hit ratios, and disk I/O. Even though CPU capacity and available memory are crucial, disk I/O is frequently the cause of bottlenecks in most systems. Paying extra attention to disk I/O and cached disk access is worthwhile.

SQL Server CPU utilization

Measuring the amount of time the CPU is busy running non-idle SQL Server threads is an important aspect of SQL Server performance monitoring and tuning. A continually high CPU usage rate may indicate the need for a CPU upgrade or the addition of multiple processors. Alternatively, a high CPU usage rate may indicate a poorly tuned application or SQL code. Optimizing the application can lower CPU utilization. A consistent state of 80 to 90 percent may indicate the need for a CPU upgrade or the addition of more processors. When you examine processor usage, consider the type of work the instance of SQL Server is performing. If SQL Server is performing a lot of calculations, such as queries involving aggregates or memory-bound queries that require no disk I/O, 100 percent of the processor’s time may not seem unusual. If this causes the performance of other applications to suffer, try changing the timing of the workload.

SQL Server memory utilization

By default, SQL Server changes its memory requirements dynamically based on available system resources. If SQL Server needs more memory, it queries the operating system to determine whether free physical memory is available and then uses the memory available. If SQL Server does not need the memory currently allocated to it, it releases the memory to the operating system. However, the option to dynamically use memory can be overridden using the min server memory, max server memory, and working set size server configuration options.

The more memory your server has the more cached disk reads and writes it can perform. Lack of system memory can cause high non-cached disk reads and writes. Adding memory to your server can help lower physical disk access.

SQL Server Cache Hit Ratios

SQL Server cache hit ratios indicate the percentage of data pages read from the SQL Server cache (memory) vs. those read from the physical disk. The SQL Server cache consists of the buffer cache and the procedure cache.

The Buffer cache is the memory pool used to store SQL Server data. The buffer cache hit ratio indicates the percentage of data pages read from the buffer cache vs. physical disk. Even though CPU capacity and available memory are crucial, disk I/O is frequently the cause of bottlenecks in most systems. Paying extra attention to cached disk I/O is crucial when monitoring the performance of SQL Server. A value of 90% cache hit ratio indicates pages were retrieved from memory 90% of the time. The other 10% were read from disk. A consistent value below 90% indicates that more physical memory is needed on the server.

The Procedure cache is the memory pool used to store SQL Server execution plans. The procedure cache hit ratio indicates the percentage of execution plan pages read from the procedure cache vs. disk.

SQL Server disk I/O

SQL Server disk I/O is frequently the cause of bottlenecks in most systems. The I/O subsystem includes disks, disk controller cards, and the system bus. If disk I/O is consistently high, consider:

  • Move some database files to an additional disk or server.
  • Use a faster disk drive or a redundant array of inexpensive disks (RAID) device.
  • Add additional disks to a RAID array, if one already is being used.
  • Tune your application or database to reduce disk access operations. Consider index coverage, better indexes, and/or normalization.

Microsoft SQL Server uses Microsoft Windows I/O calls to perform disk reads and writes. SQL Server manages when and how disk I/O is performed, but the Windows operating system performs the underlying I/O operations. Applications and systems that are I/O-bound may keep the disk constantly active.

Different disk controllers and drivers use different amounts of CPU time to perform disk I/O. Efficient controllers and drivers use less time, leaving more processing time available for user applications and increasing overall throughput.

SQL Server Performance Tips

SQL Server performance tuning can consume a considerable amount of time and effort. The following list is a quick guideline that you should keep in mind when designing and developing SQL Server database applications:

User Defined Functions (UDF)

Refrain from using user defined functions (UDF) in a select statement that may potentially return many records. UDFs are executed as many times as there are rows in a returned result. A query that returns 100,000 rows calls the UDF 100,000 times.

SQL Server table indexes

Create SQL statements that utilize defined table indexes. Using indexes minimizes the amount of table scan which in most cases will be much slower than an index scan.

Multiple disks

The single best performance increase on a SQL Server computer comes from spreading I/O among multiple drives. Adding memory is a close second. Having many smaller drives is better than having one large drive for SQL Server machines. Even though the seek time is faster in larger drives, you will still get a tremendous performance improvement by spreading files, tables, and logs among more than one drive.

Disk controllers

Different disk controllers and drivers use different amounts of CPU time to perform disk I/O. Efficient controllers and drivers use less time, leaving more processing time available for user applications and increasing overall throughput.

SQL Server foreign keys

Ensure that all your tables are linked with foreign keys. foreign keys enhance the performance of queries with joins. Database tables inside each application are naturally related. Islands of tables are rarely needed if your application’s business logic is well defined.

SQL Server primary keys

Ensure that every table has a primary key. if you can’t find a natural set of columns to serve as a primary key, create a new column and make it a primary key on the table.

Processor (CPU)

When you examine processor usage, consider the type of work the instance of SQL Server is performing. If SQL Server is performing a lot of calculations, such as queries involving aggregates or memory-bound queries that require no disk I/O, 100 percent of the processor’s time can be used. If this causes the performance of other applications to suffer, try changing the workload of the queries with aggregates.

Advantages of SQL Server 2005 64-bit

64-bit processors are expected to dominate the markets by the end of 2007. It is clear that the industry is moving towards the 64-bit platform fast, especially for server hardware. The move towards the 64-bit platform has been driven by lower hardware prices, especially after the introduction of x64 architecture by both Intel (EM64T) and AMD (AMD64). Before x64, the only 64-bit architecture available for SQL Server was the IA-64 architecture from Intel. The IA-64 architecture is very scalable but since it is not based on the 32-bit x86 processor, it cannot run native 32-bit applications.

The following table illustrates the various processors available in the market from Intel and AMD. Note that SQL Server 2005 (64-bit) is available for both x64 and the IA-64 Itanium:

Intel AMD
x64 Xeon with EM64T Athlon 64
Pentium 4 with EM64T Opteron
IA-64 (Intel only) Itanium N/A
Itanium 2

Advantages of 64-bit computing

64-bit computing has many advantages over the 32-bit architecture. The following is a list of 64-bit advantages:

Large memory addressing: The 64-bit architecture offers a larger directly-addressable memory space. SQL Server 2005 (64-bit) is not bound by the 4 GB memory limit of 32-bit systems. Therefore, more memory is available for performing complex queries and supporting essential database operations. This greater processing capacity reduces the penalties of I/O latency by utilizing more memory than traditional 32-bit systems.

Enhanced parallelism: The 64-bit architecture provides advanced parallelism and threading. Improvements in parallel processing and bus architectures enable 64-bit platforms to support larger numbers of processors (up to 64) while providing close to linear scalability with each additional processor. With a larger number of processors, SQL Server can support more processes, applications, and users in a single system.

Same application code: You can migrate to SQL Server 2005 (64-bit) and achieve new levels of application scalability without having to change any application code. Migrating current databases to a 64-bit environment can be achieved by performing a simple database backup from the 32-bit server and restoring the backup on a 64-bit server. You can also use detach (sp_detach_db) and attach (sp_attach_db) to move a database to a 64-bit server. Microsoft invested a lot of effort to make sure that database file structures remain compatible between the 32-bit editions and the 64-bit editions of SQL Server. Applications that access 32-bit servers can connect to and access 64-bit servers without changing application code. This gives users a great deal of flexibility in determining the appropriate rate of 64-bit adoption for their organizations. It is also possible to migrate data back to the 32-bit version for SQL Server 2005 databases. However, downgrading to SQL Server 7.0 from SQL Server 2005 (64-bit) is not supported.

Reduced TCO: Total cost of ownership (TCO) can be reduced with a 64-bit system through consolidation, reduced licensing fees, and higher transaction throughput. By leveraging the tremendous scalability and memory addressability of the 64-bit architecture, SQL Server 2005 (64-bit) can host increasingly large and complex database and analysis applications, and support server consolidation on large-scale SMP systems with many processors. Applications using SQL Server 2005 (64-bit) databases can benefit from massive in-memory data caching as well as larger data structures for multiple parallel workloads, concurrent user connections, plan cache, sort space, and lock memory.

Server consolidation: The additional processor and memory headroom available within the 64-bit environment enable significant scale-up and server consolidation opportunities. A large number of consolidated databases can fit into a single SQL instance that has sufficient memory to support the data structures (connections, locks, hash, and so on) needed for multiple coexisting applications. The same benefits also apply to consolidation of multiple instances onto a single physical server.

Improved bus architecture and cache: The improved bus architecture enhances performance by moving more data between cache and processors in shorter periods. A larger on-die cache allows for faster completion of user requests and more efficient use of processor time.

Memory addressing with 64-bit vs. AWE

Inherently, A 32-bit system can manage a maximum of 4 GB of memory. This limits the addressable memory space for Windows 2000 and Windows 2003 systems to 4 GB. With 2 GB reserved for the operating system by default, only 2 GB of memory remains for SQL Server. To allow a 32-bit system to address memory beyond the 4 GB limit, a set of memory management extensions to the Microsoft Win32 API called Address Windowing Extensions (AWE) is used. Using AWE, applications can acquire physical memory as non-paged memory, and then dynamically map views of the non-paged memory to the 32-bit address space. By using AWE, SQL Server Enterprise Edition can address up to 32 GB of physical memory on Windows Server 2003 Enterprise Edition and up to 64 GB of memory on Windows Server 2003 Datacenter Edition.

Although AWE provides a way to use more memory, it imposes overhead and adds initialization time leading to weaker performance as opposed to 64-bit systems. Also, the additional memory addressability with AWE is available only to the SQL Server’s data buffers. It is not available to other memory consuming database operations such as caching query plans, sorting, indexing, joins, or for storing user connection information. It is also not available on other engines such as Analysis Services. In contrast, SQL Server 2005 (64-bit) makes memory available to all database processes and operations. Using the 64-bit version on either IA64 or x64 hardware, a SQL Server instance can address up to 1 terabyte of memory; the current maximum amount of physical memory supported by Windows Server 2003 SP1. This memory is available to all components of SQL Server, and to all operations within the database engine.

Understanding SQL Server Memory Internals & Cache

Like all database servers, Microsoft SQL Server thrives on memory. Each instance of Microsoft SQL Server has its own memory address space. The SQL Server address space (Virtual memory allocation by Windows) consists of two main components: executable code and memory pool. Let’s examine each of these components in more details.

SQL Server executable code

Executable code is basically SQL Server’s own EXEs and DDLs that are loaded into the address space. The following is breakdown of the various components:

  • SQL Server Engine
  • Open Data Services
  • Server Net-Libraries
  • Distributed Queries
  • Extended Stored Procedures (DLLs)
  • OLE Automation Stored Procedures

SQL Server memory pool

The memory pool is the unit of memory that an instance of SQL Server uses to create and manage data structures relating to client requests. The following data structures are allocated in the memory pool of an instance of SQL Server:

  • Buffer Cache: This is the pool of memory pages into which data pages are read. An important indicator of the performance of the buffer cache is the Buffer Cache Hit Ratio performance counter. It indicates the percentage of data pages found in the buffer cache as opposed to disk. A value of 95% indicates that pages were found in memory 95% of the time. The other 5% required physical disk access. A consistent value below 90% indicates that more physical memory is needed on the server.
  • Procedure Cache: This is the pool of memory pages containing the execution plans for all Transact-SQL statements currently executing in the instance. An important indicator of the performance of the procedure cache is the Procedure Cache Hit Ratio performance counter. It indicates the percentage of execution plan pages found in memory as opposed to disk.
  • Log Caches: This is the pool of memory used to read and write log pages. Each log has a set of cache pages. The log caches are managed separately from the buffer cache to reduce the synchronization between log and data buffers.
  • Connection Context: Each connection has a set of data structures that record the current state of the connection. These data structures hold items such as parameter values for stored procedures, cursor positioning information, and tables currently being referenced.
  • System-level Data Structures: These are data structures that hold data global to the instance, such as database descriptors and the lock table.

The buffer cache, procedure cache, and log caches are the only memory elements whose size is controlled by SQL Server.

A very important aspect to watch for is whether SQL Server is using the maximum memory available on the system (assuming the system is dedicated to SQL Server). A system with a fully utilized memory may be prone to performance bottlenecks when competition for resources increases. Prepared Transact-SQL statements, for example, may suffer when the procedure cache is unable to expand due to fully utilized buffer caches.

 

Related Articles

The 4 Pillars of SQL Server Performance: CPU, Memory, Disk I/O, & Cache Hit Ratio

The 4 Pillars of SQL Server Performance: CPU, Memory, Disk I/O, & Cache Hit Ratios

The most important performance counters to watch when tuning SQL Server performance are CPU usage, memory usage, cache hit ratios, and disk I/O. Even though CPU capacity and available memory are crucial, disk I/O is frequently the cause of bottlenecks in most systems. Paying extra attention to disk I/O and cached disk access is worthwhile.

SQL Server CPU utilization

Measuring the amount of time the CPU is busy running non-idle SQL Server threads is an important aspect of SQL Server performance monitoring and tuning. A continually high CPU usage rate may indicate the need for a CPU upgrade or the addition of multiple processors. Alternatively, a high CPU usage rate may indicate a poorly tuned application or SQL code. Optimizing the application can lower CPU utilization. A consistent state of 80 to 90 percent may indicate the need for a CPU upgrade or the addition of more processors. When you examine processor usage, consider the type of work the instance of SQL Server is performing. If SQL Server is performing a lot of calculations, such as queries involving aggregates or memory-bound queries that require no disk I/O, 100 percent of the processor’s time may not seem unusual. If this causes the performance of other applications to suffer, try changing the timing of the workload.

SQL Server memory utilization

By default, SQL Server changes its memory requirements dynamically based on available system resources. If SQL Server needs more memory, it queries the operating system to determine whether free physical memory is available and then uses the memory available. If SQL Server does not need the memory currently allocated to it, it releases the memory to the operating system. However, the option to dynamically use memory can be overridden using the min server memory, max server memory, and working set size server configuration options.

The more memory your server has the more cached disk reads and writes it can perform. Lack of system memory can cause high non-cached disk reads and writes. Adding memory to your server can help lower physical disk access.

SQL Server Cache Hit Ratios

SQL Server cache hit ratios indicate the percentage of data pages read from the SQL Server cache (memory) vs. those read from the physical disk. The SQL Server cache consists of the buffer cache and the procedure cache.

The Buffer cache is the memory pool used to store SQL Server data. The buffer cache hit ratio indicates the percentage of data pages read from the buffer cache vs. physical disk. Even though CPU capacity and available memory are crucial, disk I/O is frequently the cause of bottlenecks in most systems. Paying extra attention to cached disk I/O is crucial when monitoring the performance of SQL Server. A value of 90% cache hit ratio indicates pages were retrieved from memory 90% of the time. The other 10% were read from disk. A consistent value below 90% indicates that more physical memory is needed on the server.

The Procedure cache is the memory pool used to store SQL Server execution plans. The procedure cache hit ratio indicates the percentage of execution plan pages read from the procedure cache vs. disk.

SQL Server disk I/O

SQL Server disk I/O is frequently the cause of bottlenecks in most systems. The I/O subsystem includes disks, disk controller cards, and the system bus. If disk I/O is consistently high, consider:

  • Move some database files to an additional disk or server.
  • Use a faster disk drive or a redundant array of inexpensive disks (RAID) device.
  • Add additional disks to a RAID array, if one already is being used.
  • Tune your application or database to reduce disk access operations. Consider index coverage, better indexes, and/or normalization.

Microsoft SQL Server uses Microsoft Windows I/O calls to perform disk reads and writes. SQL Server manages when and how disk I/O is performed, but the Windows operating system performs the underlying I/O operations. Applications and systems that are I/O-bound may keep the disk constantly active.

Different disk controllers and drivers use different amounts of CPU time to perform disk I/O. Efficient controllers and drivers use less time, leaving more processing time available for user applications and increasing overall throughput.

 

Related Articles

Understanding SQL Server Memory Internals & Cache

Tuning tempdb For Better SQL Server Performance

One of the most important areas of SQL Server performance is the tempdb database. It maintains temporary user tables and intermediate query results used to prepare and optimize queries. The tempdb in essence is very similar to a user database but it is not intended to persist data. The tempdb is replaced with a copy of the model database every time SQL Server starts. The architecture of the tempdb in SQL Server 2005 did not change much from that of SQL Server 2000, however, its usage has been optimized and expanded in SQL Server 2005.

Optimizations to tempdb introduced since SQL Server 2005

SQL Server 2005 has a plethora of new features among which internal optimizations tend to be lost. These are some of the enhancements made to tempdb in SQL Server 2005:

  • Internal worktables for execution plans across multiple executions are truncated down to 9 pages to save space.
  • Local temporary tables and table-valued functions are all cached when used in a stored procedure, function or trigger. Items that are used frequently stay in cache while least-used items are dropped to save cache space.
  • Logging in tempdb has been lowered for certain changes to reduce the amount of I/O generated. While UPDATE queries still log the original data and the new data (updated values), INSERT queries only log the new data and DELETE queries only log what was deleted.
  • Physical file contention in the tempdb database has been minimized with proportionate fills. Each tempdb file is filled at the same time so each physical file on a separate spindle will benefit from parallel efficiently.
  • Temporary tables that are dropped from tempdb are handled in the background to reduce waiting by the host application.

Monitoring the tempdb database using performance counters

SQL Server 2005 provides a number of performance counters that can be used to monitor space usage in tempdb.

  • The Database: Log File(s) Size(KB) performance counter returns the cumulative size of all the log files in the database. This size can grow if you have not set a maximum size for the log in tempdb.
  • The Database: Log File(s) Used (KB) performance counter returns the cumulative used size of all log files in the database. A large active portion of the log in tempdb can be a warning sign that a long transaction that is preventing log cleanup.
  • The Free Space in tempdb (KB) (SQL Server 2005) performance counter tracks free space in tempdb in kilobytes. Administrators use this counter to determine if tempdb is running low on free space so they can take appropriate corrective action. This counter accounts for space allocated by all three types of objects in tempdb.
  • The Version Store Size (KB) (SQL Server 2005) performance counter monitors the size in KB in both version stores. If a version store is not shrinking, it implies that a long-running transaction is preventing version store cleanup.
  • The Version Generation Rate (KB/s) (SQL Server 2005) performance counter monitors the version generation rate in kilobytes per second (KBps) in both version stores.
  • The Version Cleanup Rate (KB/s) (SQL Server 2005) performance counter monitors the version cleanup rate in KBps in all version stores. If the version cleanup rate is lower than the version generation rate, the version store will use more and more space in tempdb. However, if the version cleanup rate is 0 but the version generation rate is not, there is probably a long-running transaction that is preventing the version store cleanup.

Monitoring the tempdb database using dynamic management views

SQL Server 2005 provides a number of dynamic management views (DMVs) that can be used to monitor space usage in tempdb. The DMVs provide rich features to track tempdb space usage. You can track tempdb space usage at the instance level, the session level, or by individual task (a task is also known as a batch).

sys.dm_db_file_space_usage

This DMV returns space allocation information for the files associated with tempdb. The allocation information is grouped by object category (user, internal, and version store) for the instance. The following code is an example.

SELECT
SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage

The following sample output was generated when a query was executed with a hash-join that created a work table.

usr_obj_kb internal_obj_kb version_store_kb freespace_kb mixedextent_kb
8192 4608 0 3840 1024

You can use the output to learn how tempdb space is being used. The following points will help you analyze the output.

A higher % allocation for user objects implies that objects that are created by applications (for example, global and local temporary tables and variables) are the major consumers of tempdb. This is not necessarily a cause of concern.

A higher % allocation for internal objects implies that the query plans make heavy use of tempdb. This is not necessarily a problem, but you may want to look at the query plans to see if alternate query plans can be generated by creating indexes or by re-formulating the queries so as to minimize tempdb space usage.

A higher % allocation for the version store implies that version store cleanup cannot keep pace with version generation. See if a long-running transaction is preventing version store cleanup. Or, a high transaction throughput might be generating a large number of versions per minute. The background task cleans up versions every minute.

sys.dm_db_session_file_usage

This DMV tracks the historical allocation/deallocation of pages in tempdb for the active sessions. A session is established when a user connects to the database. The session is active until the connection is terminated. During the course of the session, the user submits one or more batches. This DMV tracks the tempdb space usage only by the completed batches. The following code example shows the top five sessions that have allocated a maximum space for user objects and internal objects in tempdb. This represents the batches that have already completed, but the code lists sessions with heavy tempdb space use. You could look at these first if you want to minimize tempdb consumption. Note that this tempdb space usage does not take into account the impact of this session on the version store space.

SELECT top 5 *
FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC

sys.dm_db_task_space_usage

This DMV tracks the allocation/deallocation of tempdb pages by the currently executing tasks (also called batches). This is extremely useful when you are running out of space in tempdb. Using this DMV, you can identify tasks with heavy tempdb space use and optionally kill them. You can then analyze why these tasks require heavy tempdb space usage and take corrective action. You can join this DMV with other DMVs to identify the SQL statement and its corresponding query plan for deeper analysis. The following query shows the top five tasks that are currently executing tasks and consuming the most tempdb space. The tempdb space usage returned does not allow for the impact on space consumed by the version store.

SELECT top 5 *
FROM sys.dm_db_task_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC