What is the difference between OPTIMIZE FOR (@variable = value OR @variavel UNKOWN)

[Seratti]

I was reading this article from MSDN but I didn't found the difference between using:

OPTION(OPTIMIZE FOR (@dtini UNKNOWN, @dtfim UNKNOWN))

OR

OPTION(OPTIMIZE FOR (@dtini = '2023-01-06', @dtfim = '2023-01-06'))

How SQL gonna works in each case?

In some cases my range of data will return 1kk and other time will return 200 rows. Not always my first execution plan will be the best for differents parameters. Now my problem is with high CPU.

[#2]

OPTION(OPTIMIZE FOR (@dtini UNKNOWN, @dtfim UNKNOWN)) tells the query optimizer to optimize the query plan for unknown parameter values, meaning it will try to generate a query plan that will perform well for any parameter value that is passed in. This can be useful when the query is expected to be run with a variety of different parameter values. On the other hand, OPTION(OPTIMIZE FOR (@dtini = '2023-01-06', @dtfim = '2023-01-06')) specifies specific parameter values to the query optimizer. This tells the optimizer to generate a query plan specifically optimized for the values '2023-01-06' that are passed in for @dtini and @dtfim. This can be useful when you know the specific parameter values that will be used for the query, and you want to ensure that the query is optimized for those specific values.