A High Performance DBMS Minimizes Contention

Overview

• Scalability means that throughput increases linearly with increased resources.

• Throughput refers to the volume of transactions the RDBMS can process in a fixed time period; the higher the volume, the more work your system can perform.

• Response time is the time it takes for the RDBMS to complete a task to process a query and return results to the user.

The key to building a fully scalable RDBMS is finding and removing "hot spots," or contention points. Contention points are areas in the system that require concurrent access by multiple users or multiple database servers. Most contention points are found in the areas of cache management, logging, and locking, which can cause transactions to slow each other down. To find and eliminate these hot spots from a Sybase environment, the SQL Server 11 performance team built a very sophisticated performance monitor to simulate a wide variety of user workloads and thoroughly evaluate every major component of SQL Server. The result is an optimized RDBMS that fully exploits CPU, memory, and I/O resources to provide real-world application scalability across a very wide range of hardware configurations.

This chapter describes how SQL Server 11 minimizes contention in the areas of caching, logging, and locking to achieve scalable high performance. It discusses the following topics:

• ``DBMS Components''

• ``Cache Management''

• ``Transaction Management and Logging''

• ``Lock Management''

The DBMS engine takes care of parsing, optimizing compiling, and executing Transact-SQL statements and returning results.

Resource managers are a set of functions that manage global system resources such as pages, buffers, and data structures. The DBMS uses these functions to manipulate different system resources. Resource managers and other specialized tasks rely on the kernel component (see Chapter 4, "A Multiprocessing Kernel") to perform low-level functions. The resource managers handle:

• Query processing

• Caching

• Indexing

• Transaction processing

• Locking

Cache Management

SQL Server has a unique procedure cache technology as well. After a stored procedure is called the first time, the precompiled procedure is held in the procedure cache so it is ready to run the next time it is called. This greatly reduces compile time for frequently run stored procedures.

This section describes SQL Server's data and procedure cache technology.

The Data Cache and Named Caches

These user-defined caches tailor SQL Server's use of the global data cache to meet the system's current work-load requirements. Figure 3-1 illustrates how you might divide the global cache in a hypothetical production environment. If you have a small, frequently accessed Customer_List table, you might want to devote to it a buffer cache that contains enough buffers to keep the entire table in memory. Because no other table shares that cache, the table's pages remain in memory for quick access. If, on the other hand, you have a large Payment_History table that is only used for low priority batch processing, you can allocate a small cache and assign the Payment_History table to it. Because queries on this table only use buffers from the small cache, only old Payment_History pages are read. These reads do not cause valuable pages (such as those in the frequently accessed Customer_List table) to be removed from cache.

Figure 3-1: SQL Server 11 cache architecture

The system procedures that allow you to do this type of configuration are sp_cacheconfig, sp_poolconfig, sp_bindcache, and sp_unbindcache. These system procedures let you reconfigure named caches "on the fly" to match changing workload requirements even within the same day's operations.

For comparison, Figure 3-2 shows a standard DBMS cache architecture in which all objects occupy a single global cache. Queries must compete with each other for a single buffer manager semaphore.

Figure 3-2: Standard DBMS cache architecture

The graph in Figure 3-3 illustrates that using multiple caches reduces contention on the buffer manager semaphore, improving scalability. The logical memory manager allows the system to optimally exploit the available memory resources. This is particularly beneficial in tuning small memory systems; it also helps to get the most out of large memory configurations.

Figure 3-3: The Logical Memory Manager reduces contention

Named Caches and Performance

• When changes are made to a cache by any user process, a spinlock denies all other processes access to the cache. Although spinlocks are very brief, they can slow performance in multiprocessor systems with high transaction rates. When you configure multiple caches, each is controlled by a separate spinlock, increasing concurrency on multiprocessor systems.

• You can configure caches so they are large enough to hold critical tables and indexes. This keeps other server activity from contending for cache space and speeds up queries that use these tables because the hit rate is 100 percent; that is, the needed pages are always found in cache.

• You can bind a "hot" table¯a table in high demand by user applications¯to one cache and indexes on the table to other caches to increase concurrency.

• You can create a cache large enough to hold the "hot pages" of a table where a high percentage of the queries reference only a portion of the table. For example, if a table contains data for a year, but 75 percent of the queries reference data from the most recent month (about 8 percent of the table), configuring a cache of about 10 percent of the table size provides room to keep the most frequently used pages in cache and leaves some space for less frequently used pages as well.

• You can assign tables or databases used in decision support systems (DSS) to specific caches with large I/O configured. This keeps DSS applications from contending for cache space with online transaction processing (OLTP) applications. DSS applications typically access large numbers of sequential pages, and OLTP applications typically access relatively few random pages.

• You can bind the tempdb database to its own cache. Because all processes that create worktables or temporary tables use tempdb, binding it to its own cache means that other processes don't have to compete for cache access because tempdb is heavily accessed. Proper sizing of tempdb's cache can keep most tempdb activity in memory for many applications. If this cache is large enough, you can even avoid I/O for tempdb.

• You can bind a database's log to a cache, again reducing contention for cache space and access to the cache.

