Learn how to query fragmentation details of data and indexes for tables in an Azure SQL database using the right system command for optimal database performance.
Table of Contents
Question
You have an Azure SQL database named DB1.
You need to query the fragmentation information of data and indexes for the tables in DB1.
Which command should you run?
A. sys.dm_db_index_usage_stats
B. DBCC CHECKALLOC
C. DBCC SHOWCONTIG
D. sts.dm_db_index_physical_stats
Answer
D. sts.dm_db_index_physical_stats
Explanation
To query the fragmentation information for data and indexes of tables in the Azure SQL database DB1, you should run the following command:
D. sys.dm_db_index_physical_stats
The sys.dm_db_index_physical_stats is a Dynamic Management View (DMV) that returns fragmentation information for the data and indexes of tables and views in the specified database. It provides detailed information about the logical and physical fragmentation of objects, including:
- Index depth
- Average fragmentation percentage
- Record copy percentage
- Extent switch counters
- Compressed page statistics
This DMV is useful for identifying tables and indexes with high fragmentation levels, which can negatively impact query performance. By regularly monitoring fragmentation using this command, you can proactively identify and address fragmentation issues, either through index rebuilds or reorganizations.
The other options provided are not suitable for querying fragmentation information:
A. sys.dm_db_index_usage_stats returns usage statistics for indexes, not fragmentation details.
B. DBCC CHECKALLOC checks the allocation of pages in the database, not fragmentation.
C. DBCC SHOWCONTIG displays fragmentation information for a specific table or view, but not for the entire database.
Administering Relational Databases on Microsoft Azure DP-300 certification exam practice question and answer (Q&A) dump with detail explanation and reference available free, helpful to pass the Administering Relational Databases on Microsoft Azure DP-300 exam and earn Administering Relational Databases on Microsoft Azure DP-300 certification.