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?

Question

Options

• Asys.dm_db_index_usage_stats
• BDBCC CHECKALLOC
• CDBCC SHOWCONTIG
• Dsts.dm_db_index_physical_stats

• A
  4% (1)
• C
  4% (1)
• D
  91% (21)

Explanation

Explanation

sys.dm_db_index_physical_stats (option D) is the correct dynamic management function (DMF) for retrieving fragmentation information - including average fragmentation percentage, page counts, and fill factor details - for indexes and data in a specified database, making it the go-to tool for index maintenance decisions.

Why the distractors are wrong:

• A (sys.dm_db_index_usage_stats): This DMV tracks how indexes are being used (seeks, scans, lookups, updates) but provides no fragmentation data - it's about usage patterns, not physical condition.
• B (DBCC CHECKALLOC): This checks the consistency of disk space allocation for a database (e.g., detecting allocation errors), not index fragmentation.
• C (DBCC SHOWCONTIG): This was the legacy SQL Server 2000-era command for checking fragmentation, but it has been deprecated since SQL Server 2005 and replaced by sys.dm_db_index_physical_stats.

Memory Tip: Think "Physical = Fragmentation" - the word physical in sys.dm_db_index_physical_stats directly hints that it examines the physical state (including fragmentation) of your indexes. If a question mentions fragmentation, look for this DMF immediately.

No community discussion yet for this question.