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)