Minding Memory

Starting with SQL Server 7.0, a central goal of the SQL Server development team has been to produce an easy-to-use system that requires little care for daily operations. To meet this goal, the team designed SQL Server to be a self-tuning system that dynamically manages system resources such as memory. However, although this self-tuning behavior reduces the daily need to actively manage your server's memory utilization, SQL Server professionals still need to understand how SQL Server uses and manages memory. Monitoring and analyzing SQL Server's memory utilization can provide vital information for performance tuning and capacity planning. As members of Microsoft's SQL Server Development Customer Advisory Team,we use our understanding of SQL Server's memory utilization to discover and solve problems in various aspects of customers' applications. Memory-based analysis often reveals application-design problems such as excessive or improper use of ad hoc queries, use of unnecessarily high isolation levels, improper security for stored procedures and views, or poor coding practices in database-update constructs. Memory analysis might even lead to the redesign of the logical and physical schema of an application to correct problems such as excessive denormalization or improper indexing. In this article, the first of a two-part series, we look at how SQL Server uses memory and ways to monitor such utilization. In the follow-up article, we'll show you how to use information about memory utilization to tune performance.

Memory Internals

For an overview of SQL Server's memory-management architecture, see the Microsoft article "Inside SQL Server 2000's Memory Management Facilities" by Ken Henderson. As Henderson explains, SQL Server organizes most of its memory into a buffer pool divided into 8KB chunks. SQL Server uses memory from the buffer pool to support a variety of needs including the database page cache, the plan cache, workspace memory, and locks. You can view data about these various memory-utilization functions by checking the Performance Monitor (PerfMon) counters in the SQL Server:Buffer Manager object, the SQL Server:Cache Manager object, and the SQL Server:Memory Manager object. In addition, the SQL Server:Access Methods, SQL Server:Locks, and SQL Server:SQL Statistics PerfMon objects contain useful related information. Let's explore the different types of SQL Server memory utilization and which counters give you information about each type.

Database page cache. The most common use of memory in SQL Server is for the database page cache (aka data cache or buffer cache). SQL Server databases are composed of 8KB pages that SQL Server can cache in memory in the database page cache. The PerfMon counter SQL Server:Buffer Manager:Database pages shows the number of pages in the database page cache. SQL Server:BufferManager:Buffer Cache Hit Ratio measures the fraction of database page requests that SQL Server is satisfying from the database page cache versus the total number of requests. In releases before SQL Server 2000 Service Pack 3 (SP3), the Buffer Cache Hit Ratio counter represents the accumulated hit ratio since the SQL Server instance started. Therefore, as the time since the instance startup increases, the counter becomes less indicative of the current hit ratio and hence less useful as a diagnostic tool. In SQL Server 2000 SP3 and later, this counter represents the current hit ratio—that is, the ratio measured over the last few thousand page accesses.

There's no predefined limit for how much of the buffer pool can be filled with database pages. However, SQL Server uses a modification of the classic clock algorithm to eliminate from the cache database pages that haven't been accessed recently. The SQL Server:Buffer Manager:Page life expectancy counter measures the average number of seconds a page would stay in the buffer cache without a new access before SQL Server would purge it from the database page cache.

Plan cache. When you send a T-SQL statement (or query) to SQL Server, SQL Server first compiles the request through an optimization process that results in a query plan. SQL Server holds recently used query plans in the plan cache (aka procedure or SQL cache) and reuses them if it needs to execute the same statement in the future.

Query plans come in two varieties: compiled plans and executable plans. Compiled plans are the product of query optimization and are read-only data structures that contain essential information about how to execute a statement. For example, a compiled plan would specify which tables and indexes to access, in what order to access them, and how to join, aggregate, or sort the data. Multiple concurrently executing queries can share the same compiled plan structure; thus, the plan cache has at most one compiled plan for a given statement at any point in time.

However, SQL Server can't execute compiled plans; to execute a statement, SQL Server must have an executable plan. SQL Server creates an executable plan by starting from a compiled plan and adding parameter and variable values, session-specific information such as user information and set options, and global runtime information such as the state of server CPU and memory utilization. Executable plans also contain small amounts of memory space that SQL Server uses for storing database row values while processing them through the executable plan. Because executable plans contain information and memory space specific to the particular invocation of the statement, SQL Server needs a different copy for each currently active invocation. Thus, the plan cache might hold several copies of the executable plan for a statement at a particular point in time.

You can examine the current contents of the plan cache by executing a SELECT statement against the master..syscacheobjects system table. For each plan currently cached, this system table contains the first 128 characters of the SQL text for the statement, the plan type (compiled or executable), the objectid of the matching database object in the case of stored-procedure or trigger plans, the reference and use counts for the plan, and the set options and database ID the plan was compiled under. Figure 1 shows an example of how to retrieve information about all the stored-procedure plans currently cached in the system.

