SQL Server – DBCC Commands
DBCC (Database consistency checker) are used to check the consistency of the databases. The DBCC commands are most useful for performance and troubleshooting exercises.
I have listed down and explained all the DBCC commands available in SQL Server 2005, with examples.
The DBCC Commands broadly falls into four categories:
Ø Maintenance
Ø Informational
Ø Validation
Ø Miscellaneous

v Maintenance Commands
Performs maintenance tasks on a database, index, or filegroup.
1. CLEANTABLE – Reclaims space from the dropped variable-length columns in tables or index views.
DBCC CLEANTABLE (‘AdventureWorks’,'Person.Contact’,0)
2. DBREINDEX – Builds one or more indexes for the table in the specified database. (Will be removed in the future version, use ALTER INDEX instead)
USE AdventureWorks
DBCC DBREINDEX (‘Person.Contact’,'PK_Contact_ContactID’,80)
3. DROPCLEANBUFFERS – Removes all clean buffers from buffer pool.
4. FREEPROCCACHE – Removes all elements from the procedure cache
5. INDEXDEFRAG – Defragments indexes of the specified table or view.
DBCC INDEXDEFRAG (‘AdventureWorks’, ‘Person.Address’, PK_Address_AddressID)
6. SHRINKDATABASE – Shrinks the size of the data and log files in the specified database
DBCC SHRINKDATABASE (‘AdventureWorks‘, 10)
7. SHRINKFILE – Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database.
USE AdventureWorks;
– Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1)
8. UPDATEUSAGE – Reports and corrects pages and row count inaccuracies in the catalog views.
DBCC UPDATEUSAGE (AdventureWorks) Informational Commands

v  Informational Commands
              Performs tasks that gather and display various types of information.
1. CONCURRENCYVIOLATION – is maintained for backward compatibility. It runs but returns no data.
2. INPUTBUFFER – Displays the lacst statement sent from a client to an instance of Microsoft SQL Server 2005.
3. OPENTRAN – Displays information about the oldest active transaction and the oldest distributed and non distributed replicated transactions, if any, within the specified database.
4. OUTPUTBUFFER – Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.
5. PROCCACHE – Displays information in a table format about the procedure cache.
6. SHOW_STATISTICS – Displays the current distribution statistics for the specified target on the specified table
USE AdventureWorks
DBCC SHOW_STATISTICS (Person.Address, AK_Address_rowguid)
7. SHOWCONTIG – Displays fragmentation information for the data and indexes of the specified table or view.
USE AdventureWorks
DBCC SHOWCONTIG (HumanResources.Employee);
8. SQLPERF – Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.
9. TRACESTATUS – Displays the status of trace flags.
10. USEROPTIONS – Returns the SET options active (set) for the current connection.
11 dbcc showfilestats – to get extents information (total & used extents)

v  Validation Commands
Performs validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.
1. CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.
DBCC CHECKALLOC (AdventureWorks)
2. CHECKCATALOG – Checks for catalog consistency within the specified database.
3. CHECKCONSTRAINTS – Checks the integrity of a specified constraint or all constraints on a specified table in the current database.
4. CHECKDB – Checks the logical and physical integrity of all the objects in the specified database.
DBCC CHECKDB (AdventureWorks)
5. CHECKFILEGROUP – Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.
USE AdventureWorks
6. CHECKIDENT – Checks the current identity value for the specified table and, if it is needed, changes the identity value.
USE AdventureWorks;
DBCC CHECKIDENT (HumanResources.Employee)
7. CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.
USE AdventureWorks;
DBCC CHECKTABLE (HumanResources.Employee)

v  Miscellaneous Commands
Performs miscellaneous tasks such as enabling trace flags or removing a DLL from memory.
1. dllname (FREE) – Unloads the specified extended stored procedure DLL from memory.
DBCC xp_sample (FREE)
2. TRACEOFF – Disables the specified trace flags.
3. HELP – Returns syntax information for the specified DBCC command.
– List all the DBCC commands
– Show the Syntax for a given DBCC commnad
DBCC HELP ('checkcatalog')
4. TRACEON – Enables the specified trace flags.

DBCC commands and their associated purpose.

DBCC CHECKDB      - This will check the allocation of all pages in the database as well as
check for any integrity issues.
DBCC results for 'msdb'.
Service Broker Msg 9675, State 1: Message Types analyzed: 16.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 7.
Service Broker Msg 9667, State 1: Services analyzed: 6.
Service Broker Msg 9668, State 1: Service Queues analyzed: 6.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'msdb'.

DBCC CHECKTABLE (table_name) - This will check the allocation of all pages for a specific table or index as well as check for any integrity issues.

DBCC results for 'vendor'.
There are 4 rows in 1 pages for object "vendor".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC DBREINDEX   - This command will re-index your table. If the index name is left out then
all indexes are rebuilt. If the fill factor is set to 0 then this will use the original fill-factor when the table was created.


DBCC PROCCACHE   - This command will show you information about the procedure cache and how much is being used.

num proc buffs |  num proc buffs used | num proc buffs active | proc cache size | proc cache used | proc cache active

-----------------          ----------------------------------------------------------------------- -------------------        ------------------

2604                                         13                            13                              230                                2                    2

DBCC MEMORYSTATUS - Displays the memory management info like VM Reserved,VM
Committed, AWE Allocated, Reserved Memory, Reserved Memory In Use. How the SQL Server buffer cache is divided up, includingbuffer activity.
DBCC SHOWCONTIG - This command gives you information TABLE level scan
performed. Pages Scanned, Extents Scanned, Extent Switches, Avg. Pages per Extent, Scan Density [Best Count:Actual Count], Extent Scan Fragmentation, Avg. Bytes Free per Page, Avg. Page Density (full), about how much space is used for a table and indexes. Information provided includes number of pages used as well as how fragmented the data is in the database.
DBCC SHOWCONTIG scanning 'spt_fallback_db' table...
Table: 'spt_fallback_db' (117575457); index ID: 0, database ID: 1
TABLE level scan performed.
- Pages Scanned................................: 0
- Extents Scanned..............................: 0
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 0.0
- Scan Density [Best Count:Actual Count].......: 100.00% [0:0]
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 0.0
- Avg. Page Density (full).....................: 0.00%
DBCC showcontig (table_id | table_name [, index_id | index_name]
DBCC SHOW_STATISTICS- This will show how statistics are laid out for an index. You
can see how distributed the data is and whether the index is really a good candidate or not.
DBCC show_statistics (‘table_name’, ‘target_name’)
DBCC SHRINKFILE  - This will allow you to shrink one of the database files. This is
equivalent to doing a database shrink, but you can specify what file and the size to shrink it to. Use the sp_helpdb command along with the database name to see the actual file names used.

DBCC shrinkdatabase ({dbid | ‘dbname’}, [freespace_percentage[, {NOTRUNCATE | TRUNCATEONLY}]])

DBCC SQLPERF       - This command will show you much of the transaction logs are being used.
                             DBCC sqlperf (LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [,CLEAR]} | {THREADS} | {LOGSPACE})
Database Name
Log Size (MB)
Log Space Used(%)

DBCC TRACEON      - This command will turn on a trace flag to capture events in the error log.
Trace Flag 1204 captures Deadlock information.
DBCC TRACEOFF     - This command turns off a trace flag.
dbcc help('?')To get all DBCC commands

SQL Server: DBCC CHECKDB Command with Complete Explanation

SQL Server is used widely for the management of database. It is possible that the data gets corrupt due to different reasons. Therefore, to deal with this issue SQL Server has a command, which act as a manual solution for corruption of the database. This command is referred as DBCC CHECKDB.

DBCC CHECKDB is a useful command often used to check the consistency error. This helps the users from high level of database corruption. The command proves to be useful when it comes to the information of physical as well as the logical integrity of the specified database objects.

DBCC CHECKDB command in SQL Server

database_name | database_id | 0
It is the ID or name of the database for which the checking is performed. In case, not specified or 0, then the current database is used. Name of the database must comply with the name of the rules for identifiers.

Indicates checks nonclustered indexes for user tables should not be performed. This reduces the overall time of execution. It does not have any effect on the system because integrity checks are performed on system table indexes.

This command tries for the repairing of all the errors that are reported. It is possible that you may loss some data, which is not repairable.
·         REPAIR_FAST
For backward compatibility maintains syntax. With this no recover actions are performed.
Repairs the data, which has no possibility of the loss of data. This includes quick repairs, like repairing absent rows in non-clustered indexes, and some time-consuming repairs, like rebuilding an index.

It is not possible to REPAIR_REBUILD, FILESTREAM data.

Note: Since DBCC CHECKDB with any of the Repair option is recoverable, therefore it is recommended to use CHECKDB

Shows all reported errors per object by default. If the option is omitted and specified, it has no effect. Object ID sort all error, except messages that are generated from tempdb database.

If in SQL Server 2008, compatibility level is 100 or above, you need to do logical consistency checks on XML indexes, spatial indexes, and indexed view, where present.

Restrains all informational messages.

TABLOCK causes DBCC CHECKDB to run in a fast mode under heavily loaded database. However, it decreases the concurrency available on the database during the running period of DBCC CHECKDB.

Provide the estimated space of tempdb space, which is required to run DBCC CHECKDB.

Provides the checking of the integrity of the physical structure of the page. It record headers and the allocation consistency of SQL Server database. This check provides a small check of the physical consistency of the database. However, it can also expose checksum failures, common hardware failure, and torn pages.

With the help of PHYSICAL_ONLY option, the time period can be shorten to run DBCC CHECKDB on large databases. However, it is recommended to perform full DBCC CHECKDB command time to time.

PHYSICAL_ONLY does not allows any repair option and always implies NO_INFOMSGS

Check the database for column values, which are out-of-range or invalid
Column-value integrity checks are enabled by default. It does not require the DATA_PURITY option. For those databases, which are upgraded to new versions of SQL Server, then by default, column-value checks are not enabled. Therefore to make them enable run DBCC CHECKDB WITH DATA_PURITY error free on the database. Once this is done, DBCC CHECKDB checks column-value integrity by default.

When, on a system, the instance of SQL Server runs, which has more than one CPU or microprocessor, MAXDOP detects the best degree of parallelism, which shows the the number of processors used or involved to run one statement, for each parallel plan execution.

It overrides the maximums degree of parallelism

Note: if MAXDOP is set to zero, then the server will choose the maximum degree of parallelism.


In the above section, we have discussed the importance of DBCC CHECKDB command in SQL Server. We have also, learned the circumstances where the need of this command arises in front of the users.

  1. DBCC activecursors [(spid)]
  2. DBCC addextendedproc (function_name, dll_name)
  3. DBCC addinstance (objectname, instancename)
  4. DBCC adduserobject (name)
  5. DBCC auditevent (eventclass, eventsubclass, success, loginname, rolename, dbusername, loginid)
  6. DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])
  7. DBCC balancefactor (variance_percent)
  8. DBCC bufcount [(number_of_buffers)]
  9. DBCC buffer ( {'dbname' | dbid} [, objid [, number [, printopt={0|1|2} ][, dirty | io | kept | rlock | ioerr | hashed ]]])
  10. DBCC bytes ( startaddress, length )
  11. DBCC cachestats
  12. DBCC callfulltext
  13. DBCC checkalloc [('database_name'[, NOINDEX | REPAIR])] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]
  14. DBCC checkcatalog [('database_name')] [WITH NO_INFOMSGS]
  15. DBCC checkconstraints [( 'tab_name' | tab_id | 'constraint_name' | constraint_id )] [WITH ALL_CONSTRAINTS | ALL_ERRORMSGS]
  17. DBCC checkdbts (dbid, newTimestamp)]
  18. DBCC checkfilegroup [( [ {'filegroup_name' | filegroup_id} ] [, NOINDEX] )] [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]
  19. DBCC checkident ('table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ] )
  20. DBCC checkprimaryfile ( {'FileName'} [, opt={0|1|2|3} ])
  21. DBCC checktable ('table_name'[, {NOINDEX | index_id | REPAIR}]) [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]
  22. DBCC cleantable ('database_name'|database_id, 'table_name'|table_id, [batch_size])
  23. DBCC cacheprofile [( {actionid} [, bucketid])
  24. DBCC clearspacecaches ('database_name'|database_id, 'table_name'|table_id, 'index_name'|index_id)
  25. DBCC collectstats (on | off)
  26. DBCC concurrencyviolation (reset | display | startlog | stoplog)
  27. DBCC config
  28. DBCC cursorstats ([spid [,'clear']])
  29. DBCC dbinfo [('dbname')]
  30. DBCC dbrecover (dbname [, IgnoreErrors])
  31. DBCC dbreindex ('table_name' [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]
  32. DBCC dbreindexall (db_name/db_id, type_bitmap)
  33. DBCC dbrepair ('dbname', DROPDB [, NOINIT])
  34. DBCC dbtable [({'dbname' | dbid})]
  35. DBCC debugbreak
  36. DBCC deleteinstance (objectname, instancename)
  37. DBCC des [( {'dbname' | dbid} [, {'objname' | objid} ])]
  38. DBCC detachdb [( 'dbname' )]
  39. DBCC dropcleanbuffers
  40. DBCC dropextendedproc (function_name)
  41. DBCC dropuserobject ('object_name')
  42. DBCC dumptrigger ({'BREAK', {0 | 1}} | 'DISPLAY' | {'SET', exception_number} | {'CLEAR', exception_number})
  43. DBCC errorlog
  44. DBCC extentinfo [({'database_name'| dbid | 0} [, {'table_name' | table_id} [, {'index_name' | index_id | -1}]])]
  45. DBCC fileheader [( {'dbname' | dbid} [, fileid])
  46. DBCC fixallocation [({'ADD' | 'REMOVE'}, {'PAGE' | 'SINGLEPAGE' | 'EXTENT' | 'MIXEDEXTENT'}, filenum, pagenum [, objectid, indid])
  47. DBCC flush ('data' | 'log', dbid)
  48. DBCC flushprocindb (database)
  49. DBCC free dll_name (FREE)
  50. DBCC freeproccache
  51. DBCC freeze_io (db)
  52. DBCC getvalue (name)
  53. DBCC help ('DBCC_command' | '?')
  54. DBCC icecapquery ('dbname', stored_proc_name [, #_times_to_icecap (-1 infinite, 0 turns off)]) Use 'DBCC icecapquery (printlist)' to see list of SP's to profile. Use 'DBCC icecapquery (icecapall)' to profile all SP's.
  55. DBCC incrementinstance (objectname, countername, instancename, value)
  56. DBCC ind ( { 'dbname' | dbid }, { 'objname' | objid }, { indid | 0 | -1 | -2 } )
  57. DBCC indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid | indname})
  58. DBCC inputbuffer (spid)
  59. DBCC invalidate_textptr (textptr)
  60. DBCC invalidate_textptr_objid (objid)
  61. DBCC iotrace ( { 'dbname' | dbid | 0 | -1 }, { fileid | 0 }, bufsize, [ { numIOs | -1 } [, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )
  62. DBCC latch ( address [, 'owners'] [, 'stackdumps'])
  63. DBCC lock ([{'DUMPTABLE' | 'DUMPSTATS' | 'RESETSTATS' | 'HASH'}]|[{'STALLREPORTTHESHOLD', stallthreshold}])
  64. DBCC lockobjectschema ('object_name')
  65. DBCC log ([dbid[,{0|1|2|3|4}[,['lsn','[0x]x:y:z']|['numrecs',num]|['xdesid','x:y']|['extent','x:y']|['pageid','x:y']|['objid',{x,'y'}]|['logrecs',{'lop'|op}...]|['output',x,['filename','x']]...]]])
  66. DBCC loginfo [({'database_name' | dbid})]
  67. DBCC matview ({'PERSIST' | 'ENDPERSIST' | 'FREE' | 'USE' | 'ENDUSE'})
  68. DBCC memobjlist [(memory object)]
  69. DBCC memorymap
  70. DBCC memorystatus
  71. DBCC memospy
  72. DBCC memusage ([IDS | NAMES], [Number of rows to output])
  73. DBCC monitorevents ('sink' [, 'filter-expression'])
  74. DBCC newalloc - please use checkalloc instead
  75. DBCC no_textptr (table_id , max_inline)
  76. DBCC opentran [({'dbname'| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]
  77. DBCC outputbuffer (spid)
  78. DBCC page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ][, cache={0|1} ])
  79. DBCC perflog
  80. DBCC perfmon
  81. DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}, targetfile, targetpg, order={1|0})
  82. DBCC pintable (database_id, table_id)
  83. DBCC procbuf [({'dbname' | dbid}[, {'objname' | objid}[, nbufs[, printopt = { 0 | 1 } ]]] )]
  84. DBCC proccache
  85. DBCC prtipage (dbid, objid, indexid [, [{{level, 0} | {filenum, pagenum}}] [,printopt]])
  86. DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]
  87. DBCC readpage ({ dbid, 'dbname' }, fileid, pageid, formatstr [, printopt = { 0 | 1} ])
  88. DBCC rebuild_log (dbname [, filename])
  89. DBCC renamecolumn (object_name, old_name, new_name)
  90. DBCC resource
  91. DBCC row_lock (dbid, tableid, set) - Not Needed
  92. DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)
  93. DBCC ruleon ( rulenum | rulestring } [, { rulenum | rulestring } ]+)
  94. DBCC setcpuweight (weight)
  95. DBCC setinstance (objectname, countername, instancename, value)
  96. DBCC setioweight (weight)
  97. DBCC show_statistics ('table_name', 'target_name')
  98. DBCC showcontig (table_id | table_name [, index_id | index_name] [WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]])
  99. DBCC showdbaffinity
  100. DBCC showfilestats [(file_num)]
  101. DBCC showoffrules
  102. DBCC showonrules
  103. DBCC showtableaffinity (table)
  104. DBCC showtext ('dbname', {textpointer | {fileid, pageid, slotid [,option]}})
  105. DBCC showweights
  106. DBCC shrinkdatabase ({dbid | 'dbname'}, [freespace_percentage [, {NOTRUNCATE | TRUNCATEONLY}]])
  107. DBCC shrinkdb is no longer supported. Please use shrinkdatabase instead
  108. DBCC shrinkfile ({fileid | 'filename'}, [compress_size [, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])
  109. DBCC sqlmgrstats
  111. DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, 'THREADID'}] )]
  112. DBCC tab ( dbid, objid )
  113. DBCC tape_control {'query' | 'release'}[,('\\.\tape')]
  114. DBCC tec [( uid[, spid[, ecid]] )]
  115. DBCC textall [({'database_name'|database_id}[, 'FULL' | FAST] )]
  116. DBCC textalloc ({'table_name'|table_id}[, 'FULL' | FAST])
  117. DBCC thaw_io (db)
  118. DBCC traceoff [( tracenum [, tracenum ... ] )]
  119. DBCC traceon [( tracenum [, tracenum ... ] )]
  120. DBCC tracestatus (trace# [, ...trace#])
  121. DBCC unpintable (dbid, table_id)
  122. DBCC updateusage ({'database_name'| 0} [, 'table_name' [, index_id]]) [WITH [NO_INFOMSGS] [,] COUNT_ROWS]
  123. DBCC upgradedb (db)
  124. DBCC usagegovernor (command, value)
  125. DBCC useplan [(number_of_plan)]
  126. DBCC useroptions
  127. DBCC wakeup (spid)
  128. DBCC writepage ({ dbid, 'dbname' }, fileid, pageid, offset, length, data)

Below are the detailed description of some DBCC commands and their usage:-

DBCC CACHESTATS displays information about the objects currently in the buffer cache, such as hit rates, compiled objects and plans, etc.
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 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.

DBCC DROPCLEANBUFFERS: Use this command to remove all the test 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.

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 server on a weekly basis. Also, you can accomplish the same thing using this stored procedure: sp_cycle_errorlog.

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.
DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'database_name')

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 other 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 reduce 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.
DBCC INDEXDEFRAG (Database_Name, Table_Name, Index_Name)

