Lesson 5 | Monitoring with dbcc command, continued |
Objective | Monitor the consistency of individual tables. |
Monitor Consistency of individual Tables using dbcc checktable
You can monitor the consistency of individual tables in SQL Server using the `DBCC CHECKTABLE` command.
This command is used specifically to validate the integrity and structural consistency of a single table and its associated indexes.
โ
Syntax:
DBCC CHECKTABLE ('schema_name.table_name') [ WITH options ]
โ
Example:
DBCC CHECKTABLE ('dbo.Employees');
โ
What It Checks:
- Page and row structure of the table
- Index consistency
- Link-level consistency between pages
- System table integrity
- Allocation and deallocation consistency
๐ Useful Options:
Option |
Purpose |
NOINDEX |
Skips checking non-clustered indexes |
PHYSICAL_ONLY |
Limits checks to physical structure and reduces run time |
ALL_ERRORMSGS |
Displays all errors instead of only the first 200 |
TABLOCK |
Uses a table lock instead of an internal database snapshot (faster) |
ESTIMATEONLY |
Estimates tempdb space required for the operation, without running it |
๐ Recommended Usage:
Use it proactively during maintenance windows or after suspect corruption:
-- Full logical & physical check
DBCC CHECKTABLE ('dbo.Customers') WITH ALL_ERRORMSGS, TABLOCK;
-- Lightweight physical check
DBCC CHECKTABLE ('dbo.Orders') WITH PHYSICAL_ONLY;
โ ๏ธ Important Notes:
Important considerations for using the `DBCC CHECKTABLE` command in SQL Server. It highlights three key points:
-
Resource Intensiveness:
DBCC CHECKTABLE
can consume significant system resources (CPU, I/O) when run on large tables. This means it can impact the performance of your SQL Server instance, especially during peak hours. Therefore, it's often recommended to run it during off-peak times or when resource contention is minimal.
-
Detection Only, No Repair: This command is designed to find inconsistencies or corruption within a specific table. It does not fix them. If
DBCC CHECKTABLE
reports errors, you'll need to take further action. This typically involves:
- Restoring the database from a good backup (the preferred method to ensure data consistency).
- In severe cases where a backup isn't viable, using
DBCC CHECKDB
with a repair option (e.g., REPAIR_ALLOW_DATA_LOSS
), but this should be a last resort as it might lead to data loss.
-
Regular Use Recommended: Despite its resource intensity and lack of repair capabilities, the text emphasizes that
DBCC CHECKTABLE
(or more commonly, DBCC CHECKDB
which covers the entire database) should be a regular part of a Database Administrator's (DBA) routine. Regular checks help in:
- Early Detection: Catching potential data corruption issues before they become widespread or critical.
- Proactive Maintenance: Identifying problems that might lead to performance issues or data integrity problems down the line.
- Ensuring Data Integrity: Verifying the logical and physical integrity of the database objects.
In essence, `DBCC CHECKTABLE` is a diagnostic tool that's crucial for maintaining the health and integrity of your SQL Server databases, but it needs to be used thoughtfully, considering its resource impact and the fact that it's a detection-only mechanism. It's often used as part of a larger `DBCC CHECKDB` strategy.
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:
DBCC 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:
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.
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.
sys.dm_db_index_physical_stats dynamic management view (DMV) in SQL Server 2022
In **SQL Server 2022**, the **`DBCC SHOWCONTIG`** command is deprecated and replaced by the **`sys.dm_db_index_physical_stats`** Dynamic Management View (DMV), which provides **fragmentation and page-level statistics** for indexes and heaps.
โ
Purpose of `sys.dm_db_index_physical_stats`
This DMV returns information about:
- Fragmentation (internal and external)
- Page count
- Average page density
- Forwarded records
- Index depth
These statistics are crucial for making decisions about whether to **reorganize** or **rebuild** indexes to maintain optimal query performance.
๐ง Syntax
sys.dm_db_index_physical_stats (
{ database_id | NULL },
{ object_id | NULL },
{ index_id | NULL },
{ partition_number | NULL },
{ mode }
)
---
๐งพ Parameters
Parameter |
Description |
database_id |
ID of the database; use DB_ID() for current DB |
object_id |
ID of the table or view |
index_id |
ID of the index (0 for heap, 1+ for indexes) |
partition_number |
Partition number (NULL for all) |
mode |
Level of scan: 'LIMITED' , 'SAMPLED' , or 'DETAILED' |
๐ Example Queries
-
Check fragmentation for all indexes in current DB
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
JOIN sys.indexes AS i
ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.database_id = DB_ID()
ORDER BY avg_fragmentation_in_percent DESC;
-
Check fragmentation for a specific table
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.page_count
FROM sys.dm_db_index_physical_stats(
DB_ID('YourDatabaseName'),
OBJECT_ID('YourTableName'),
NULL, NULL, 'DETAILED'
) AS ips
JOIN sys.indexes AS i
ON ips.object_id = i.object_id AND ips.index_id = i.index_id;
-
Use to decide whether to reorganize or rebuild
- Reorganize if:
avg_fragmentation_in_percent
BETWEEN 5%โ30%
- Rebuild if:
avg_fragmentation_in_percent
> 30%
๐ Maintenance Script Example
-- Automatically rebuild or reorganize indexes
DECLARE @tableName SYSNAME;
DECLARE index_cursor CURSOR FOR
SELECT DISTINCT OBJECT_NAME(object_id)
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 5;
OPEN index_cursor;
FETCH NEXT FROM index_cursor INTO @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_msforeachtable
@command1 = 'ALTER INDEX ALL ON ? REORGANIZE',
@whereand = 'AND OBJECT_NAME(object_id) = ''' + @tableName + '''';
FETCH NEXT FROM index_cursor INTO @tableName;
END
CLOSE index_cursor;
DEALLOCATE index_cursor;
๐ Notes
LIMITED
mode is fastest but skips detailed info.
- Always filter out indexes with
page_count < 1000
before acting on fragmentation.
- The DMV requires VIEW SERVER STATE permission on the server.
[1]Fragmentation: A condition that occurs when data is not stored consecutively.
