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 ind

ex. 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 t

hat 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 yo

u 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.

Explanation:

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 Se

rver 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 update

d frequently, not a warehousing table.

Reference: SET SHOWPLAN_XML(Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms187757.aspx