| Lesson 5 | Monitoring with dbcc command, continued |
| Objective | Monitor the consistency of individual tables. |
Monitor Consistency of individual Tables using dbcc checktable
The dbcc checktable command is used to validate that an individual table is healthy. It performs the same checks as dbcc checkdb, but it will only run on one table at a time. The syntax of dbcc checktable is as follows:
sDBCC CHECKTABLE ( 'table_name'
[, NOINDEX | index_id |
{ REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST| REPAIR_REBUILD}]
) [WITH {ALL_ERRORMSGS | NO_INFOMSGS}]
Review of parameters
The optional parameters should look familiar by now, they are the same ones you will use with dbcc checkalloc and dbcc checkdb.
database name – The name of the database to check.
NONINDEX – Causes dbcc checkalloc to skip checking non-clustered indexes.
REPAIR_ALLOW_DATA_LOSS – Performs more complex repairs that can result in data being lost.
REPAIR_FAST – Performs minor fast repairs that can be done without any data lost
REPAIR_REBUILD – Besides the minor repairs, it can perform more complex repairs that will not result in data loss.
ALL_ERRORMSGS – Causes dbcc checkalloc will display all error messages.
NO_INFOMSGS – Causes dbcc checkalloc display no informational messages. When using this option, you will only see messages if there are errors.
dbcc checktable will produce the following output:
database name – The name of the database to check.
NONINDEX – Causes dbcc checkalloc to skip checking non-clustered indexes.
REPAIR_ALLOW_DATA_LOSS – Performs more complex repairs that can result in data being lost.
REPAIR_FAST – Performs minor fast repairs that can be done without any data lost
REPAIR_REBUILD – Besides the minor repairs, it can perform more complex repairs that will not result in data loss.
ALL_ERRORMSGS – Causes dbcc checkalloc will display all error messages.
NO_INFOMSGS – Causes dbcc checkalloc display no informational messages. When using this option, you will only see messages if there are errors.
dbcc checktable will produce the following output:
DBCC results for 'Customers'. There are 91 rows in 3 pages for object 'Customers'.
dbcc showcontig
dbcc showcontig displays fragmentation[1] information for a table. It will display how the data is physically sorted and stored. This is independent of the index order and, instead, has to do with the order in which the data pages were allocated to a table.
While this information shows how continuous the data is in the file, it does not show how continuous the file is. Because of this, a seemingly continuous data file could be fragmented.
While this information shows how continuous the data is in the file, it does not show how continuous the file is. Because of this, a seemingly continuous data file could be fragmented.
Use an operating sytem defragmentor to keep the files continuous. The syntax is as follows:
DBCC SHOWCONTIG [ ( table_id [, index_id] ) ]
Table_id – The identifier of the table
Index_id – The identifier of the tables index
Dbcc showcontig can be used to check either a table or an individual index. It will produce a report that shows you what SQL Server needs to do when it reads the data. This will include the number of pages, extents, and how much fragmentation there is.
To find out the table_id for a given table. use the sql: select object_id(table_name). The next lesson will continue with the dbcc commands that can be used to check a database.
Index_id – The identifier of the tables index
Dbcc showcontig can be used to check either a table or an individual index. It will produce a report that shows you what SQL Server needs to do when it reads the data. This will include the number of pages, extents, and how much fragmentation there is.
To find out the table_id for a given table. use the sql: select object_id(table_name). The next lesson will continue with the dbcc commands that can be used to check a database.
[1]Fragmentation: A condition that occurs when data is not stored consecutively.
