Undocumented DBCC: Command to track memory usage
This DBCC gives you the top 20 cached items in SQL Server at any given time to help you analyze which objects are spending the most time in or out of the cache.
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:
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
