Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 93 discussion

Actual exam question from Microsoft's 70-762

Question #: 93
Topic #: 1

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You have a table that has a clustered index and a nonclustered index. The indexes use different columns from the table. You have a query named Query1 that uses the nonclustered index.
Users report that Query1 takes a long time to report results. You run Query1 and review the following statistics for an index seek operation:

You need to resolve the performance issue.
Solution: You defragment both indexes.
Does the solution meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: B 🗳️
We see Actual Number of Row is 3571454, while Estimated Number of Rows is 0.
This indicates that the statistics are old, and need to be updated.

by grain at March 22, 2020, 8:05 p.m.

Comments

Submit Cancel

HA2020

4 years, 5 months ago

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15 "you can defragment an index by either reorganizing an index or rebuilding an index by running Transact-SQL commands in your tool of choice or by using SQL Server Management Studio."

upvoted 1 times

...

Alex5x

4 years, 8 months ago

Indeed, the question is tricky and missing some information. You can defragment an index by either reorganizing an index (ALTER INDEX REORGANIZE) or rebuilding an index (ALTER INDEX REBUILD). According to Microsoft (https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-2016#Restrictions): When an index is created or rebuilt, statistics are created or updated by scanning all the rows in the table. When an index is reorganized, statistics are not updated. It is clear that we want to update statistics to solve the issue. However, which approach they mean when saying "You defragment both indexes." That is the first question. The second one is what if the rebuild index was done with STATISTICS_NORECOMPUTE = ON option? Will the statistics be updated in this case? And the final question: why should I defragment BOTH indexes? I should care only about the NonClustered one, shouldn't? Finally, I would go with the answer "No" just because I know that there is another solution in the Q94 (You update statistics for the nonclustered index) which is correct.

upvoted 1 times

Hoglet

4 years, 6 months ago

Rebuild always means rebuild, while defrag means reorganize. If an option isn't mentioned, assume it hasn't been selected and it's the default behaviour.

upvoted 2 times

...

databasejamdown

4 years, 8 months ago

Question is tricky and doesn't seem to give enough information. According to Microsoft's website. Defrag may be accomplished by rebuilding or reorganizing. Rebuilding updates statistics automatically and reorganizing does not: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15. Therefore we would need to now if defragging is referring to rebuilding or re-organizing. I would Answer No based on Cococo's statement, but the truth is I really do not know.

upvoted 2 times

...

Cococo

4 years, 9 months ago

Rebuild is running DBCC DBREINDEX, Reorganize - DBCC INDEXDEFRAG, and according to Microsoft when an index is reorganized, stats are not updated - https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15

upvoted 2 times

Cococo

4 years, 9 months ago

92, 93 & 94 are the same bunch of questions and 94 (with update stats for nonclustered idx) is the one right answer

upvoted 1 times

...

Newt

5 years, 1 month ago

defragment means to remove the wasted space from a fragmented index. Other post is fragmented not defragment

upvoted 1 times

...

Newt

5 years, 1 month ago

Defragment means you have pages that are only half full, so you have a lot of wasted space on your disk

upvoted 1 times

...

grain

5 years, 3 months ago

What does defragment mean, index rebuild?

upvoted 2 times

Nickname17

5 years, 2 months ago

Index reorganize

upvoted 2 times

...

Hoglet

5 years ago

You have 2 options defraging an index. A rebuild creates a second version of the index, and then deletes the old one. This is a big-bang approach. If you stop if mid-way, you have nothing to show. The other option is a reorg. This does a bubble sort of all the index pages. If you stop it mid-way, you have still made some progress. But if the index is too fragmented, it will take a loooong time.

upvoted 2 times

...