70-473 · Question #73
You manage a large Microsoft Azure SQL database. The database contains data that changes several thousand times each day. You need to ensure that the performance of the database stays consistent when
The correct answer is A. Update the statistics of all the tables in the database.. To maintain consistent performance in a frequently changing Azure SQL database, it is essential to regularly update table statistics.
Question
Options
- AUpdate the statistics of all the tables in the database.
- BClear the procedure cache of the database.
- CRun a consistency check on the tables in the database.
- DEnable Extended Events for the server that contains the database.
How the community answered
(54 responses)- A80% (43)
- B6% (3)
- C4% (2)
- D11% (6)
Why each option
To maintain consistent performance in a frequently changing Azure SQL database, it is essential to regularly update table statistics.
Updating statistics ensures the query optimizer has accurate information about data distribution, allowing it to generate efficient execution plans. With data changing several thousand times daily, maintaining up-to-date statistics is critical for consistent query performance and preventing performance degradation.
Clearing the procedure cache forces recompilation of queries, which can be disruptive and does not provide sustained consistent performance for frequently changing data.
Running a consistency check verifies database integrity but does not directly address query performance consistency related to data changes or query optimization.
Enabling Extended Events is a monitoring tool for capturing diagnostic data and does not directly improve or maintain database performance.
Concept tested: Database performance optimization - statistics management
Source: https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics
Topics
Community Discussion
No community discussion yet for this question.