Home > SQL Server Tips > Database Administration > Undocumented DBCC: Command to track memory usage
SQL Server Tips:
EMAIL THIS
 TIPS & NEWSLETTERS TOPICS 

DATABASE ADMINISTRATION

Undocumented DBCC: Command to track memory usage


Serdar Yegulalp, Contributor
04.05.2006
Rating: -1.75- (out of 5)


Expert advice on database development
Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


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 sampl


    Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


    RELATED CONTENT
    Microsoft SQL Server Performance Monitoring and Tuning
    Performance implications of transaction log autogrowth in SQL Server
    The short course on how SQL Server really works
    Determining the source of full transaction logs in SQL Server
    Improving SQL Server full-text search performance
    New GROUP BY option provides better data control in SQL Server 2008
    Microsoft SQL Server 2008 Resource Governor primer
    Examining data files when SQL Server tempdb is full
    Testing transaction log autogrowth behavior in SQL Server
    Meeting business needs with SQL Server full-text search
    Using dynamic management views to improve SQL Server index effectiveness

    SQL/Transact SQL (T-SQL)
    Working with sparse columns in SQL Server 2008
    Determining the source of full transaction logs in SQL Server
    New GROUP BY option provides better data control in SQL Server 2008
    Using the OPENROWSET function in SQL Server
    Loading data files with SQL Server's BULK INSERT statement
    Importing and exporting bulk data with SQL Server's bcp utility
    Testing transaction log autogrowth behavior in SQL Server
    Securing SQL Server with access control, login monitoring and DDL triggers
    Top 10 SQL Server development tips of 2008
    The sqlcmd utility in SQL Server
    SQL/Transact SQL (T-SQL) Research

    Database Administration
    Top load balancing methods for SQL Server
    Performance implications of transaction log autogrowth in SQL Server
    The keys to database backup protection for SQL Server
    Understanding transparent data encryption in SQL Server 2008
    Working with sparse columns in SQL Server 2008
    Determining the source of full transaction logs in SQL Server
    Implementing SQL Server 2008 FILESTREAM functionality
    Improving SQL Server full-text search performance
    Using the OPENROWSET function in SQL Server
    New replication features in SQL Server 2008 and what they mean to you

    RELATED GLOSSARY TERMS
    Terms from Whatis.com − the technology online dictionary
    contiguity  (SearchSQLServer.com)
    contiguous  (SearchSQLServer.com)
    drilldown  (SearchSQLServer.com)
    hashing  (SearchSQLServer.com)
    hybrid online analytical processing  (SearchSQLServer.com)

    RELATED RESOURCES
    2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
    Search Bitpipe.com for the latest white papers and business webcasts
    Whatis.com, the online computer dictionary


    ed 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

    • Rate this Tip
    To rate tips, you must be a member of SearchSQLServer.com.
    Register now to start rating these tips. Log in if you are already a member.




    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.



    SQL Server Development - .NET, C#, T-SQL, Visual Basic
    HomeNewsTopicsITKnowledge ExchangeTipsAsk the ExpertsMultimediaWhite PapersIT Downloads
    About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
    SEARCH 
    TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of technology-specific websites, events and online magazines.

    TechTarget Corporate Web Site  |  Media Kits  |  Site Map




    All Rights Reserved, Copyright 2005 - 2009, TechTarget | Read our Privacy Policy     		  TechTarget - The IT Media ROI Experts