Tip

Undocumented DBCC: Command to track memory usage

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

    • Requires Free Membership to View

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    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
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.