One of SQL Server 2000’s main selling points is its ease-of-use and simplicity. You can install it out of the box and use it pretty much without making many configuration changes. In terms of the product’s use of memory, there are some settings you can change, for example, how much memory to use, whether to dynamically allocate it, and so on. Otherwise, there isn’t a lot you can do in terms of memory tuning. But you may well find yourself in a situation where you want to know what tables and indexes SQL Server is caching, how much of them it is caching, and which database is consuming most of your server’s memory. This article will explain how to capture memory usage and analyze it to determine what data your server is caching. This article does not cover execution plan caching (procedure cache), just the data cache.
The Tool
The system for monitoring and analyzing memory usage described in this article relies on an undocumented and unsupported command:
DBCC MEMUSAGE
This command is actually a leftover from SQL Server 6.5 and although unsupported and undocumented, I have used it extensively and found it to be reliable and to have no discernible impact on the server. Be warned that the Microsoft knowledge base has a specific warning against using this command on SQL Server 7.0 servers, as detailed in this knowledge base article
http://support.microsoft.com/default.aspx?scid=kb;en-us;196629
This warns that running this command may cause the server to stop responding under heavy load. I have only tested it on SQL Server 2000 servers, without any problems. However, you should use it with care and understand that it is unsupported.
What Does DBCC MEMUSAGE Tell Us?
It tells us the top 20 objects in the data cache, how many pages are in cache for each one, how many pages of these are dirty*, and which database they belong to. It also tells us whether the pages are data or index, and if index pages, which index they are from. This is very useful information if you want to know how your memory is being allocated. There are limitations – as it only gives the top 20 objects, therefore it isn’t a comprehensive picture. But it can give you a very good feel for what’s going on in your SQL Server data cache, particularly how objects are moved into and aged out of cache as query load changes.
*A dirty page is one which has been modified in RAM but the changes have not yet been committed to disk in the database itself. SQL Server flushes these dirty pages out of disk in an asynchronous way. This process and the number of dirty pages are not important for the purposes of this article.
The Monitoring System
This system works by creating a table, MemusageRecord, which holds the output from DBCC MEMUSAGE over a period of time. A stored procedure is also created which runs DBCC MEMUSAGE, captures its output, and then appends the results into the MemusageRecord table. This stored procedure is run as a scheduled task in SQL Server agent. Once the data has been captured, it can be analyzed. I have included a simple query for this, but it is best to export this data to Excel, or a similar tool, where it can more easily be pivoted and graphs can be produced to summarise the results. To set up this system, first create the table MemusageRecord using the script provided. I suggest you put this in a dedicated admin type database. If you don’t have one of these, or don’t want to create one, you can put the table in the database of interest – it won’t grow too large provided you empty it out regularly.
CREATE TABLE [dbo].[MemusageRecord] (
[dbid] [int] NOT NULL ,
[objectid] [int] NOT NULL ,
[indexid] [int] NOT NULL ,
[Buffers] [int] NOT NULL ,
[Dirty] [int] NOT NULL ,
[InsertDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [mur001] ON [dbo].[memusagerecord]([InsertDate]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[memusagerecord] ADD
CONSTRAINT [DF_getdate_insertdate] DEFAULT (getdate()) FOR [InsertDate]
GO
Next, create the stored procedure prMemusageRecord. This procedure creates a temporary table, #memusagerecord, and inserts the output of DBCC MEMUSAGE into it. The reason for this is so that we can also capture the date and time of the data, which is done by having an InsertDate column in the main memusagerecord table which defaults to getdate(), effectively date stamping the memusage data as it is captured and inserted into the record table. Note that the user context in which this procedure is run must be a member of the sysadmin fixed server role – this is the only role which has sufficient permission to run DBCC MEMUSAGE – and permission on it cannot be transferred.