DBCC FREEPROCCACHE: Used to clear out the stored procedure cache for all SQL Server databases. You may want to use this command before testing to ensure that previous stored procedure plans won't negatively affect testing results.

DBCC MEMORYSTATUS: Lists a breakdown of how the SQL Server buffer cache is divided up, including buffer activity. Undocumented command, and one that may be dropped in future versions of SQL Server.

DBCC OPENTRAN: An open transaction can leave locks open, preventing others from accessing the data they need in a database. This command is used to identify the oldest open transaction in a specific database.
DBCC OPENTRAN('database_name')

DBCC PAGE: Use this command to look at contents of a data page stored in SQL Server.
DBCC PAGE ({dbid|dbname}, pagenum [, print option] [,cache] [,logical])
Dbid or dbname: Enter either the dbid or the name of the database in question.
Pagenum: Enter the page number of the SQL Server page that is to be examined.
Print option: (Optional) Print option can be either 0, 1, or 2. 0 - (Default) This option causes DBCC PAGE to print out only the page header information. 1 - This option causes DBCC PAGE to print out the page header information, each row of information from the page, and the page's offset table. Each of the rows printed out will be separated from each other. 2 - This option is the same as option 1, except prints the page rows as a single block of information rather than separating the individual rows. The offset and header will also be displayed.
Cache: (Optional) This parameter allows either a 1 or a 0 to be entered. 0 - This option causes DBCC PAGE to retrieve the page number from disk rather than checking to see if it is in cache. 1 - (Default) This option takes the page from cache if it is in cache rather than getting it from disk only.
Logical: (Optional) This parameter is for use if the page number that is to be retrieved is a virtual page rather then a logical page. It can be either 0 or 1. 0 - If the page is to be a virtual page number. 1 - (Default) If the page is the logical page number.

