A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.

A custome

r that uses an on-premises instance reports that queries take a long time to complete.

You need to reconfigure table statistics so that the query optimizer can use the optimal query execution plans available.

Which Transact-SQL segment should you use?

A

.

sp_autostats

B.

AUTO_UPDATE_STATISTICS_ASYNC

C.

SET AUTO_UPDATE_STATISTICS ON

D.

CREATE STATISTICS

Explanation:

You can turn on automatic statistics update by running this SQL statement:

SET AUTO_UPDATE_STATISTICS ON

Incorrect Answers:

A:

sp_autostats without options just displays the settings.

B: The AUTO_UPDATE_STATISTICS_ASYNC option affects how automatic statistics updates are applied to your SQL Server database. When this option is enabled, the Query Optimizer will not wait for the upd

ate of statistics, but will run the query first and update the outdated statistics afterwards. When this option is disabled, the Query Optimizer will update the outdated statistics before compiling the query therefore possibly getting a better plan based o

n the most current statistics. This is referred to as synchronous statistics updates.

References: https://www.mssqltips.com/sqlservertip/2766/sql-server-auto-update-and-auto-create-statistics-options/