IMPORTANT DBCC COMMANDS USED IN SQL SERVER REGULARLY FOR DBA OPERATIONS
| Command Name | Description |
|---|---|
| DBCC Cachestats | displays information about the objects currently in the buffer cache, such as hit rates, compiled objects and plans, etc. |
| DBCC DROPCLEANBUFFERS | Used to remove the clean buffers from the buffer cache, but it will not clear the dirty pages that are occupied in the memory |
| DBCC Errorlog | Used to recycle the errorlog file. Sp_cycle_errorlog also serves the same work |
| DBCC FlushProcINDB (@DBID) | Used to clear out the stored procedure cache for a specific database on a SQL Server, not the entire SQL Server |
| DBCC PROCCACHE | Displays information about how the stored procedure cache is being used |
| DBCC FREEPROCCACHE | Used to clear out the stored procedure cache for all SQL Server databases |
| DBCC MEMORYSTATUS | Complete information about the SQL Server Memory Cache |
| DBCC OPENTRAN | Shows the open transaction information |
| DBCC PINTABLE (@db_id,@tbl_id) | table associated with this database will be PINNED in the physical RAM to increase the performance by decreasing the disk I/O. This should be done by thorough command on the memory concept |
| DBCC UNPINTABLE (@db_id,@tbl_id) | table associated with this database will be UNPINNED in the physical RAM to give room to other resources for using the RAM. This should be done by thorough command on the memory concept |
| DBCC SHOWCONTIG (Table_id, IndexID) | to find the fragmentation level of the table |
| DBCC SHOW_STATISTICS (table_name,index_name) | selectivity of the index used. |
| DBCC SQLPERF(Logspace) | Gives the LOG FILE information likes usage, %occupied |
| DBCC SQLPERF(WAITSTATS) | WAITSTATS information will be provided |
| DBCC SQLPERF(IOSTATS) | Provides read write outstanding information |
| DBCC SQLPERF(RASTATS) | Provides read ahead outstanding information |
| DBCC SQLPERF(THREADS) | Provides threads information, mem usage and CPU usage |
| DBCC UPDATEUSAGE('database_name') | Updates the space usage information of the objects after the any heavy operations like reorg/rebuild index has happned |
Hope the above information helps
Thanks