As we mentioned, at any point in time, the plan cache has at most one copy of a compiled plan for a particular SQL statement. However, SQL Server might need to produce different query plans for the same statement based on the current settings of some set options (e.g., whether you've set ANSI_NULLS ON or OFF). Similarly, the database you're working with affects the plan for a particular statement because the same SQL text might refer to completely different database objects depending on which database you execute the statement in. Thus, it's more accurate to say that SQL Server has at most one compiled plan in the plan cache for a particular combination of database and plan-affecting set options. Figure 2 lists the plan-affecting set options.

In most cases, reusing cached query plans is desirable because doing so reduces the amount of CPU time SQL Server spends compiling statements. However, in some circumstances, plan reuse might be detrimental to performance—for example, when the best plan for a stored procedure depends on the particular values of parameters passed in. We'll explore the merits of plan reuse more in our follow-up article.

SQL Server can cache and reuse all compiled plans and most executable plans. However, the presence of some query operators prevents SQL Server from caching certain executable plans. For example, plans that contain the HASH operator or the PARALLELISM operator are not cacheable in SQL Server 2000. In such cases, SQL Server must generate the executable plan from the compiled plan each time you invoke the statement. However, generating an executable plan from a compiled plan is much less expensive than producing a compiled plan in the first place.

The SQL Server:Buffer Manager:Procedure Cache Pages PerfMon counter tallies the number of pages that the plan cache currently occupies. Counters in the SQL Server: Cache Manager object provide further details about the plan cache. In particular, the Cache Hit Ratio counter measures the fraction of times SQL Server found a matching plan for an SQL statement in the plan cache; however, this ratio is the accumulated value since the instance of SQL Server was last started and, thus, gets progressively less sensitive over time. Other counters in this object tally the number of different plans of various types and the number of buffer pool pages those plans occupy. As with the database page cache, there is no predefined limit to the size of the plan cache. The buffer pool's clock algorithm purges less frequently used plans from the plan cache to make room for other memory needs.

Workspace memory. As we mentioned, executable plans contain small amounts of memory space for buffering runtime database row values while SQL Server is processing those values through the executable plan. For example, a SCAN operator in an executable plan would allocate enough space to store all the columns for one row of the table or index that operator is scanning. This fixed-size space is all the memory the SCAN operator will ever need, regardless of how many rows exist in the table, even if the number of rows varies from one run of the execution plan to the next. In contrast, any SORT and HASH operators in the executable plan need workspace memory in various amounts depending on how much data they process. For example, a SORT that needs to sort 1 million rows of 100 bytes each ideally needs approximately 100MB of workspace memory to sort all the rows in one pass. Because SORT and HASH operators can require a lot of workspace memory, SQL Server has a special process for handling queries that contain such operators. Before execution, a query that needs workspace memory enters a memory-grant queue to get a workspace-memory grant. This grant is permission to allocate a particular amount of memory at a later time during the query's execution. Based on the current state of the system—such as the amount of memory available to SQL Server, how busy the buffer pool is with database-page I/O activity, and how much memory SQL Server has given in grants to other sessions—a query might have to wait in the workspace-memory-grant queue before it can start execution. The SQL Server: Memory Manager object includes the following workspace-related PerfMon counters: Memory Grants Pending, which tells you the number of waiting queries; Memory Grants Outstanding, which counts the number of queries that have grants and are currently executing; Granted Workspace Memory (KB), which measures the total amount of grants SQL Server has made to running queries; and Maximum Workspace Memory (KB), which measures the maximum amount of memory SQL Server is currently willing to devote to workspace-memory grants. In general, SQL Server is able to devote between 25 and 75 percent of the buffer pool's virtual memory to workspace memory. The value varies based on the demand for memory for other uses (such as database page cache and plan cache). If demand is low, SQL Server devotes a larger fraction of memory to workspace memory needs; if demand is high, SQL Server devotes a smaller fraction to workspace memory.

Locks. SQL Server uses two structures to implement locking: lock blocks and lock-owner blocks. A lock block (which requires 64 bytes on 32-bit systems and 96 bytes on 64-bit systems) represents a particular resource such as a row or a page that SQL Server has locked. A lock-owner block (which requires 32 bytes on 32-bit systems and 56 bytes on 64-bit systems) represents a particular lock holder (i.e., a transaction). The SQL Server:Memory Manager:Lock Blocks Allocated and SQL Server:Memory Manager:Lock Owner Blocks Allocated PerfMon counters tally how many of each of these structures SQL Server has allocated. You can get more detailed information about current system locks (e.g., the resource that's locked, the mode of the lock) by examining the master..syslockinfo system table or by executing the sp_lock system stored procedure.

By default, SQL Server dynamically manages the number of locks in the system to match the demands of the workload. SQL Server restricts locks to an upper limit of 60 percent of the buffer pool's virtual memory. If the amount of memory that locks occupy crosses this threshold, subsequent attempts to allocate a lock structure result in error 1204. In addition, you can use the advanced sp_configure option locks to set an explicit limit on the number of locks SQL Server will allocate.

Other Memory Uses

SQL Server also uses memory for a variety of other purposes such as caching connection information, optimizing queries, and processing utility commands such as bulk copy program (bcp) and backup/restore. You can find PerfMon counters for some of these uses in the SQL Server:Memory Manager object. The DBCC MEMORYSTATUS command provides an overall summary of SQL Server memory utilization, including the uses we've discussed. Although an explanation of this useful command is outside the scope of this article, you can read a detailed description of it in the Microsoft article "INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage."

We hope the information in this article helps you understand some of the important uses of memory in SQL Server and how to monitor them. Take some time and get familiar with the memory-utilization characteristics of your SQL Server. In the second part of this series, we'll look at how to use all this information to find and fix potential memory problems and boost SQL Server performance.

Comments

Plain text