Problem solve Get help with specific problems with your technologies, process and projects.

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.

Serdar Yegulalp
Follow:

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

    • This was last published in April 2006

    Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

    Start the conversation

    Send me notifications when other members comment.

    Register

    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

    Please create a username to comment.

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close