Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureHard
A database administrator is managing an Azure SQL Database. The database experiences periods of high query load, leading to some queries taking longer than expected. The DBA suspects that the query optimizer is making suboptimal choices for certain complex queries. To help the query optimizer make better decisions and improve query performance without rewriting the queries, which database management task should the DBA perform regularly?
- AShrink the database files
- BChange the database compatibility level
- CUpdate statistics
- DRebuild indexes
Show answer & explanationAnswer & explanation
Correct answer: C. Update statistics
Updating statistics regularly provides the SQL Server query optimizer with up-to-date information about the data distribution in tables and indexes. This accurate information allows the optimizer to create more efficient query execution plans, directly improving performance for complex queries without requiring code changes.
Why the other options are wrong
- A. Shrinking database files can lead to index fragmentation and generally degrades performance, it's not a recommended routine task for performance improvement.
- B. Changing the database compatibility level can introduce new query optimizer behaviors but is a significant change with potential for unexpected side effects, not a routine performance improvement task like updating statistics.
- D. Rebuilding indexes can improve performance by removing fragmentation and updating statistics, but it's a more resource-intensive operation. Updating statistics directly and more frequently is often sufficient and less impactful.
Database Statistics
Objects that contain statistical information about the distribution of data in one or more columns of a table or indexed view, used by the query optimizer to create efficient execution plans.
- Used by the query optimizer to estimate row counts.
- Impacts the choice of join types, access methods, and index usage.
- Can become outdated as data changes.
- Regular updates are crucial for optimal query performance.
Memory trick: Statistics give the optimizer the smarts to speed up queries.