Question

You manage an Azure SQL Database supporting a high-concurrency OLTP application. Users intermittently report query timeouts that coincide with statistics being updated. Investigation shows queries are waiting during query compilation because the optimizer triggers a synchronous statistics update on a large table before the plan is generated. You want to reduce this compilation-time blocking without disabling automatic statistics updates. Which database setting change should you make?

Accepted Answer

Enable AUTO_UPDATE_STATISTICS_ASYNC so statistics updates run in the background and queries compile with existing statistics

Answer

Disable AUTO_UPDATE_STATISTICS entirely and rely on a scheduled maintenance job

Answer

Enable AUTO_CREATE_STATISTICS to generate additional column statistics

Answer

Set the database compatibility level to a lower value to disable Intelligent Query Processing