A large database on Azure SQL Managed Instance has a table with a non-clustered index that is heavily used by queries. The index has fragmentation of 80% and the statistics are stale. The DBA wants to perform index maintenance with minimal impact on the production workload. The maintenance window is 1 hour. What should the DBA do?

Rebuild the index with SORT_IN_TEMPDB = ON and update statistics with a sample rate.

Reorganize the index and update statistics with a sample rate.

Rebuild the index with ONLINE = ON and update statistics with FULLSCAN.

Skip the rebuild and only update statistics.

Your company runs a SQL Server 2019 instance on an Azure VM. The database has a large fact table with frequent inserts and updates. You notice that the query optimizer often chooses a table scan instead of an index seek for a commonly used filtered query. You also see that the index fragmentation is above 30% and the last statistics update was over a week ago. You need to improve query performance with minimal downtime. What should you do first?

Update statistics using UPDATE STATISTICS

Run DBCC CHECKDB to verify index integrity

Rebuild the indexes using ALTER INDEX ... REBUILD

Disable the index and recreate it

Microsoft Certified:Azure Database Administrator Associate

Domain 3Objective 3

Configure Database Solutions for Optimal Performance DP-300 Practice Questions (Page 2)

Part of the Monitor, configure, and optimize database resources domain, which accounts for 20–25% of the DP-300 exam. Microsoft does not publish an official question count, but from its 100-minute exam (~40–65 total, ~8–16 in this domain), expect 3–5 from this objective — we provide 26 practice questions to prepare you well beyond it. (estimate)

26questions here

6free pages

9concepts

20–25%of the exam

Questions 6–10

6foundation · easy
Which index maintenance operation should be performed to remove fragmentation from an index without rebuilding it from scratch?
Select an answer first
7expert · medium
A large database on Azure SQL Managed Instance has a table with a non-clustered index that is heavily used by queries. The index has fragmentation of 80% and the statistics are stale. The DBA wants to perform index maintenance with minimal impact on the production workload. The maintenance window is 1 hour. What should the DBA do?
Select an answer first
8application · medium
Your company runs a SQL Server 2019 instance on an Azure VM. The database has a large fact table with frequent inserts and updates. You notice that the query optimizer often chooses a table scan instead of an index seek for a commonly used filtered query. You also see that the index fragmentation is above 30% and the last statistics update was over a week ago. You need to improve query performance with minimal downtime. What should you do first?
Select an answer first
9foundation · easy
Which Azure SQL Database purchasing model allows you to pay only for the resources you use and automatically scale based on workload?
Select an answer first
10foundation · easy
Which Resource Governor component is used to classify incoming sessions into a workload group?
Select an answer first

Finished these 5 questions?

Review the revealed explanations, or continue through the curriculum.

Next 5 questions →Back to DP-300

Free Basic Practice is a study aid with revealable answers — not a scored exam. Examers.io is independent and not affiliated with or endorsed by Microsoft. “DP-300” is a trademark of its owner, used for identification only.

Configure Database Solutions for Optimal Performance DP-300 Practice Questions (Page 2)

Questions 6–10

Which index maintenance operation should be performed to remove fragmentation from an index without rebuilding it from scratch?

Which Azure SQL Database purchasing model allows you to pay only for the resources you use and automatically scale based on workload?

Which Resource Governor component is used to classify incoming sessions into a workload group?

Keep studying — Monitor, configure, and optimize database resources