You have database objects that were created by using the following script:
The dbo.Customers table has 1 million rows.
You discover that usp_GetCustomersByDate takes a long time to complete.
The query plan used by the stored procedure is shown in the exhibit. (Click the Exhibit button.)
You need to ensure that usp_GetCustomersByDate completes as quickly as possible.
What should you do?
A.
Modify the stored procedure to include the OPTIMIZEFOR UNKNOWN query hint.
B.
Execute the sp_recompile ‘dbo.GetCustomersByDate’ statement.
C.
Execute the ALTER INDEXIX_Cust:omers_CreationDateWITH REBUILD statement.
D.
Modify the stored procedure to include the OPTIMIZE FOR(‘1/1/2008’) query hint.
Maybe OPTIMIZE FOR(‘1/1/2008’)?
The correct answer is option D: Optimize for (‘1/1/2008’).
The date literal value is assigned to the @CreationDate variable, and only used for compilation and building an execution plan during query optimization, but not during query execution.
Reference: https://msdn.microsoft.com/en-us/library/ms181714%28v=sql.110%29.aspx
Agreed with option D.
And what to do if someone queries for 2007 ?
The hint would propagate to use the index which has no information for this time…
The Query Analyzer doesn’t use the HINT during execution; it only uses it for ”cardinality estimation” and optimization prior to query execution.
Hello,
A.Modify the stored procedure to include the OPTIMIZEFOR UNKNOWN query hint.
I would go for OPTIMIZE FOR UNKNOWN because if you use D OPTIMIZE FOR(‘1/1/2008’) you will generate a new query plan that still may not be appropriate for other parameter values.
Reference :
https://blogs.msdn.microsoft.com/sqlprogrammability/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature/
a+,=)
-=Clement=-