You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and
SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Refer to the Exhibit.)
You write the following Transact-SQL query:
You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the
estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on
SalesOrderDetail. You need to improve the performance of the query. What should you do?
A.
Use a FORCESCAN hint in the query.
B.
Add a clustered index on SalesOrderId in SalesOrderHeader.
C.
Use a FORCESEEK hint in the query.
D.
Update statistics on SalesOrderId on both tables.
Explanation:
References: http://msdn.microsoft.com/en-us/library/ms187348.aspx
I think B is correct. Obviously there ist no primary key in either of the two tables and therefore no clustered index yet. An index on SalesOrderId is very worthwhile because it is used in the join.
No : I think D is correct . Table Have an index. You update statistics than query is fast
its D, in the question says “Analysis of the query plan shows table scans where the
estimated rows do not match the actual ” that’s because statistics are not updated the sql server may chose a different execution plan…
I think C is correct, Update Statistics This default, he query optimizer and updates statistics as a requirement,and Forceseek forcing an index seek operation may yield better query performance. This is especially true when inaccurate cardinality or cost estimations cause the optimizer to favor scan operations at plan compilation time
reference:
https://technet.microsoft.com/en-us/library/bb510478%28v=sql.105%29.aspx
Answer is D.
Reference: https://technet.microsoft.com/en-us/library/bb510478%28v=sql.105%29.aspx
The best practice is before using the FORCESEEK table hint, make sure that statistics on the database are current and accurate.
Up-to-date statistics allow the optimizer to accurately assess the cost of different query plans, and choose a high-quality plan. Therefore, we recommend setting the AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS to ON (the default) for every user database. Alternatively, you can manually update statistics on a table or view by using the UPDATE STATISTICS statement.