Last month in "Memory Myths" (InstantDoc ID 37890), 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, by entering InstantDoc ID 37908 at http://www.sqlmag.com. 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.
Prev. page
[1]
2
next page 
