Managing Memory

Last month in "Memory Myths," I talked about some common misconceptions regarding SQL Server memory management. Unless you've done a lot of performance tuning on your system, much of the discussion might have seemed abstract to you. Let's make the ideas a little more concrete by examining some tools you can use to practice memory management. One is a system-monitoring tool, and the other is a workload-simulation tool.

The best tool to use for observing SQL Server memory management behavior is System Monitor (known as Performance Monitor on Windows NT 4.0). You can access this tool from the Start menu by choosing Administrative Tools, Performance. You could choose to monitor hundreds of possible counters, of which dozens deal specifically with memory use, both for SQL Server and for the OS. Unfortunately, in this article I can't tell you everything about using the options in System Monitor. The best resource I've found for learning about System Monitor is the Windows 2000 Resource Kit. For information about SQL Server—specific monitoring, you can read the book Microsoft SQL Server 2000 Performance Tuning Technical Reference, by Edward Whalen et al (Microsoft Press, 2001).

To make it easier for you to start using System Monitor, I created a file that opens System Monitor with several useful counters already defined. You can download this file, MonitorMemory.msc from the "Download the Code" link at the top of the page. If you use this file to open System Monitor, you'll have five counters defined:

• Total Server Memory (KB). This value, from the SQL Server: Memory Manager object, shows how much memory SQL Server is using. The primary use of SQL Server's memory is for the buffer pool, but some memory is used for other purposes, such as storing query plans for reuse and keeping track of user process information.
• Target Server Memory (KB), from the SQL Server: Memory Manager object. This value shows how much memory SQL Server attempts to acquire. If you haven't configured a max server memory value for SQL Server, the target amount of memory will be about 5MB less than the total available system memory. You'll be able to see how Target Server Memory affects Total Server Memory in the tests I describe later.
• Working Set, from the Process object, for the sqlserver.exe process instance. The working set is the subset of a process's virtual pages that are resident in physical memory. This value shows how much physical memory SQL Server is using; it encompasses all of SQL Server's memory needs, including the buffer pool and other memory objects as well as the SQL Server executable. This is the value you see when you look at the Processes tab of the Task Manager tool in the Mem Usage column for the sqlservr.exe process.
• Working Set, from the Process object, for the isqlw.exe process instance. This value shows how much memory Query Analyzer is using. Notice how much memory is consumed when you run a query that returns a large result set. If you're not already doing so, after seeing Query Analyzer's memory consumption, you might decide to run your client tools on a different machine from the one you're running SQL Server on.
• Available KBytes, from the Memory object. This value represents how much memory is free and available to the OS. As SQL Server's memory needs grow and its target memory size increases, it attempts to ensure that approximately 5MB of memory is free at all times.

As I mentioned, there are dozens of SQL Server memory counters, but these will give you a good start at observing SQL Server's memory management behavior.

I've provided a small utility from the SQL Server Resource Kit called LeakyApp.exe, which you can download from http://www.sqlmag.com. This utility is a compiled program that uses the system's free memory. It lets you see what happens to SQL Server's memory usage when it's under pressure from other memory-consuming applications.

Here are a few tests you can run to observe how SQL Server uses memory resources. If your machine has more than 128MB of physical memory, you can speed up the tests if you reduce the amount of memory that Windows can use by adding /MAXMEM=128 to the C:\boot.ini file and rebooting. But attempt this decrease only if the computer is a dedicated development machine and you have experience modifying boot.ini. This change makes Windows think that your machine has only 128MB of RAM and ignore all additional physical memory.

For the first test, make sure that your SQL Server is configured to be completely dynamic in its memory usage by not setting an upper or lower limit for memory. You can verify this setting by making the configuration changes from Listing 1. After you run Listing 1's script, follow these steps:

