Useful SQL Server DBCC Commands

DBCC CACHESTATS displays information about the objects currently in the buffer cache, such as hit rates, compiled objects and plans, etc.

Example:

DBCC CACHESTATS

Sample Results (abbreviated):

Object Name       Hit Ratio
------------      -------------

Proc              0.86420054765378507
Prepared          0.99988494930394334
Adhoc             0.93237136647793051
ReplProc          0.0
Trigger           0.99843452831887947
Cursor            0.42319205924058612
Exec Cxt          0.65279111666076906
View              0.95740334726893905
Default           0.60895011346896522
UsrTab            0.94985969576133511
SysTab            0.0
Check             0.67021276595744683
Rule              0.0
Summary           0.80056155581812771

Here's what some of the key statistics from this command mean:

• Hit Ratio: Displays the percentage of time that this particular object was found in SQL Server's cache. The bigger this number, the better.
• Object Count: Displays the total number of objects of the specified type that are cached.
• Avg. Cost: A value used by SQL Server that measures how long it takes to compile a plan, along with the amount of memory needed by the plan. This value is used by SQL Server to determine if the plan should be cached or not.
• Avg. Pages: Measures the total number of 8K pages used, on average, for cached objects.
• LW Object Count, LW Avg Cost, WL Avg Stay, LW Ave Use: All these columns indicate how many of the specified objects have been removed from the cache by the Lazy Writer. The lower the figure, the better.

[7.0, 2000] Updated 9-1-2005

*****

DBCC DROPCLEANBUFFERS: Use this command to remove all the data from SQL Server's data cache (buffer) between performance tests to ensure fair testing. Keep in mind that this command only removes clean buffers, not dirty buffers. Because of this, before running the DBCC DROPCLEANBUFFERS command, you may first want to run the CHECKPOINT command first. Running CHECKPOINT will write all dirty buffers to disk. And then when you run DBCC DROPCLEANBUFFERS, you can be assured that all data buffers are cleaned out, not just the clean ones.

Example:

DBCC DROPCLEANBUFFERS

[7.0, 2000, 2005] Updated 9-1-2005

*****

DBCC ERRORLOG: If you rarely restart the mssqlserver service, you may find that your server log gets very large and takes a long time to load and view. You can truncate (essentially create a new log) the Current Server log by running DBCC ERRORLOG. You might want to consider scheduling a regular job that runs this command once a week to automatically truncate the server log. As a rule, I do this for all of my SQL Servers on a weekly basis. Also, you can accomplish the same thing using this stored procedure: sp_cycle_errorlog.

Example:

DBCC ERRORLOG

[7.0, 2000, 2005] Updated 9-1-2005

*****

DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a specific database on a SQL Server, not the entire SQL Server. The database ID number to be affected must be entered as part of the command.

You may want to use this command before testing to ensure that previous stored procedure plans won't negatively affect testing results.

Example:

DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'database_name')
DBCC FLUSHPROCINDB (@intDBID)

[7.0, 2000, 2005] Updated 9-1-2005

*****

DBCC INDEXDEFRAG: In SQL Server 2000, Microsoft introduced DBCC INDEXDEFRAG to help reduce logical disk fragmentation. When this command runs, it reduces fragmentation and does not lock tables, allowing users to access the table when the defragmentation process is running. Unfortunately, this command doesn't do a great job of logical defragmentation.

The only way to truly reduce logical fragmentation is to rebuild your table's indexes. While this will remove all fragmentation, unfortunately it will lock the table, preventing users from accessing it during this process. This means that you will need to find a time when this will not present a problem to your users.

Of course, if you are unable to find a time to reindex your indexes, then running DBCC INDEXDEFRAG is better than doing nothing.

Example:

DBCC INDEXDEFRAG (Database_Name, Table_Name, Index_Name)

[2000] Updated 9-1-2005