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 . sys.index_columns
B . UPDATE STATISTICS
C . CREATE STATISTICS
D . SET AUTO_CREATE_STATISTICS ON
Answer: D
Explanation:
AUTO_UPDATE_STATISTICS { ON | OFF }
ON specifies that the query optimizer updates statistics when they are used by a query and when they might be out-of-date. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-setÂoptions?view=sql-server-2017#auto_update_statistics