You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
You discover that the root cause is a query against a frequently updated table that has a clustered
index. The query returns four columns: three columns in its WHERE clause contained in a nonclustered index and one additional column.
You need to optimize the statement.
What should you do?
A.
Add a HASH hint lo 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 FORCESCAN hint to the Attach query.
G.
Add a columnstore index to cover the query.
H.
Enable the optimize for ad hoc workloads option.
I.
Cover the unique clustered index with a columnstore index. Include a SET FORCEPLAN ON
statement before you run the query.
J.
Include a SET STATISTICS PROFILE ON 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 ISOLATION LEVEL SNAPSHOT statement before you run the query.
N.
Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
Usually posts some extremely intriguing stuff like this. If youre new to this site.
ANSWER IS G
G
As far as I know Columnstore indexes are supposed to be used in Data Warehause envs, not in OLTP (frequently updated table)
I think that the correct answer is D – “Include”
https://msdn.microsoft.com/en-us/library/ms190806.aspx
Answer is C. Because it says You discover that the root cause is a query against a frequently updated table that has a clustered index. There is a different question where it does not say that and the answer is G.