DBCC PINTABLE & DBCC UNPINTABLE: By default, SQL Server automatically brings into its data cache the pages it needs to work with. These data pages will stay in the data cache until there is no room for them, and assuming they are not needed, these pages will be flushed out of the data cache onto disk. At some point in the future when SQL Server needs these data pages again, it will have to go to disk in order to read them again into the data cache for use. If SQL Server somehow had the ability to keep the data pages in the data cache all the time, then SQL Server's performance would be increased because I/O could be significantly reduced on the server.
The process of "pinning a table" is a way to tell SQL Server that we don't want it to flush out data pages for specific named tables once they are read in in the first place. This in effect keeps these database pages in the data cache all the time, which eliminates the process of SQL Server from having to read the data pages, flush them out, and reread them again when the time arrives. As you can imagine, this can reduce I/O for these pinned tables, boosting SQL Server's performance.
To pin a table, the command DBCC PINTABLE is used. For example, the script below can be run to pin a table in SQL Server:
DECLARE @db_id int, @tbl_id int
USE Northwind
SET @db_id = DB_ID('Northwind')
SET @tbl_id = OBJECT_ID('Northwind..categories')
DBCC PINTABLE (@db_id, @tbl_id)

While you can use the DBCC PINTABLE directly, without the rest of the above script, you will find the script handy because the DBCC PINTABLE's parameters refer to the database and table ID that you want to pin, not by their database and table name. This script makes it a little easier to pin a table. You must run this command for every table you want to pin.
Once a table is pinned in the data cache, this does not mean that the entire table is automatically loaded into the data cache. It only means that as data pages from that table are needed by SQL Server, they are loaded into the data cache, and then stay there, not ever being flushed out to disk until you give the command to unpin the table using the DBCC UNPINTABLE. It is possible that part of a table, and not all of it, will be all that is pinned.
When you are done with a table and you no longer want it pinned, you will want to unpin your table. To do so, run this example code:
DECLARE @db_id int, @tbl_id int
USE Northwind
SET @db_id = DB_ID('Northwind')
SET @tbl_id = OBJECT_ID('Northwind..categories')
DBCC UNPINTABLE (@db_id, @tbl_id)

