Search code examples
sqlsql-serverparameter-sniffing

Any benefit in parameterizing the value of GETDATE() / GETUTCDATE()?

Is there any benefit in parameterizing the value of GETDATE()/GETUTCDATE()? The objective is to allow SQL Server to reuse execution plans, example:

SELECT [c].[ID], [c].[Name]
FROM [Customer] AS [c]
WHERE [c].[ActivationDate] > DATEADD(day, CAST(-7.0E0 AS int), GETUTCDATE()) AND [c]. 
[ActivationDate] < DATEADD(day, CAST(-7.0E0 AS int), GETUTCDATE())

VS

exec sp_executesql N'SELECT [c].[ID], [c].[Name]
FROM [Customer] AS [c]
WHERE [c].[ActivationDate] > @__date1_0 AND [c].[ActivationDate] < @__date2_1',N'@__date1_0 datetime2(7),@__date2_1 datetime2(7)',@__date1_0='2024-04-24 01:32:14.4602195',@__date2_1='2024-04-24 01:32:14.4602223'
Solution

  • In the case of embedding GETDATE or GETUTCDATE, there is little difference, because either way the value is sniffed. See this article, which proves that runtime constant functions are affected by parameter sniffing.

    However, if you were to use a local variable rather than a parameter then could be a difference. There may be a benefit, or there may be a downside.

    What will happen here is that if it is not parameterized then the date will be looked at as a black box and parameter sniffing will be disabled, equivalent to using OPTION (OPTIMIZE FOR UNKNOWN). The average distribution/density statistics will be used.

    Whereas if you use a parameter (not a local variable) then the server will sniff the parameter and cache the plan (on first use) based on that parameter.

    Whether that is beneficial or not depends on the distribution of your data. For example, if there is a large skew in the data for or against that period in time then the plan may be significantly different compared to using the average density, due to wildly different estimated cardinality (row-counts).

    An interesting case when this can happen in the other direction is when the statistics has not been updated for the latest data, which you are trying to retrieve, so the server thinks there will be only a small amount of rows, and in actual fact there are a lot of rows. Then using a local variable or OPTIMIZE FOR UNKNOWN can be more beneficial.

    But if your data is mostly uniform, or there is only one good access pattern, then the plan will almost certainly be the same regardless, so it makes little difference.