You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
Your investigation shows the root cause is a query against a read-only table that has a
clustered index.
The query returns the following six columns:
• One column in its WHERE clause contained in a non-clustered index •
• Four additional columns
• One COUNT (*) column based on a grouping of the four additional columns 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 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.
Wrong i think cause it asks for optimization and forceseek seems the best option
0
0
does any one knows the right answer?
no idea of how Add a columnstore index work. but It does not seem right.
Like jake said, it wld b forceseek.
0
0
The FORCESEEK hint prompts the Query Optimizer to make use of index seek as opposed to performing a full table scan. BUT, the clustered-index column is not included in the WHERE predicate; hence, the slow-running query. ALSO, the four additional columns in the result set have no index defined on them.
SQL Server 2012 has a method for storing nonclustered indexes; in addition to regular row storage, SQL Server 2012 can store index data ‘column by column’ in what’s called a columnstore index. This way, a columnstore index spreads over (covers) more than one search column.
Columnstore indexes can speed up data warehousing queries (very large, read-only tables), by a large factor of x10 (even, x100).
A columnstore index is a form of nonclustered index encompassing multiple columns of a table. It greatly improves batch processing.
The SQL Server Query Optimizer considers using columnstore index during the query optimization phase just as it does any other index.
1
0
Add A Columnstore Index To Cover The Query
0
0
Besides, part of new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
1
0