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.

2) DBCC SHOWCONTIG:

Usage:

To fetch index fragmentation details from a database, we can use the DBCC SHOWCONTIG command. This command provides the organizational details of indexes which help the database administrator to catch fragmented indexes which may hit the performance of the query.  

Example:

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

Benefits:

Helps database administrator to catch fragmented indexes which helps to address performance issues.

Helps to prepare maintenance strategies and index optimization plans.

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

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

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