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.

3. Troubleshooting:

It helps to troubleshoot issues like slow-performing queries. Using this, we can easily identify missing or outdated statistics. So, we can update or create statistics to improve execution plans which will provide better performance of a query.

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

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

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