parameter-sniffing – Tarik Billa

What are the main differences between OPTION(OPTIMIZE FOR UNKNOWN) and OPTION(RECOMPILE)?

April 7, 2024 by Tarik

Will OPTION(OPTIMIZE FOR UNKNOWN) reuse cache instead of recompiling each time? Yes, it will. There are two main differences between OPTION(OPTIMIZE FOR UNKNOWN) and OPTION(RECOMPILE) as can be seen from this quote from MSDN: OPTIMIZE FOR UNKNOWN Instructs the query optimizer to use statistical data instead of the initial values for all local variables when … Read more

SP taking 15 minutes, but the same query when executed returns results in 1-2 minutes

September 6, 2023 by Tarik

This is the footprint of parameter-sniffing. See here for another discussion about it; SQL poor stored procedure execution plan performance – parameter sniffing There are several possible fixes, including adding WITH RECOMPILE to your stored procedure which works about half the time. The recommended fix for most situations (though it depends on the structure of … Read more