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:
- Open the following programs: Monitor Memory.msc, Query Analyzer (and log in), and LeakyApp.exe.
- 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.
- Click the "Start Leaking" button on LeakyApp.exe.
- 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.
End of Article
Prev. page
1
[2]
next page -->