1. Open the following programs: Monitor Memory.msc, Query Analyzer (and log in), and LeakyApp.exe.
2. Into Query Analyzer, load Listing 2, which makes a much larger copy of the Order Details table in the Northwind database. Start executing this script.
3. Click the "Start Leaking" button on LeakyApp.exe.
4. Observe the memory counters in System Monitor.

You can notice several things while watching your System Monitor. The Total Server Memory value for SQL Server and the Working Set value should start slowly increasing as the script in Query Analyzer requires more memory. Then, as soon as LeakyApp starts, the Available KBytes counter should start dropping. If you have no more than 128MB of usable memory, you should see the graph for Available KBytes dropping quickly. The value for Target Server Memory will slowly drop until it reaches the value of Total Server Memory. Target and total memory will then stay at the same value and slowly drop in value together. When the Available KBytes value gets very low (on my system, it was about 10MB), Target Server Memory and Total Server Memory will make a big drop as SQL Server dynamically adjusts to external memory pressure. When Available KBytes reaches its minimum value of about 5MB and SQL Server has already given up buffer-pool memory, Windows will start reducing the Working Set of other applications. You can see a big drop in the Working Set for Query Analyzer (which should be finished executing the script by this time).

Next, select Stop Leaking, then select Reset. When the application stops leaking, the Available KBytes counter levels out. But after you click Reset, you'll notice big changes. At that point, the counters for Available KBytes and for Target Server Memory should increase quite a bit. Note that Total Server Memory doesn't immediately increase; it stays constant until you start some SQL Server activity that requires additional memory.

A slightly different test you can run is to limit SQL Server's memory use by configuring SQL Server's minimum and maximum memory values. First, run the script that Listing 3 shows to reconfigure SQL Server memory use. Next, rerun the script in Listing 2. At this point, SQL Server Working Set and Total Server Memory start increasing. Now, click the Start Leaking button on LeakyApp.exe and observe the memory counters in System Monitor. Notice that the value for SQL Server Target Memory is almost exactly what you configured SQL Server's minimum memory to be.

Next, run the script in Listing 4, which starts using lots of memory by joining the big table to itself multiple times. Note that the query returns only a count of the number of rows in the result, so you're not using a lot of memory for Query Analyzer's results.

As the query executes, you should see Target Server Memory increasing and Total Server Memory following along with it. Target Server Memory will reach approximately the maximum memory value you configured for SQL Server. The Total Server Memory value will stay slightly lower than the maximum because SQL Server needs to reserve some memory for other uses besides what the Target Server Memory value allows.

You can run other kinds of tests to observe more about SQL Server memory management behavior, even with this small set of basic counters. To see the effect of forcing the Working Set Manager to favor the SQL Server process for working set reduction, configure set working set size to 1. As I mentioned in "Memory Myths," this option limits the total amount of memory that other processes can commit and makes SQL Server one of the last processes to have its pages removed from physical memory. In effect, you're taking away physical memory from the rest of the system. After making this configuration change, you can try running the script from Listing 2 or Listing 4, with or without running LeakyApp.exe.

For another test, you can configure AWE enabled to 1. As I mentioned last month, with Address Windowing Extensions (AWE) enabled, SQL Server will by default allocate almost all memory during startup. This memory is locked and can't be paged out. After you've made this configuration change, you can vary this test by running Listing 2 or Listing 4, with or without running LeakyApp.exe.

As a final step after finishing any of these tests, you might want to run the code from Listing 1 again to reset SQL Server's memory back to being fully dynamic or change the values to whatever minimum and maximum you had previously set. You can expand your observations of SQL Server memory management by adding other counters and watching the interaction among them. The references I mentioned at the beginning of this article will give you more information about using System Monitor and the various counters available.

I hope you find that using System Monitor to see what's happening inside SQL Server as it uses memory will solidify your understanding of SQL Server memory management behavior. Maybe it will even inspire you to use this wonderful tool for monitoring other aspects of SQL Server's internal behavior. You'll feel yourself becoming more of an expert with every test you run.