DBCC PROCCACHE: Displays information about how the stored procedure cache is being used.
DBCC REINDEX: Periodically (weekly or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server's performance.
If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.
Database reorganizations can be done scheduling SQLMAINT.EXE to run using the SQL Server Agent, or if by running your own custom script via the SQL Server Agent (see below).
Unfortunately, the DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database, it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease.
DBCC DBREINDEX('table_name', fillfactor)
--Script to automatically reindex all tables in a database
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
PRINT "Reindexing " + @TableName
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
CLOSE TableCursor

The script will automatically reindex every index in every table of any database you select, and provide a fillfactor of 90%. You can substitute any number you want for the 90 in the above script.
When DBCC DBREINDEX is used to rebuild indexes, keep in mind that as the indexes on a table are being rebuilt, that the table becomes unavailable for use by your users. For example, when a non-clustered index is rebuilt, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it. When a clustered index is rebuilt, an exclusive table lock is put on the table, preventing any table access by your users. Because of this, you should only run this command when users don't need access to the tables being reorganized. [7.0,
DBCC SHOWCONTIG: Used to show how fragmented data and indexes are in a specified table. If data pages storing data or index information becomes fragmented, it takes more disk I/O to find and move the data to the SQL Server cache buffer, hurting performance. This command tells you how fragmented these data pages are. If you find that fragmentation is a problem, you can reindex the tables to eliminate the fragmentation. Note: this fragmentation is fragmentation of data pages within the SQL Server MDB file, not of the physical file itself.
Since this command requires you to know the ID of both the table and index being analyzed, you may want to run the following script so you don't have to manually look up the table name ID number and the index ID number.
--Script to identify table fragmentation
--Declare variables
@ID int,
@IndexID int,
@IndexName varchar(128)
--Set the table and index to be examined
SELECT @IndexName = 'index_name' --enter name of index
SET @ID = OBJECT_ID('table_name') --enter name of table
--Get the Index Values
FROM sysindexes
WHERE id = @ID AND name = @IndexName
--Display the fragmentation

While the DBCC SHOWCONTIG command provides several measurements, the key one is Scan Density. This figure should be as close to 100% as possible. If the scan density is less than 75%, then you may want to reindex the tables in your database.
DBCC SHOW_STATISTICS: Used to find out the selectivity of an index. Generally speaking, the higher the selectivity of an index, the greater the likelihood it will be used by the query optimizer. You have to specify both the table name and the index name you want to find the statistics on.
DBCC SHOW_STATISTICS (table_name, index_name)

DBCC SQLMGRSTATS: Used to produce three different values that can sometimes be useful when you want to find out how well caching is being performed on ad-hoc and prepared Transact-SQL statements.
Sample Results:
Item Status
------------------------- -----------
Memory Used (8k Pages) 5446
Number CSql Objects 29098
Number False Hits 425490
Here's what the above means:
· Memory Used (8k Pages): If the amount of memory pages is very large, this may be an indication that some user connection is preparing many Transact-SQL statements, but it not un-preparing them.
· Number CSql Objects: Measures the total number of cached Transact-SQL statements.
· Number False Hits: Sometimes, false hits occur when SQL Server goes to match pre-existing cached Transact-SQL statements. Ideally, this figure should be as low as possible.
DBCC SQLPERF(): This command includes both documented and undocumented options. Let's take a look at all of them and see what they do.
This option (documented) returns data about the transaction log for all of the databases on the SQL Server, including Database Name, Log Size (MB), Log Space Used (%), and Status.
This option (undocumented) returns data about SQL Server thread management.
This option (undocumented) returns data about wait types for SQL Server resources.
This option (undocumented) returns data about outstanding SQL Server reads and writes.
This option (undocumented) returns data about SQL Server read-ahead activity.
This option (undocumented) returns data about I/O, CPU, and memory usage per SQL Server thread.

DBCC SQLPERF (UMSSTATS): When you run this command, you get output like this. (Note, this example was run on a 4 CPU server. There is 1 Scheduler ID per available CPU.)
Statistic Value
-------------------------------- ------------------------
Scheduler ID 0.0
num users 18.0
num runnable 0.0
num workers 13.0
idle workers 11.0
work queued 0.0
cntxt switches 2.2994396E+7
cntxt switches(idle) 1.7793976E+7
Scheduler ID 1.0
num users 15.0
num runnable 0.0
num workers 13.0
idle workers 10.0
work queued 0.0
cntxt switches 2.4836728E+7
cntxt switches(idle) 1.6275707E+7
Scheduler ID 2.0
num users 17.0
num runnable 0.0
num workers 12.0

Scheduler Switches 0.0
Total Work 3.1632352E+7
Below is an explanation of some of the key statistics above:
· num users: This is the number of SQL Server threads currently in the scheduler.
· num runnable: This is the number of actual SQL Server threads that are runnable.
· num workers: This is the actual number of worker there are to process threads. This is the size of the thread pool.
· idle workers: The number of workers that are currently idle.
· cntxt switches: The number of context switches between runnable threads.
· cntxt switches (idle): The number of context switches to the idle thread.

DBCC TRACEON & DBCC TRACEOFF: Used to turn on and off trace flags. Trace flags are often used to turn on and off specific server behavior or server characteristics temporarily. In rare occasions, they can be useful to troubleshooting SQL Server performance problems.
To use the DBCC TRACEON command to turn on a specified trace flag, use this syntax:
DBCC TRACEON (trace# [,...n])
To use the DBCC TRACEON command to turn off a specified trace flag, use this syntax:
DBCC TRACEOFF (trace# [,...n])
You can also use the DBCC TRACESTATUS command to find out which trace flags are currently turned on in your server using this syntax:
DBCC TRACESTATUS (trace# [,...n])

DBCC UPDATEUSAGE: The official use for this command is to report and correct inaccuracies in the sysindexes table, which may result in incorrect space usage reports. Apparently, it can also fix the problem of unreclaimed data pages in SQL Server. You may want to consider running this command periodically to clean up potential problems. This command can take some time to run, and you want to run it during off times because it will negatively affect SQL Server's performance when running. When you run this command, you must specify the name of the database that you want affected.
DBCC UPDATEUSAGE ('databasename')
The bottom line is that database and log files should be monitored and managed with care. You can allocate additional space to the database using the ALTER DATABASE command. Both data and log files can be shrunk using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands. The latter allows you to shrink individual files, whereas the former shrinks all database files (if you have more than one) in parallel. Unless I have a small database to manage I prefer using DBCC SHRINKFILE since it allows more control by specifying which file to shrink. The syntax of DBCC SHRINKFILE is as follows:

DBCC SHRINKFILE ({fileid | 'filename'}, [compress_size [, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])
As shown above, you may provide a file identifier (found in sysfiles system table) or file name you wish to shrink. In addition you can specify the compress size and one of the shrinking options. If you omit the compress_size parameter, DBCC SHRINKFILE will attempt to reduce the file to its minimum size. The NOTRUNCATE option moves data pages within the file but does not release free space to the operating system. TRUNCATEONLY does the exact opposite -- it releases free space at the end of the file, but does not re-shuffle the data pages. EMPTYFILE option is for special occasions when you wish to move all data to a different file and drop the data file. Feel free to get additional information on DBCC SHRINKFILE from SQL Server online documentation.

Please be forewarned that shrinking a large data file (one that is several gigabytes or larger) can generate much system overhead and might take a dozens of minutes to complete.
Managing log files is somewhat different from management of data files. The difference is due to the architecture of the transaction log. Regardless of how many log files you add to your database, it is still considered to be a single physical log; therefore you cannot backup individual transaction log files. On the logical level, the transaction log is made up of multiple Virtual Log Files (VLF). Transaction logs may contain transactions that have been committed as well as those that have not. Committed transactions are saved to the disk each time the CHECKPOINT command is executed.

Whether you can shrink the transaction log depends on whether the last virtual log file contains active transactions. You can examine the output of an undocumented DBCC command DBCC LOGINFO, to see if VLF's contain active transactions. If you pass no parameters to DBCC LOGINFO, it will execute in the context of the current database. Alternatively you can pass the database name OR database identifier as a parameter. For example:

Sample results:

FileID FileSize StartOffset FSeqNo Status Parity CreateLSN
------ ------- ----------- ------ ------ ------ ---------
2 253952 8192 909780 0 64 0
2 253952 262144 909779 0 64 0
2 253952 516096 909778 0 128 0
2 278528 770048 909777 0 128 0
2 262144 1048576 0 0 0 909788000000397000000
2 262144 1310720 0 0 0 909788000000457000000
2 262144 1572864 0 0 0 909789000000019000000
2 262144 1835008 0 0 0 909790000000017000000
2 262144 2097152 0 0 0 909791000000039000000
2 262144 2359296 0 0 0 909792000000035000000
This sample shows that the transaction log consists of two files: file identifiers found in the sysfiles system table for log files are 2 and 7. The transaction log contains 15 virtual log files (total number of rows in the output) of which 5 contain active transactions -- this is true for VLF's that have 2 in the status column of the output. Note also that virtual log files containing the active transactions are at the bottom of the output. Until the status of these VLF's changes to 0 you cannot shrink the transaction log. The status will change when transactions are saved to the disk.

Once the status of a virtual log file changes to 0 you can run DBCC SHRINKFILE against each physical log file (in this case against files 2 and 7) to reduce their size. For example:


Dbid fileid currentsize minimumsize usedpages estimatedpages
---- ------ ----------- ----------- --------- --------------
8 2 128 128 128 128
This output lets us know that the file size has been reduced to its minimum.
--Returns the SET options active (set) for the current connection.

--Reports and corrects inaccuracies in the sysindexes table, which may result in incorrect space usage reports by the sp_spaceused system stored procedure.

--Checks the consistency of disk space allocation structures for a specified database.

--DBCC CHECKCATALOG checks that every data type in syscolumns has a matching entry in systypes and that every table and view in sysobjects has at least one column in syscolumns.


--Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.


--Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.

--DBCC PINTABLE is best used to keep small, frequently referenced tables in memory.
DECLARE @db_id int, @tbl_id int
USE pubs
SET @db_id = DB_ID('pubs')
SET @tbl_id = OBJECT_ID('pubs..authors')
DBCC PINTABLE (@db_id, @tbl_id)

--Displays the last statement sent from a client to Microsoft® SQL Server™.

--Displays information in a table format about the procedure cache.

--Defragments clustered and secondary indexes of the specified table or view.DBCC INDEXDEFRAG is an online operation. It does not hold locks long term and thus will not block running queries or updates.the defragmentation is always fully logged, regardless of the database recovery model setting
DBCC INDEXDEFRAG (database_name, table_name, index_name) [ WITH NO_INFOMSGS ]

--DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table.
DBCC DBREINDEX ('database.owner.table_name', index_name, fillfactor ) [ WITH NO_INFOMSGS ]
DBCC DBREINDEX (authors, '', 70)

--Provides statistics about the use of transaction-log space in all databases.

--Displays the current distribution statistics for the specified target on the specified table.

--Shrinks the size of the data files in the specified database.

--Displays fragmentation information for the data and indexes of the specified table.
[ , { FAST | ALL_LEVELS } ] } ]

--Provided detailed reports on memory use.

-- Provided detailed information about various system performances

--Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the specified database

--Drops a damaged database.

--Checks the integrity of a specified constraint or all constraints on a specified table.

-- Command to peform database repair with three options
} ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]


Find all DMV's by running below script:
SELECT nametypetype_descFROM sys.system_objectsWHERE name LIKE 'dm_%'ORDER BY name

SQL Server DMV/DMFs that every DBA should know

SQL Server 2012 Dynamic Management Views and Functions

Execution Related
  • sys.dm_exec_connections - will return session_id, num_reads/num_writes,last_read/last_Write, local_net_address(IP),local_tcp_port.
  • sys.dm_exec_sessions - will return session_id, Login_name, login_time, last_request_start_time,database_id
  • sys.dm_exec_requests - will return session_id, start_time, status, command, database_id, percent_complete
  • sys.dm_exec_cached_plans
  • sys.dm_exec_query_plans
  • sys.dm_exec_sql_text
  • sys.dm_exec_query_stats
Index Related
  • sys.dm_db_index_physical_stats ---will return database_id, object_id, index_id,index_type_desc, avg_fragmentation_in_percent, fragment_count
  • sys.dm_db_index_usage_stats - --will return database_id, object_id, index_id,user_lookups, user_updates
SQL Server Operating System
  • sys.dm_os_performance_counters
  • sys.dm_os_schedulers
  • sys.dm_os_nodes
  • sys.dm_os_waiting_tasks
  • sys.dm_os_wait_stats
I/O Related
  • sys.dm_io_virtual_file_stats - return the statistics of the data/log file of DB.
List of Locks:
select * from sys.dm_tran_locks

Get a Listing of All Connections:
SELECT *  FROM sys.dm_exec_connections

Get a Listing of All Sessions
SELECT *  FROM sys.dm_exec_sessions

Get a listing of all user connections:
SELECT c.session_id
 , c.auth_scheme
 , c.node_affinity
 , s.login_name
 , db_name(s.database_id) AS database_name
 , CASE s.transaction_isolation_level
 WHEN 0 THEN 'Unspecified'
 WHEN 1 THEN 'Read Uncomitted'
 WHEN 2 THEN 'Read Committed'
 WHEN 3 THEN 'Repeatable'
 WHEN 4 THEN 'Serializable'
 WHEN 5 THEN 'Snapshot'
 END AS transaction_isolation_level
 , s.status
 , c.most_recent_sql_handle
 FROM sys.dm_exec_connections c
 INNER JOIN sys.dm_exec_sessions s
 ON c.session_id = s.session_id

Get a listing of all user connections with their request details:

SELECT c.session_id
 , c.auth_scheme
 , c.node_affinity
 , r.scheduler_id
 , s.login_name
 , db_name(s.database_id) AS database_name
 , CASE s.transaction_isolation_level
 WHEN 0 THEN 'Unspecified'
 WHEN 1 THEN 'Read Uncomitted'
 WHEN 2 THEN 'Read Committed'
 WHEN 3 THEN 'Repeatable'
 WHEN 4 THEN 'Serializable'
 WHEN 5 THEN 'Snapshot'
 END AS transaction_isolation_level
 , s.status AS SessionStatus
 , r.status AS RequestStatus
 , CASE WHEN r.sql_handle IS NULL THEN
 END AS sql_handle
 , r.cpu_time
 , r.reads
 , r.writes
 , r.logical_reads
 , r.total_elapsed_time
 FROM sys.dm_exec_connections c
 INNER JOIN sys.dm_exec_sessions s
 ON c.session_id = s.session_id
 LEFT JOIN sys.dm_exec_requests r
 ON c.session_id = r.session_id

No comments:

Post a Comment

Popular Posts