Among the undocumented SQL Server commands I've been covering in the past few weeks, there is one command that helps determine how well SQL Server is managing its memory usage: DBCC MEMUSAGE. Specifically, DBCC MEMUSAGE determines how effectively SQL Server caches data and which objects spend the most time in or out of the cache. Like most of the other DBCC commands, the data it returns needs analyzing to be useful.
Say you run this command:
DBCC TRACEON (3604)
DBCC MEMUSAGE
You will get a table with the following columns:
dbID: The ID # of the database for the object being cached
objectID: The specific ID # of the object being cached
indexID: If the data is part of an index, this is 0; otherwise, it's the index ID # that tells you where the data is from
buffers: The number of buffers used to hold the data (the results are sorted by this column in descending order)
dirty: Whether or not the data in question is "dirty" -- i.e., modified in memory but not yet flushed out to disk. This is only for the sake of information and doesn't really affect the quality of the data gathered.
Note that DBCC MEMUSAGE only captures the top 20 cached items at any given time, so it's not a complete record. But if sampled over time, it can give you a good idea of what objects are most consistently cached, which could be used as part of an internal process to report back on what's spending the most time in the cache or competing for cache space. If you wanted to export the results, you could create a table and use a periodic INSERT INTO <tablename > EXEC('DBCC MEMUSAGE') to populate the table. Once every minute or so over the course of a day should give you a very broad sampling of what's in the cache.
Be aware that as of SQL Server 7.0, Microsoft warns against using DBCC MEMUSAGE in a production environment. Instead, it recommends using the SQL Server Buffer Manager Object or other system statistics. However, most people who have used DBCC MEMUSAGE in a production environment have not reported any negative effects on SQL Server 2000 or better.
About the author: Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
More information from SearchSQLServer.com
Step-by-Step Guide: Hunt down SQL Server performance problems
FAQ: SQL Server performance boosters
Undocumented DBCC: Command to view a database transaction log
