Question

You manage an Azure SQL Database supporting a nightly ETL process that bulk-loads several million rows into a large staging table before it is queried by reporting jobs. Operators report that queries running immediately after the load sometimes choose poor execution plans with severely underestimated row counts, even though AUTO_UPDATE_STATISTICS is enabled. You confirm that the statistics update threshold has not yet been crossed after the bulk load. Which action best ensures the optimizer has accurate cardinality estimates for the reporting queries?

Accepted Answer

Add a step to the ETL process that runs UPDATE STATISTICS on the staging table WITH FULLSCAN after the bulk load completes.

Answer

Enable AUTO_CREATE_STATISTICS on the database so missing statistics are generated automatically.

Answer

Rebuild all indexes on the staging table after each load to refresh statistics with sampled data.

Answer

Enable the ASYNC option for auto update statistics so the optimizer does not wait for updates.