DBCC Commands in Microsoft SQL Server - DBA should know - Part - 3

1) DBCC SHOW_STATISTICS

Introduction:

To get detailed statistical information on the density of data of a particular table or index, in SQL Server, we have the DBCC SHOW_STATISTICS command.

Usage:

Another DBCC Informational command is SHOW_STATISTICS. It is used to validate the statistics of an index or table. It displays in-depth details of the data distribution of a database object which helps database administrators at the time of query analysis & optimizing query performance.

Example:

Syntax:

DBCC SHOW_STATISTICS (<TableName>,<IndexName>);

Example:

DBCC SHOW_STATISTICS ('EmpMaster', 'IX_EmpMaster_DeptID');

Benefits:

This DBCC command offers multiple benefits:

1. Query Optimization:

It helps database administrators to decide whether the new index is required or needs to modify the existing index or need to remove any existing index to improve the performance of a query. 

2. Performance Tuning:

Helps database administrators to identify performance bottlenecks in the database. It provides a facility to fetch statistical details like the density of data, histogram, and cardinality details which helps administrators to fine-tune the indexes of a database and improve the performance of the database/query.

DBCC Commands in Microsoft SQL Server - DBA should know - Part - 2

DBCC INDEXDEFRAG:

Usage:

To defragment indexes in the database, we can use the DBCC INDEXDEFRAG command. Actually, when we fire the DBCC INDEXDEFRAG command in SQL Server, it runs & tries to reduce fragmentation and enhance the performance of the query. Also tries to reorganize all pages of clustered and non-clustered indexes which are available at leaf-level.

Example:

DBCC INDEXDEFRAG('<DatabaseName>,<TableName>,<IndexName>')

DBCC INDEXDEFRAG('AdventureWorksDW2016','DimSalesTerritory','AK_SalesTerritory')

Benefits:

It reduces fragmentation & improves the performance of the TSQL queries.

It also minimizes IO operation.

DBCC Commands in Microsoft SQL Server - DBA should know - Part - 1

DBCC Commands in SQL Server

SQL Server has so many DBCC commands which are available in documentation & a few DBCC commands are not in documents. DBAs should be aware of these DBCC commands to perform their daily tasks.

In this article, we'll try to explain & give in-depth knowledge about the DBCC commands, types of DBCC commands based on categories, etc.

Introduction

DBCC stands for Database Console Commands. DBCC commands are very powerful tools that help database administrators to maintain and troubleshoot database performance, integrity, index maintenance, memory allocation management, and consistency.

Microsoft has grouped Database Console Commands in the below categories:

DBCC Category

Command category

Perform

Maintenance

Maintenance tasks on a database, index, or filegroup.

Miscellaneous

Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

Informational

Tasks that gather and display various types of information.

Validation

Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.