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.
I simulated the situation.
If I place a select statement outside of a procedure and use a date before the index start, the clustered scan is used.
If I use a date after IndexStart, the IndexSeek is made as desired
In the procedure I did not manage the optimizer to use the index seek.
I checked:
…Option (Optimize for (‘2008-01-01’));
-> Syntax Error
…Option (Optimize for ‘2008-01-01’);
-> Syntax Error
…Option (Optimize for (@CreationDate = ‘2008-01-01’));
-> Procedure Created but exec used Clustered Scan
…Option (Optimize for (@CreationDate Unknown));
-> Procedure Created but exec used Clustered Scan
…Option (Optimize for Unknown);
-> Procedure Created but used exec Clustered Scan
@mickeyW, the scan operator may depend on your data distribution.
If you specify the OPTIMIZE FOR UNKNOWN hist, the optimizer will use the “all density” value in the statistics to estimate the number of rows.
You can view it using DBCC SHOW_STATISTICS command.
It works fine and produce the same plan regardless the parameter value passed to the SP.
However, this is not the *best* plan, this is a “generic”plan that is optimal for some cases and not for other cases.
The best option would be the RECOMPILE hint but this is not an option in the question.
Regards
Alberto