You are employed as a SQL Server 2012 database developer at ABC.com. You have a stored
procedure that is executed quite often. The stored procedure joins data from two tables.
ABC.com users report that the stored procedure takes a long time to execute. You analyze the
query plan and find that the stored procedure often makes use of table scans rather than indexes
when the estimated rows do not match the actual rows on one of the tables.
How would you optimize the performance of the stored procedure?
A.
You should make use of the KEEPIDENTITY table hint in the stored procedure.
B.
You should make use of the KEEPDEFAULTS table hint in the stored procedure.
C.
You should make use of the IGNORE_CONSTRAINTS table hint in the stored procedure.
D.
You should make use of the FORCESEEK table hint in the stored procedure.
E.
You should update statistics on the tables queried by the stored procedure.
Explanation:
The answer is incorrect
FORCESEEK – restricts how the query optimazer accesses tables
Correct E
You should update statistics on the tables queried by the stored procedure.
http://msdn.microsoft.com/en-us/library/ms187348.aspx
0
0
I thinkd its D. FORCESCAN was introduced to force a scan on an index or table.
http://technet.microsoft.com/en-us/library/bb510478(v=sql.105).aspx
0
0
“when the estimated rows do not match the actual rows on one of the tables”
That’s mean the statistics is not most updated.
0
0
E
0
0
FORCESEEK
Specifies that the query optimizer use only an index seek operation as the access path to the data in the table or view. Starting with SQL Server 2008 R2 SP1, index parameters can also be specified. In that case, the query optimizer considers only index seek operations through the specified index using at least the specified index columns.
there fore D IS ALSO THE ANSWER
0
0
The question says:
[…] use of table scans rather than indexes WHEN the estimated rows DO NOT MATCH the actual rows […]
So in other cases it uses indexes.
So right answer is E – UPDATE STATISTICS.
0
0
Besides, part of the new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0