Monitoring Databases  «Prev  Next»

Lesson 6Monitoring with dbcc command, continued
Objective Perform miscellaneous database monitoring with dbcc.

How to Perform Miscellaneous Database Monitoring with dbcc

Although `dbcc` commands can still be used in SQL Server 2019, it's generally not recommended for miscellaneous database monitoring". Here's why:
Limitations of `dbcc` for Monitoring:
  • Limited Scope: `dbcc` commands primarily focus on "data integrity checks and repairs", not comprehensive performance monitoring.
  • High Impact: Running certain `dbcc` commands can significantly "impact database performance", causing temporary slowdowns or blocking.
  • Outdated Approach: Newer, purpose-built tools offer broader, real-time monitoring capabilities and require less manual intervention.

Better Alternatives for Monitoring:
  • Built-in tools: Utilize tools like "SSMS Activity Monitor, Performance Dashboard, and DMVs" for performance metrics, wait times, and overall health.
  • Extended Events: Leverage this powerful tool for detailed performance data capture and analysis for troubleshooting.
  • Third-party tools: Explore options like "Azure Monitor, SolarWinds SQL Sentry, Datadog, Dynatrace, and Redgate SQL Monitor for comprehensive monitoring, alerting, and performance analysis.

When to use `dbcc`:
While not primarily for monitoring, `dbcc` still holds value in specific situations:
  • Manual verification of data integrity: After suspected corruption or inconsistencies, specific `dbcc` checks can provide targeted verification.
  • Troubleshooting specific issues: Certain `dbcc` commands assist in diagnosing specific performance bottlenecks or data consistency problems.

Use `dbcc` judiciously and with caution due to its potential performance impact. For general monitoring, newer, purpose-built tools provide a more efficient and comprehensive approach.

DBCC Commands

  1. Dbcc checkfilegroup performs the same checks as dbcc checkdb, but it can not fix any data. Unless you specify NO_INFOMSGS, a report will be generated that lists of all the tables on the filegroup.
  2. dbcc checkident command validates the current identity in a table, and it will fix the value if it needs to. You should use this if you are manually setting identity values in a table. If SQL Server is always automatically setting identity values, there is no reason to use this command. The current identity is the next value that will be generated for an identity column. The syntax is as follows: 
    DBCC CHECKFILEGROUP
( [{ 'filegroup' | filegroup_id}] [, NOINDEX] 
) [WITH {ALL_ERRORMSGS | NO_INFOMSGS}]
  3. dbcc checkfilegroup: dbcc checkfilegroup is used to verify all of the tables on a file group. You can use it to make sure that a filegroup is valid before you perform a filegroup backup. Also, you can use it to check a database one filegroup at a time if it takes too long to check a whole database. The syntax is as follows: (Note that it uses parameters with which you are already familiar.)

dbcc checkident Syntax in SQL-Server

Check Indent 
DBCC CHECKIDENT
Check Indent

  1. Table_name – The name of the table to check.
  2. Noreseed – Tells SQL Server to only check the value without fixing it.
  3. RESEED – Tells SQL Server to fix the identity, default value.
  4. New_reseed_value – This is the value to use when reseeding the table.

Ad High Performance SQL Server

DBCC CHECKIDENT ('table'):

Verifies the consistency of the current identitycolumn value and the identity column for a specifi c table. If a problem exists, the next value for the identity column is updated to correct any error. If the identity column is broken, the new identity value violates a primary key or unique constraint and new rows cannot be added to the table. You can also use this command to reseed the current identity value by using the RESEED option and a new_reseed_value. The following code demonstrates the use of the DBCC CHECKIDENT command. If it is needed, this command resets the current identity value of the Employee table in 

the AdventureWorks2012 sample database:
Use AdventureWorks2012;
DBCC CHECKIDENT ("HumanResources.Employee");


When you run dbcc checkident it will report on the current value for the next identity and the current value in the identity column. If the table is not corrupt, the values should match. Checking identity information: current identity value '1', current column value '1'.

dbcc dbreindex

The dbcc dbreindex command rebuilds one or more indexes on a table. This is equivalent to dropping the index and then creating the same index, but it does it in one step and does not require you to remove foreign key constraints while rebuilding primary keys.
The syntax is: 

DBCC DBREINDEX 
( [ 'database.owner.table_name' [, index_name [, fillfactor ] ] ]
) [WITH NO_INFOMSGS]

The next lesson will cover how to monitor the transaction log.
SEMrush Software