Exam 70-764 All Questions

View all questions & answers for the 70-764 exam

Exam 70-764 topic 1 question 50 discussion

Actual exam question from Microsoft's 70-764

Question #: 50
Topic #: 1

DRAG DROP -
You administer a database that is used for reporting purposes. The database has a large fact table that contains three hundred million rows. The table includes a clustered columnstore index and a nonclustered index on the ProductID column. New rows are inserted into the table every day.
Performance of queries that filter the Product ID column have degraded significantly.
You need to improve the performance of the queries.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:

Show Suggested Answer

Suggested Answer:

Step 1: Drop the clustered columnstore index
Step 2: Create a clustered rowstore index on ProductID.
Rowstore indexes perform best on queries that seek into the data, searching for a particular value, or for queries on a small range of values. Use rowstore indexes with transactional workloads since they tend to require mostly table seeks instead of table scans.
Step 3: Create a nonclustered index on ProductID
Incorrect Answers:
Not: Create a nonclustered columnstore index on ProductID
Use a nonclustered columnstore index to perform analysis in real-time on an OLTP workload.
References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview

by RohitM at Sept. 23, 2019, 10:22 a.m.

Comments

Submit Cancel

Som2020

4 years, 6 months ago

1) Drop the Columnstore index. 2) Create a rowstore index. 3) Create a clustered column store index with drop existing

upvoted 1 times

Hoglet

4 years, 5 months ago

Why? Step 1 and 3 don't align, in step 1 you drop the Columnstore index, so don't need to "drop existing" option in step 3

upvoted 1 times

...

MelKr

5 years, 1 month ago

According to "https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-query-performance?view=sql-server-ver15" the correct answer would be 1. DROP exististing Column Store 2. Create clustered RowStore index --> orders the table by ProductID as this is not the insert-order of rows and reason for degraded performance 3. create clustered columnstore index with drop existing --> Convert clustered rowstore to columnstore keeping the sort order of productID nonclustered index on productID is not touched at all and beginning with SQL2016 is kept automatically when dropping a clustered index.

upvoted 3 times

Hoglet

4 years, 5 months ago

This will require on-going maintenance, as the new rows won't follow the this ordering. Alternative if keeping the rowstore clustered index on ProductID and then create a non-clustered columnstore index on ProductID. Thought?

upvoted 1 times

...

TheSwedishGuy

5 years, 4 months ago

Since this is a database which requires searches for individual values for different products, it makes sense that the index is row-stored, instead of column stored. So drop the columnstore, then create a clustered rowstore index, followed by nonclustered rowstore index to increase performance. https://logicalread.com/2018/04/05/choosing-rowstore-indexes-columnstore-indexes/#.Xjq3WldKiUk

upvoted 1 times

Hoglet

4 years, 5 months ago

You would create both a clustered and non-clustered rowstore indexes on ProductID? Unless the table is wide and the non-clustered index will significantly reduce the width there is no point in having both

upvoted 1 times

...

mrn0107

5 years, 5 months ago

This has no sence. 1. we should drop clustered index (only one clustered index can be per table), 2. create clustered index on ProductID 3. i do not see logical answer for this

upvoted 1 times

...

TheSwedishGuy

5 years, 5 months ago

1) Drop the Columnstore index. 2) Create a Rowstore index. 3) Create a non clustered index.

upvoted 1 times

...

tomzus

5 years, 7 months ago

Dropping a clustered rowstore index doesn't cause all the non clustered indexes to also be dropped. Don't get why you would create a clustered index on product ID only to then go on and create a non-clustered index on the same column, that would be duplicating the clustered index, requiring more storage and the non-clustered would be less efficient to use.

upvoted 1 times

...

Varad

5 years, 8 months ago

Sorry. I take my words back. I tested just now and found that, dropping Clustered columnstore index didnot drop the NonClustered index, like in normal clustered index.

upvoted 2 times

Hoglet

4 years, 5 months ago

Dropping any type of Clustered Index does NOT "drop" the non-clustered indexes, such that they will need to be re-created. When dropping a rowstore clustered index, the table becomes a "heap". So all the non-clustered indexes are rebuilt to reference the Row IDs (RID) of the Heap, where before they referenced the Clustered Index Key. But you don't have to manually re-create them

upvoted 1 times

...