Exam 70-464 topic 1 question 59 discussion

Actual exam question from Microsoft's 70-464

Question #: 59
Topic #: 1

You have a SQL Server instance named SQL\Instance1. Instance1 contains a database named Database1.
You need to recommend an index defragmentation solution for an index on a disk-based table named ContentIndex. ContentIndex must meet the following requirements:
✑ Remain online during the defragmentation.
✑ Update distribution statistics.
✑ Perform defragmentation as quickly as possible.
Which type of index defragmentation solution should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

A. DBCC DBREINDEX
B. REORGANIZE
C. REBUILD
D. DBCC INDEXDEFRAG

Show Suggested Answer

Suggested Answer: B 🗳️
The REORGANIZE operation is always performed online.
Incorrect Answers:
C: With the REBUILD command, the data is "offline" and unavailable during the rebuild.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

by sanketh at May 23, 2020, 9:10 a.m.

Comments

Submit Cancel

Luzix

4 years, 7 months ago

ALTER INDEX REBUILD WITH (ONLINE = ON)*

upvoted 1 times

...

Luzix

4 years, 7 months ago

Solution: C Statistics: When an index is created or rebuilt, statistics are created or updated by scanning all the rows in the table. However, starting with SQL Server 2012 (11.x), statistics are not created or updated by scanning all the rows in the table when a partitioned index is created or rebuilt. Instead, the Query Optimizer uses the default sampling algorithm to generate these statistics. To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause. When an index is reorganized, statistics are not updated. https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15

upvoted 1 times

...

sanketh

5 years ago

I would go with C , since there is a requirement to update stats as well which is not possible in reorganize feature

upvoted 2 times

...