Exam 70-764 topic 2 question 6 discussion

Actual exam question from Microsoft's 70-764

Question #: 6
Topic #: 2

You maintain a database named DB1 that has a nonpartitioned table. You create an index in the table. Automatic statistic updates are disabled.
Uses report that some Microsoft SQL Server Reporting Service (SSRS) queries take long time to run. You determine that the issue is caused by stale statistics for
Index1.
You need to update the statistics for the index.
What should you do?

A. Set the value of the Database Read-Only property to True.
B. Run the system stored procedure sp_updatestats.
C. Set the value of the Auto Create Incremental Statistics property to True.
D. Run the UPDATE STATISTICS command with the COLUMNS option.

Show Suggested Answer

Suggested Answer: D 🗳️
UPDATE STATISTICS updates query optimization statistics on a table or indexed view.
The ALL | COLUMNS | INDEX options update all existing statistics, statistics created on one or more columns, or statistics created for indexes. If none of the options are specified, the UPDATE STATISTICS statement updates all statistics on the table or indexed view.
Note: You can update query optimization statistics on a table or indexed view in SQL Server by using SQL Server Management Studio or Transact-SQL. By default, the query optimizer already updates statistics as necessary to improve the query plan; in some cases you can improve query performance by using
UPDATE STATISTICS or the stored procedure sp_updatestats to update statistics more frequently than the default updates.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017

by jolsca at Sept. 2, 2020, 11:05 p.m.