Home » Microsoft » 70-764 v.2 » Which Transact-SQL segment should you use?
A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.
A customer 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
Correct Answer: C
Explanation/Reference:
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 update 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 on 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/
