Being day 24 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC SHRINKDATABASE. When I first heard about DBCC Shrink Database (many years ago), I immediately thought “what a great feature, run this every night when the system load is low, and the database will be smaller, perform better, and all around be in better shape”. …
Being day 22 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC OUTPUTBUFFER. I missed a few days on the DBCC Commands due to attending SQL Saturday in Redmond, and the release of my book on Amazon.com. I am now back on track to finish out the rest of the month with more DBCC commands. Description: …
Being day 17 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC INPUTBUFFER. Description: DBCC INPUTBUFFER allows access to the last query run for a specific session (SPID). DBCC INPUTBUFFER Syntax: Example: The following example we get a list of the sessions, then browse the queries that they have been running. Next if we use one …
Being day 16 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC INDEXDEFRAG. Description: DBCC INDEXDEFRAG was used for to defragment indexes on SQL Server 2000, the same thing can be done with Alter Index on newer versions of SQL Server.. DBCC INDEXDEFRAG Syntax: For more info on index defragmentation, check out the Database Health Reports …
Being day 15 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC HELP. Description: DBCC HELP is used get more information about a specific DBCC Command. DBCC HELP Syntax: Example: The following example calls DBCC Help to get help on the help command. You can use DBCC Help on any of the DBCC Commands here DBCC …
Being day 14 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC FREESYSTEMCACHE. Description: DBCC FREESYSTEMCACHE is used to free all memory associated with all of the SQL Server caches. DBCC FREESYSTEMCACHE Syntax: Example: The following example we first select form sys.dm_os_memory_clerks to find all of the available system caches that could be freed. In order …
Being day 13 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC FREESESSIONCACHE. Description: DBCC FreeSessionCache is only available on SQL Server 2005 and newer it removes all queries from the distributed query cache. This however does not impact anything in the standard procedure cache, to clear the standard procedure cache use DBCC FreeProcCache instead. The distributed …
Being day 12 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC FREEPROCCACHE. Description: DBCC FREEPROCCACHE is used for purge all of the parsed query plans out of memory. This is commonly used in development environments, but not as common in a production environment. Use in a development environment is common, for instance when you are …
Being day ten of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC DROPCLEANBUFFERS. Description: DBCC DropCleanBuffers is great to use in a development or test environment to flush all of the clean data pages out of memory to use for performance testing purposes. This can be combined with DBCC FreeProcCache to simulate the state of a freshly …
Being day nine of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC DBREINDEX. Microsoft recommends avoiding the use of DBCC DBREINDEX. DBCC DBReIndex has been deprecated meaning that Microsoft has announced that DBCC DBReIndex will be going away in future versions of SQL Server Over time as you insert, update and delete from tables various clustered and …