Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 72 discussion

Actual exam question from Microsoft's 70-762

Question #: 72
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 are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.
Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the
Microsoft SQL Server instance and observe several high values that include a disk performance issue. You examine wait statistics and observe an unusually high
WRITELOG value.
You need to improve the application response time.
Solution: You add a unique clustered index to the Employees table.
Does the solution meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: A 🗳️
References:
https://msdn.microsoft.com/en-us/library/ms190457.aspx

by AshleyLiang at Sept. 30, 2019, 9:53 a.m.

Comments

Submit Cancel

Oooo

Highly Voted 4 years, 8 months ago

I don't even see a point of debating on this. option says you are adding an unique clustered index means, there is no existing index and this is a heap table. There are thousands of rows being inserted so its obviously going to be fastest on a heap table and adding a clustered index is going to decrease the performance so let it be writelog or whatever. Answer is NO.

upvoted 9 times

...

Vijayglobal

Highly Voted 5 years, 1 month ago

Found this if it helps. I am going with "No". "https://use-the-index-luke.com/sql/dml/insert"

upvoted 8 times

MML

4 years, 11 months ago

I agree

upvoted 2 times

...

JawwadAK

Most Recent 4 years, 7 months ago

Hey guys, Ans is right because here is two points which should be in mind, 1st is we assumed that Clustered index is created but not unique clustered index so we created that. second is we need to insert in bulk through Stored proc, so in this case Unique Clustered Index will be helpful coz all the work on going to be done on the fly.. so and is right

upvoted 2 times

...

SoupDJ

4 years, 7 months ago

No question that creating a clustered index slows writing to disk and speeds up (hopefully) reading - but I have a different take. WRITELOG refers to the waiting time to write to the transaction log - which is something that occurs BEFORE data cache is written to disk. Granted, writing data cache to disk will require more time with the index is clustered - but that doesn't impact the step before, which has to do with reducing the time to write to the transaction log. I vote NO simply because adding a clustered index won't have any impact (at all) on the question of the speed of writing to the transaction log.

upvoted 4 times

...

Mosufe

4 years, 9 months ago

Give a look at this: https://www.sqlshack.com/how-to-handle-the-sql-server-writelog-wait-type/

upvoted 1 times

...

melvin9900

4 years, 10 months ago

Answer is NO : reference : https://www.mssqltips.com/sqlservertip/1185/minimally-logging-bulk-load-inserts-into-sql-server/

upvoted 4 times

...

gmu

4 years, 10 months ago

Remember that the issue is related to Writelog and not about time. I think that the answer should be A. Maybe this case is useful https://www.mssqltips.com/sqlservertip/4961/sql-server-insert-performance-for-clustered-indexes-vs-heap-tables/

upvoted 1 times

...

stm22

4 years, 11 months ago

Solution: You add a unique clustered index to the Employees table. i think the table was a heap because if you can add a unique clustered index, then there was no previous clustered index. i vote Yes.

upvoted 3 times

...

lukadataowner

5 years, 2 months ago

No: i suppose the table already has a clustered pk index

upvoted 1 times

...

JohnFan

5 years, 2 months ago

using a clustered index gave us better performance in every category that we measured, CPU, IO and total duration. The only side effect of using a clustered index was that it did use a little bit more space. This space could also be reclaimed by rebuilding the index during a maintenance window (or online if you are running Enterprise Edition). In any case, before implementing any index changes in production you should always test in your own environment to ensure you also see a positive improvement in performance. https://www.mssqltips.com/sqlservertip/4961/sql-server-insert-performance-for-clustered-indexes-vs-heap-tables/

upvoted 1 times

...

JohnFan

5 years, 3 months ago

I assume this is clustered columnstore index?

upvoted 1 times

JohnFan

5 years, 2 months ago

Adding a new row to a table involves several steps. First, the database must find a place to store the row. For a regular heap table—which has no particular row order—the database can take any table block that has enough free space. This is a very simple and quick process, mostly executed in main memory. All the database has to do afterwards is to add the new entry to the respective data block.

upvoted 1 times

JohnFan

5 years, 2 months ago

If there are indexes on the table, the database must make sure the new entry is also found via these indexes. For this reason it has to add the new entry to each and every index on that table. The number of indexes is therefore a multiplier for the cost of an insert statement. https://use-the-index-luke.com/sql/dml/insert When you insert a row into an allpages-locked table with a clustered index, the data row must be placed in physical order according to the key value on the table. Other rows on the data page move down on the page, as needed, to make room for the new value. As long as there is room for the new row on the page, the insert does not affect any other pages in the database. The clustered index is used to find the location for the new row. http://infocenter-archive.sybase.com/help/index.jsp?topic=/com.sybase.dc20021_1251/html/locking/locking174.htm

upvoted 1 times

JohnFan

5 years, 2 months ago

SQL Server Insert Performance for Clustered Indexes vs. Heap Tables (with NonClustered Indexes) https://www.mssqltips.com/sqlservertip/4961/sql-server-insert-performance-for-clustered-indexes-vs-heap-tables , https://www.mssqltips.com/sqlservertip/4961/sql-server-insert-performance-for-clustered-indexes-vs-heap-tables/ does it help to use an ORDER BY clause in the SELECT statement for the clustered index column, or would any gain acheived be negated by the extra sort required for the ORDER BY clause? https://dba.stackexchange.com/questions/7350/efficient-insert-into-a-table-with-clustered-index

upvoted 1 times

...

gtc108

5 years, 3 months ago

The answer should be B: NO because clustered index sorts the data which takes time to INSERT, UPDATE, and DELETE.

upvoted 5 times

...

AshleyLiang

5 years, 8 months ago

Does the clustered index has anything to do with insert operations?

upvoted 1 times

imran

5 years, 6 months ago

https://www.mssqltips.com/sqlservertip/4961/sql-server-insert-performance-for-clustered-indexes-vs-heap-tables/

upvoted 2 times

Bartek

5 years, 3 months ago

Hmm, I am not sure, I would wonder in answer "No" becouse Your article is referencing to inserts into heaps with noncludtered indexes, not directly to heaps.

upvoted 3 times

JohnFan

5 years, 2 months ago

when you have a clustered index, a bulk insert operation is really fast only when you insert data sorted with the same order as the clustered index expression. https://www.sqlbi.com/blog/marco/2007/07/02/the-clustered-index-the-bulk-insert-and-the-sort-operation/

upvoted 2 times

...