Cache Optimization Strategies

• The set forceplan on option forces SQL Server to join the tables in a query in the order in which they are listed in the from clause.

• The set table count option allows you to specify the number of tables that are considered at once as joins are optimized.

• The system procedure sp_cachestrategy disables and re-enables cache strategies and large I/O for individual objects.

• Clauses for select, delete, and insert statements allow you to specify the index, cache strategy, or I/O size for a query.

• The set prefetch option allows you to enable or disable large I/O for a session.

• Improved CPU utilization

• Decreased need for buffer washing during transaction processing

• Faster checkpoints

• Shorter recovery time

Data Cache Management Summary

Procedure Cache

Figure 3-4: The procedure cache

The memory allocated for the procedure cache holds the optimized query plans for all batches, including any triggers. See ``Stored Procedures'' for more detailed information about how the procedure cache works.

Transaction Management and Logging

Figure 3-5 shows the data cache during a transaction. The data cache holds the data pages that the transaction is using and all other log pages and data pages that currently reside in the data cache.

The transaction log is a write-ahead log. When a user issues a SQL statement that modifies any data in the database, SQL Server automatically writes the change to the log before it writes the change to the actual data page in cache. This strategy ensures that SQL Server can reverse all changes made by a transaction if any part of the transaction fails.

Figure 3-5: Writing data changes

SQL Server logs transactions on a per-database basis rather than on a per-user basis as some database managers do. Because all database users share the same transaction log, there are fewer writes to disk, which is an automatic performance gain. In fact, in some situations the amount of I/O per user actually decreases as the number of users increases.

Changing the log I/O size using sp_logiosize can improve performance by reducing the number of times that SQL Server writes transaction log pages to disk. By default, SQL Server defines the log I/O size of a database to be 4K. If the transaction log for a database is bound to the default cache or a user-defined cache that does not contain a 4K memory pool, SQL Server sets the log I/O size to 2K (a 2K memory pool is always present in any cache).

Minimizing Log Contention with the User Log Cache

Because every transaction involves the logging system, its efficiency is the most important factor for high transaction throughput. An analytical study of the logging subsystem revealed that the real bottleneck for transaction throughput was not the log file on disk, but the log semaphore in memory. As database engines are added and the transaction volume increases, the contention on the log semaphore dramatically increases, resulting in reduced scalability.

System 11's logging mechanism boosts overall performance by enabling user log caches (ULCs) (also referred to as the private log caches (PLCs) in some documents) and group commits. There is one user log cache for each configured user connection. SQL Server buffers the user transaction log records with user log caches, which reduces the contention at the end of the transaction log. When a user log cache becomes full or when another event occurs (such as when the transaction completes), SQL Server "flushes" all log records from the user log cache to the database transaction log. You can configure the size of all user log caches for your server.

A user log cache improves I/O and scalability on multiprocessor systems because it is only necessary to acquire the log semaphore once for all log records. Expanded group commits decrease the system overhead by permitting the logging mechanism to log more transactions in a single I/O.

Because more than one process can access the contents of a user log cache when determining a possible "flush," SQL Server uses spinlocks to protect the user log caches. A spinlock is an internal locking mechanism that prevents a second process from accessing a data structure when another process is using it. All processes trying to access the resource must wait (or spin) until the lock is released. SQL Server uses sp_configure's configurable user log cache spinlock ratio parameter to specify the ratio of user log caches per user log cache spinlock.

Because the log records for a complete transaction are immediately transferred through the log semaphore, contention on the log semaphore becomes almost nonexistent. Figure 3-6 shows that using the ULC eliminates contention on the append_log semaphore. Contention alleviated by the ULC dramatically increases SQL Server's transaction throughput.

Figure 3-6: The ULC eliminates log contention

Lock Management

This gives you a great deal of control over how the lock manager behaves. System 11's rich set of configuration parameters lets you fine tune lock behavior to get the best performance possible from your system as it evolves.

Page Locks and Table Locks

SQL Server uses page-level locking, and only escalates to table locks when it detects that a large portion of the table will be affected, as in the case of a bulk load or an update operation that affects many rows throughout the table. In addition, Sybase supplies extensions such as intent and demand locks to extend concurrency.

Lock Promotion

Spinlock Ratios

Deadlocks

Freelocks

For most SQL Server configurations, you can increase the percentage of locks assigned to the engine freelock lists and improve application performance.

Rows Per Page, Contention, and Concurrency

max_rows_per_page applies to the data pages of an unindexed table or the leaf pages of an index and is retained and used even after the table or index is created. SQL Server uses the max_rows_per_page value whenever it adds or deletes rows.

Page Allocation

For example, when inserting data into a 10GB table that has 120 OAM pages and only one unused data page:

• A page utilization percent of 100 tells SQL Server to scan through all 120 OAM pages to locate the unused data page.

• A page utilization percent of 95 allows SQL Server to allocate a new extent to the object, because 95 is lower than the ratio of used pages to used and unused pages.

You can selectively choose isolation level 0 for a query in a transaction using the at isolation clause as follows:

select *

from titles

at isolation read uncommitted