You deploy a Microsoft SQL Server instance to support a global sales application. The instance includes the following tables: Table A and Table B

HOTSPOT

You deploy a Microsoft SQL Server instance to support a global sales application. The instance includes the following tables: Table A and Table B.

TableA is a partitioned table that uses an incrementing integer number for partitioning. The table has millionsof rows in each partition. Most changes to the data in TableA affect recently added data. The UPDATESTATISTICS for TableA takes longer to complete than the allotted maintenance window.

Thousands of operations are performed against TableB each minute. You observe a large number of AutoUpdate Statistics events for TableB.

You need to address the performance issues with each table.

In the table below, identify the action that will resolve the issues for each table.

NOTE: Make only one selection in each column.

View Answer

Answer:

Explanation:

Table A: Auto_update statistics off

Table A does not change much. There is no need to update the statistics on this table.

Table B: SET AUTO_UPDATE_STATISTICS_ASYNC ON

You can set the database to update statistics asynchronously:

ALTER DATABASE YourDBName

SET AUTO_UPDATE_STATISTICS_ASYNC ON

If you enable this option then the Query Optimizer will run the query first and update the outdated statistics afterwards. When you set this option to OFF, the Query Optimizer will update the outdated statistics before compiling the query. This option can be useful in OLTP environments

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