Execution Plan Behavior with DateTimeOffset Function
I'm trying to figure out the best way to query date ranges in SQL Server when working with DateTimeoffset fields.
My request
I would think (if anything) that the first request would take a little more effort since there must be an implicit conversion from String to DateTimeOffset.
But in the query plan the actual one shows a slightly different story
and it looks like the problem is the estimated row count?
So, all things being equal, why does using the datetimeoffset parameter rather than strings incorrectly estimate the number of rows returned and therefore take orders of magnitude more resources to execute? shows the cost of the request is 96% of the batch? Is the query cost just an estimate, even if it is the actual query plan?
The statistics are accurate and up-to-date and I cleared the proc cache before running the query
source to share
The literal uses histogram statistics because the value is known at compile time. The compile-time value is unknown with a local variable, so average density statistics are used instead, resulting in different row count estimates.
You can either add a query hint OPTION RECOMPILE
to sniff the value of a runtime variable, or you can use a parameterized query. These methods will use a histogram for row count estimates. The latter will also cache the plan so that subsequent executions will use the same plan regardless of the value if the plan remains in the cache.
This is general behavior and does not apply to the datetimeoffset data type.
source to share