![sql server datediff sql server datediff](https://www.rodolforodarte.com/wp-content/uploads/2011/05/DateDIff1.png)
This is clearly not right it seems SQL Server has guessed that the variable would match 50% of the rows in the table. Top Operations tab for query that uses a variable
![sql server datediff sql server datediff](https://i.stack.imgur.com/P8gXo.png)
The problem with this approach is that, with a variable, you are going to end up with a stable plan, but the cardinality is going to be based on a guess (and the type of guess will depend on the presence or absence of statistics).
![sql server datediff sql server datediff](https://csharpcode.org/wp-content/uploads/2015/08/date.png)
But if you were to use dynamic SQL, I would do it this way instead of the way they recommend in the KB article, most importantly to minimize SQL injection risks:ĭECLARE DATE = DATEADD ( MONTH, DATEDIFF ( MONTH, 0, GETDATE ( ) ), 0 ) SELECT COUNT ( * ) FROM dbo. Recommending dynamic SQL here is unnecessarily complex and probably overkill, given that a different expression could solve the problem. In my tests, using a different expression (such as the one above that doesn't use DATEDIFF) overcame the issue in modern builds of both SQL Server 2008 and SQL Server 2012. It suggests you use dynamic SQL to work around the issue.
#Sql server datediff plus
On the plus side, running the above queries with the trace flag on (using OPTION (QUERYTRACEON 4199)) yields plans that do not have the incorrect estimate issue. Neither the KB article nor the Connect item give any insight into the cause (that the arguments to DATEDIFF have been swapped during evaluation). The fact that this trace flag is required for the fix is mentioned in a related Connect item, #630583, but this information hasn't made it back to the KB article. It neglects to mention that, in order to benefit from the fix, you need to turn on trace flag 4199 (and "benefit" from all of the other ways that specific trace flag can affect the optimizer).I can still reproduce this issue on SQL Server 2008 SP3 CU #8 () and SQL Server 2012 SP1 CU #5 (). However, the symptom is still present in branches that aren't explicitly mentioned there, even though they have seen many additional CUs since the article was published. The KB article claims that the issue has been fixed in various service packs or cumulative updates for SQL Server 2005, 20 R2.There is a knowledge base article (KB #2481274) that claims to address the problem, but it has a few problems of its own: This appears to involve constant folding, at least peripherally there are a lot more details on constant folding in this Books Online article but, unfortunately, the article does not reveal any information about this particular bug. To put it simply, SQL Server has a DATEDIFF bug where it swaps the second and third arguments when evaluating the expression for cardinality estimation. That said, if you are deriving constants using DATEDIFF within your queries this way, you really should test this impact in your environment. And it is important to note that the queries themselves still return correct data it is just that the estimates that are wrong (and could lead to a worse plan than I've demonstrated here). On this particular data size and query, the net performance impact (most notably duration and reads) is largely irrelevant. Top Operations tab showing accurate estimates
![sql server datediff sql server datediff](https://www.essentialsql.com/wp-content/uploads/2015/08/DateTimeFunctions.png)
When you look at the Top Operations tab here, though, you see that the estimate is bang on: The plan is very similar to query 1 above, and if you didn't look closer you would think these plans are equivalent: DateTest WHERE CreateDate = CONVERT ( DATE, DATEADD ( DAY, 1 - DAY ( GETDATE ( ) ), GETDATE ( ) ) ) SELECT /* Query 3 */ COUNT ( * ) FROM dbo. Top Operations tab for second query Ī slightly different variation of the query, using a different expression to calculate the beginning of the month (alluded to at the beginning of the post), does not exhibit this symptom: Based on incorrect cardinality estimates like this, I'm sure you can imagine what kind of drastic effect this could have on more complex queries against much larger data sets. Graphical plan for DATEDIFF(MONTH, GETDATE(), 0) queryīut the estimated costs are out of whack – note how much higher the estimated costs are for the first query, which only returns 50 rows, compared to the second query, which returns 15,000 rows!Īnd the Top Operations tab shows that the first query (looking for ) estimated that it would find 15,000 rows, when in actuality it only found 50 the second query shows the opposite: it expected to find 50 rows matching, but found 15,000. Graphical plan for DATEDIFF(MONTH, 0, GETDATE()) query DateTest WHERE CreateDate = DATEADD ( MONTH, DATEDIFF ( MONTH, GETDATE ( ), 0 ), 0 ) DateTest WHERE CreateDate = DATEADD ( MONTH, DATEDIFF ( MONTH, 0, GETDATE ( ) ), 0 ) SELECT /* Query 2 */ COUNT ( * ) FROM dbo. SELECT /* Query 1 */ COUNT ( * ) FROM dbo.