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?
The stored procedure is performing a clustered index scan, which is inefficient for fetching rows based on the CreationDate column. The OPTIMIZE FOR UNKNOWN query hint helps the SQL Server query optimizer by using average distribution statistics for all parameter values. This can improve performance by allowing the optimizer to generate a more efficient execution plan. Given the options provided, modifying the stored procedure to include the OPTIMIZE FOR UNKNOWN query hint is likely the best approach to ensure the procedure completes as quickly as possible.
None of these will work. The optimizer must be certain that the parameter will be greater than the date specified in the filter. Optimize for unknown uses density vector - doesn't give any assurance. Hint with the date is a hint for cardinality, it also won't use the filtered index. We would need the option recompile hint or change the condition and add a cut-off date that's specified in the filter.
May be OPTION(RECOMPILE) is better but this question only have 4 answers. I think the question is "as quickly as possible" and only 4 options to choose. So A could be the best answer.
Should be "OPTIMIZE FOR @CreationDate UNKNOW"