Exam 70-461 topic 1 question 135 discussion

Actual exam question from Microsoft's 70-461

Question #: 135
Topic #: 1

You have a database application that uses Microsoft SQL Server 2012. You have a query named Query1 that returns four columns from a frequently updated table that has a clustered index. Three of the columns are referenced in the WHERE clause of the query. The three columns are part of a non-clustered index. The fourth column is not referenced in the WHERE clause.
Users report that the application begins to run slowly. You determine that the root cause for the performance issue is Query1.
You need to optimize the statement.
What should you do?

A. Add a HASH hint to the query.
B. Add a LOOP hint to the query.
C. Add a FORCESEEK hint to the query.
D. Add an INCLUDE clause to the index.
E. Add a FORCESCAN hint to the Attach query.
F. Add a columnstore index to cover the query.
G. Enable the optimize for ad hoc workloads option.
H. Cover the unique clustered index with a columnstore index.
I. Include a SET FORCEPLAN ON statement before you run the query.
J. Include a SET STATISTICS PROFILEON statement before you run the query. K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query. L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query. M. Include a SET TRANSACTION ISOLATIONLEVEL SNAPSHOT statement before you run the query. N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Show Suggested Answer

Suggested Answer: K 🗳️
SET SHOWPLAN_XML (Transact-SQL) causes SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document
Incorrect Answers:
F: Columnstore indexes in the SQL Server Database Engine can be used to significantly speed-up the processing time of common data warehousing queries.
Typical data warehousing workloads involve summarizing large amounts of data. But in this question the query is run on a table that is updated frequently, not a warehousing table.
Reference: SET SHOWPLAN_XML(Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms187757.aspx

by foqa at Dec. 31, 2019, 11:31 a.m.

Comments

Submit Cancel

Sugar

5 years, 1 month ago

could be D or G

upvoted 1 times

...

foqa

5 years, 5 months ago

I would consider D. Including 4th column in non-clustered index. The clustered index wouldn't have to be sought at all.

upvoted 3 times

...