A Small Collection of DBCC Commands

There are a number of pretty useful DBCC Commands that have nothing to do with checking the consistency of a database (and thus were probably not written by Paul Randal).  I have put together a few of them in the script below.  The first command is the only one that I consider to be somewhat “dangerous”, in that it will completely flush the contents of your “clean’ buffer cache, which will cause a lot of stress on your I/O subsystem as the buffer cache is refilled from disk. Depending on your workload, this might take a few minutes to happen, during which time you would see a performance impact.

-- A Small Collection of Useful DBCC Commands
-- Glenn Berry 
-- August 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry


-- Clears out contents of buffer cache
-- Use caution before doing this on a production system!
DBCC DROPCLEANBUFFERS;

-- Clears procedure cache on entire SQL instance
DBCC FREEPROCCACHE;

-- Remove the specific plan from the cache using the plan handle
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);

-- Clear ad-hoc SQL plans for entire SQL instance
DBCC FREESYSTEMCACHE('SQL Plans'); 

-- Clears TokenAndPermUserStore cache on entire SQL instance
DBCC FREESYSTEMCACHE ('TokenAndPermUserStore');

-- Releases all unused cache entries from all caches. ALL specifies all supported caches
-- Asynchronously frees currently used entries from their respective caches after they become unused
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;


-- Determine the id of the current database
-- and flush the procedure cache for only that database
DECLARE @intDBID AS INT = (SELECT DB_ID());
DBCC FLUSHPROCINDB (@intDBID);

-- Clear Wait Stats for entire instance
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- Get VLF count for transaction log for the current database,
-- number of rows equals VLF count. Lower is better!
DBCC LOGINFO;

-- Returns lots of useful information about memory usage
DBCC MEMORYSTATUS;

-- Find oldest open transaction
DBCC OPENTRAN;

-- Get input buffer for a SPID
DBCC INPUTBUFFER(21);

-- Check trace status for instance
DBCC TRACESTATUS(-1)

This entry was posted in SQL Server 2008 R2. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s