Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
A data administrator is managing an Azure SQL Database that is experiencing periodic slowdowns in query execution, even for queries that historically performed well. The underlying data distribution in some tables has changed significantly due to frequent inserts and updates. Which maintenance task should the administrator perform to address this issue?
- ARebuild indexes
- BShrink the database files
- CUpdate statistics
- DBackup the database
Show answer & explanationAnswer & explanation
Correct answer: C. Update statistics
Outdated database statistics can lead the query optimizer to make poor choices, resulting in slow query performance. Updating statistics provides the optimizer with current information about data distribution.
Why the other options are wrong
- A. Rebuilding indexes can improve performance by reducing fragmentation, but updating statistics specifically addresses issues related to the query optimizer's understanding of data distribution.
- B. Shrinking database files may free up space but can lead to index fragmentation and is generally not recommended for performance improvement.
- D. Backing up the database is crucial for disaster recovery but does not directly address query performance issues related to data distribution changes.
Database Statistics
Database statistics are objects that contain information about the data distribution in one or more columns of a table or indexed view.
- Used by the query optimizer to create efficient execution plans.
- Become outdated with frequent data changes.
- Updating them helps improve query performance.
Memory trick: Stats for Speed, Indexes for Access.