Understanding DBCC SHOWCONTIG and DBCC INDEXDEFRAG
In SQL Server, Indexing is not really part of the relational database architecture, yet it is the most important operational feature. Indexing was inherited from the pre-relational world: Indexed Sequential Access Method (ISAM) were the main data storage mechanism on IBM mainframes after the introduction of COBOL in the middle of 60-s. Currently DB2 relational databases are used on mainframes although ISAM never really went away on legacy systems.
Most operational problems on SQL Servers revolve around indexes: what are the right indexes, how do we maintain them in top operating shape, etc.
To check the fragmentation of indexes in the entire database, execute:
Use <DatabaseName>
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
The result will be presented in a tabular format.
For individual table fragmentation, execute:
DBCC SHOWCONTIG (TableName) with all_indexes
In SQL Server 2005, execute:
USE AdventureWorks
GO
DBCC SHOWCONTIG (‘HumanResources.Employee’)
GO
The results will be in text format with labels:
DBCC SHOWCONTIG scanning ‘TableName’ table…
Table: ‘TableName’ (206334645); index ID: 7, database ID: 14
LEAF level scan performed.
– Pages Scanned…………………………..: 62128
– Extents Scanned…………………………: 7808
– Extent Switches…………………………: 8098
– Avg. Pages per Extent……………………: 8.0
– Scan Density [Best Count:Actual Count]…….: 95.89% [7766:8099]
– Logical Scan Fragmentation ………………: 17.39%
– Extent Scan Fragmentation ……………….: 7.68%
– Avg. Bytes Free per Page…………………: 2412.0
– Avg. Page Density (full)…………………: 70.20%
Index ID 1 refers to the clustered index on the table if any. To find out the index name,
you have to look in the sysindexes table.
___________________________________________________________________________________________
Alternative to reindexing is indexdefrag. To defrag a specific index, execute:
DBCC INDEXDEFRAG (DatabaseName, TableName, IndexName)
- Defragging is on online operation.
It will not lock up the table.
On the other hand it will slow down the operation.
It is a logged operation.
So it competes with regular OLTP transactions for writing
Assume it takes 1 hour to defrag an index in the middle of the day.
Should you do it? Probably not. If it takes only 5 minutes, you should.
- Your only protection against quickly deteriorating indexes is the FILL FACTOR.
During the night you should reindex with a fill factor of 70 in general.
If performance issue persists, you can gradually go down to 40.
Fill factor is limited help if the inserted data does not have good distribution.
On the other hand, it works really great if the inserted data has a good distribution.
In SQL Server 2005:
select * from sys.indexes
- The results above show moderate fragmentation.
The page density is the result of indexing with a fill factor of 70.
With time that deteriorates, indicating the need to reindex.
If logical fragmentation is above 50%, you have to reindex to regain performance.
********************************************************************************************************
DBCC Commands and Understanding
The script:
dbcc traceon(2520)
dbcc help (‘?’)
GO
Shows the list of DBCC commands. then DBCC HELP(<command>)
If you run DBCC HELP on all the commands you end up with this list:
DBCC activecursors [(spid)]
DBCC addextendedproc (function_name, dll_name)
DBCC addinstance (objectname, instancename)
DBCC adduserobject (name)
DBCC auditevent (eventclass, eventsubclass, success, loginname
, rolename, dbusername, loginid)
DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag])
DBCC balancefactor (variance_percent)
DBCC bufcount [(number_of_buffers)]
DBCC buffer ( {'dbname' | dbid} [, objid [, number [, printopt={0|1|2} ]
[, dirty | io | kept | rlock | ioerr | hashed ]]])
DBCC bytes ( startaddress, length )
DBCC cachestats
DBCC callfulltext
DBCC checkalloc [('database_name'[, NOINDEX | REPAIR])]
[WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]]
DBCC checkcatalog [('database_name')] [WITH NO_INFOMSGS]
DBCC checkconstraints [( 'tab_name' | tab_id | 'constraint_name' | constraint_id )]
[WITH ALL_CONSTRAINTS | ALL_ERRORMSGS]
DBCC checkdb [('database_name'[, NOINDEX | REPAIR])]
[WITH NO_INFOMSGS[, ALL_ERRORMSGS]
[, PHYSICAL_ONLY][, ESTIMATEONLY][,DBCC TABLOCK]
DBCC checkdbts (dbid, newTimestamp)]
DBCC checkfilegroup [( [ {'filegroup_name' | filegroup_id} ]
[, NOINDEX] )] [WITH NO_INFOMSGS
[, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]
DBCC checkident ('table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ] )
DBCC checkprimaryfile ( {'FileName'} [, opt={0|1|2|3} ])
DBCC checktable ('table_name'[, {NOINDEX | index_id | REPAIR}])
[WITH NO_INFOMSGS[, ALL_ERRORMSGS]
[, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]]
DBCC cleantable ('database_name'|database_id, 'table_name'|table_id,[batch_size])
DBCC cacheprofile [( {actionid} [, bucketid])
DBCC clearspacecaches ('database_name'|database_id,
'table_name'|table_id, 'index_name'|index_id)
DBCC collectstats (on | off)
DBCC concurrencyviolation (reset | display | startlog | stoplog)
DBCC config
DBCC cursorstats ([spid [,'clear']])
DBCC dbinfo [('dbname')]
DBCC dbrecover (dbname [, IgnoreErrors])
DBCC dbreindex ('table_name' [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS]
DBCC dbreindexall (db_name/db_id, type_bitmap)
DBCC dbrepair ('dbname', DROPDB [, NOINIT])
DBCC dbtable [({'dbname' | dbid})]
DBCC debugbreak
DBCC deleteinstance (objectname, instancename)
DBCC des [( {'dbname' | dbid} [, {'objname' | objid} ])]
DBCC detachdb [( 'dbname' )]
DBCC dropcleanbuffers
DBCC dropextendedproc (function_name)
DBCC dropuserobject ('object_name')
DBCC dumptrigger ({'BREAK', {0 | 1}} | 'DISPLAY' | {'SET', exception_number}
| {'CLEAR', exception_number})
DBCC errorlog
DBCC extentinfo [({'database_name'| dbid | 0}
[,{'table_name' | table_id} [, {'index_name' | index_id | -1}]])]
DBCC fileheader [( {'dbname' | dbid} [, fileid])
DBCC fixallocation [({'ADD' | 'REMOVE'},
{'PAGE' | 'SINGLEPAGE' | 'EXTENT' | 'MIXEDEXTENT'}
, filenum, pagenum [, objectid, indid])
DBCC flush ('data' | 'log', dbid)
DBCC flushprocindb (database)
DBCC free dll_name (FREE)
DBCC freeproccache
dbcc freeze_io (db)
dbcc getvalue (name)
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.
dbcc incrementinstance (objectname, countername, instancename, value)
dbcc ind ( { 'dbname' | dbid }, { 'objname' | objid }, { indid | 0 | -1 | -2 } )
DBCC indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid |indname})
DBCC inputbuffer (spid)
DBCC invalidate_textptr (textptr)
DBCC invalidate_textptr_objid (objid)
DBCC iotrace ( { 'dbname' | dbid | 0 | -1 }
, { fileid | 0 }, bufsize, [ { numIOs | -1 }
[, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] )
DBCC latch ( address [, 'owners'] [, 'stackdumps'])
DBCC lock ([{'DUMPTABLE' | 'DUMPSTATS' | 'RESETSTATS' | 'HASH'}] |
[{'STALLREPORTTHESHOLD', stallthreshold}])
DBCC lockobjectschema ('object_name')
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']]...]]])
DBCC loginfo [({'database_name' | dbid})]
DBCC matview ({'PERSIST' | 'ENDPERSIST' | 'FREE' | 'USE' | 'ENDUSE'})
DBCC memobjlist [(memory object)]
DBCC memorymap
DBCC memorystatus
DBCC memospy
DBCC memusage ([IDS | NAMES], [Number of rows to output])
DBCC monitorevents ('sink' [, 'filter-expression'])
DBCC newalloc - please use checkalloc instead
DBCC no_textptr (table_id , max_inline)
DBCC opentran [({'dbname'| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]]
DBCC outputbuffer (spid)
DBCC page ( {'dbname' | dbid}, filenum, pagenum
[, printopt={0|1|2|3} ][, cache={0|1} ])
DBCC perflog
DBCC perfmon
DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}
, targetfile, targetpg, order={1|0})
DBCC pintable (database_id, table_id)
DBCC procbuf [({'dbname' | dbid}[, {'objname' | objid}
[, nbufs[, printopt = { 0 | 1 } ]]] )]
DBCC proccache
DBCC prtipage (dbid, objid, indexid [, [{{level, 0}
| {filenum, pagenum}}] [,printopt]])
DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )]
DBCC readpage ({ dbid, 'dbname' }, fileid, pageid
, formatstr [, printopt = { 0 | 1} ])
DBCC rebuild_log (dbname [, filename])
DBCC renamecolumn (object_name, old_name, new_name)
DBCC resource
DBCC row_lock (dbid, tableid, set) - Not Needed
DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+)
DBCC ruleon ( rulenum | rulestring } [, { rulenum | rulestring } ]+)
DBCC setcpuweight (weight)
DBCC setinstance (objectname, countername, instancename, value)
DBCC setioweight (weight)
DBCC show_statistics ('table_name', 'target_name')
DBCC showcontig (table_id | table_name [, index_id | index_name]
[WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]])
DBCC showdbaffinity
DBCC showfilestats [(file_num)]
DBCC showoffrules
DBCC showonrules
DBCC showtableaffinity (table)
DBCC showtext ('dbname', {textpointer | {fileid, pageid, slotid[,option]}})
DBCC showweights
DBCC shrinkdatabase ({dbid | 'dbname'}, [freespace_percentage
[, {NOTRUNCATE | TRUNCATEONLY}]])
DBCC shrinkfile ({fileid | 'filename'}, [compress_size
[, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]])
DBCC sqlmgrstats
DBCC sqlperf (LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]}
| {THREADS} | {LOGSPACE})
DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, 'THREADID'}] )]
DBCC tab ( dbid, objid )
DBCC tape_control {'query' | 'release'}[,('\\.\tape')]
DBCC tec [( uid[, spid[, ecid]] )]
DBCC textall [({'database_name'|database_id}[, 'FULL' | FAST] )]
DBCC textalloc ({'table_name'|table_id}[, 'FULL' | FAST])
DBCC thaw_io (db)
DBCC traceoff [( tracenum [, tracenum ... ] )]
DBCC traceon [( tracenum [, tracenum ... ] )]
DBCC tracestatus (trace# [, ...trace#])
DBCC unpintable (dbid, table_id)
DBCC updateusage ({'database_name'| 0} [, 'table_name' [, index_id]])
[WITH [NO_INFOMSGS] [,] COUNT_ROWS]
DBCC upgradedb (db) DBCC usagegovernor (command, value)
DBCC useplan [(number_of_plan)]
DBCC useroptions DBCC wakeup (spid)
DBCC writepage ({ dbid, 'dbname' }, fileid, pageid, offset, length, data)
DBCC Commands and Explanation
DBCC CACHESTATS displays information about the objects currently in the buffer cache, such as hit rates,
compiled objects and plans, etc.
Example:
DBCC CACHESTATS
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’s 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 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.
Example:
DBCC DROPCLEANBUFFERS
*****
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 Servers on a weekly basis. Also, you can accomplish the same thing using this stored procedure: sp_cycle_errorlog.
Example:
DBCC 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.
Example:
DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = ‘database_name’)
DBCC FLUSHPROCINDB (@intDBID)
*****
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 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 remove 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.
Example:
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.
Example:
DBCC FREEPROCCACHE
*****
DBCC MEMORYSTATUS:
Lists a breakdown of how the SQL Server buffer cache is divided up, including buffer activity. This is an undocumented command, and one that may be dropped in future versions of SQL Server.
Example:
DBCC MEMORYSTATUS
*****
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.
Example:
DBCC OPENTRAN(‘database_name’)
*****
DBCC PAGE: Use this command to look at contents of a data page stored in SQL Server.
Example:
DBCC PAGE ({dbid|dbname}, pagenum [,print option] [,cache] [,logical])
where:
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 it 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 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 into the cache 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.
Example:
DBCC PROCCACHE
*****
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 perform 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 byscheduling 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.
Example:
DBCC DBREINDEX(‘table_name’, fillfactor)
or
–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)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = ‘base table’
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT “Reindexing ” + @TableName
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE 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, 2000]Updated 10-16-2005
*****
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.
Example:
DBCC SHOWCONTIG (Table_id, IndexID)
Or:
–Script to identify table fragmentation
–Declare variables
DECLARE
@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
SELECT @IndexID = IndID
FROM sysindexes
WHERE id = @ID AND name = @IndexName
–Display the fragmentation
DBCC SHOWCONTIG (@id, @IndexID)
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. [6.5, 7.0, 2000] Updated 3-20-2006
*****
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.
Example:
DBCC SHOW_STATISTICS (table_name, index_name)
********************************************************************************************************
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.
Example:
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 syn
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.
Example:
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])
For specific information on the different kinds of trace flags available, search this website or look them up in Books Online.
*****
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.
Example:
DBCC UPDATEUSAGE (‘databasename’)
..n])
For specific information on the different kinds of trace flags available, search this website or look them up in Books Online.
*****
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.
Example:
DBCC UPDATEUSAGE (‘databasename’)
*******************************************************************************************************
DBCC Commands and Understanding
DBCC CHECKALLOC – Check consistency of disk allocation.
DBCC CHECKCATALOG – Check catalog consistency
DBCC CHECKCONSTRAINTS – Check integrity of table constraints.
DBCC CHECKDB – Check allocation, and integrity of all objects.
DBCC CHECKFILEGROUP – Check all tables and indexed views in a filegroup.
DBCC CHECKIDENT – Check identity value for a table.
DBCC CHECKTABLE – Check integrity of a table or indexed view.
DBCC CLEANTABLE – Reclaim space from dropped variable-length columns.
DBCC dllname – Unload a DLL from memory.
DBCC DROPCLEANBUFFERS – Remove all clean buffers from the buffer pool.
DBCC FREE… CACHE – Remove items from cache.
DBCC HELP – Help for DBCC commands.
DBCC INPUTBUFFER – Display last statement sent from a client to a database instance.
DBCC OPENTRAN – Display information about recent transactions.
DBCC OUTPUTBUFFER – Display last statement sent from a client to a database instance.
DBCC PROCCACHE – Display information about the procedure cache
DBCC SHOW_STATISTICS – Display the current distribution statistics
DBCC SHRINKDATABASE – Shrink the size of the database data and log files.
DBCC SHRINKFILE – Shrink or empty a database data or log file.
DBCC SQLPERF – Display transaction-log space statistics. Reset wait and latch statistics.
DBCC TRACE… – Enable or Disable trace flags
DBCC UPDATEUSAGE – Report and correct page and row count inaccuracies in catalog views
DBCC USEROPTIONS – Return the SET options currently active
SqlSchoolHouse's Blog 