PdfPrep.com

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 . 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

Exit mobile version