Essentially, the problem is that a poor estimate can be made not simply when
SYSUTCDATETIME()) appears, as Erland originally reported, but when any
datetime2expression is involved in the predicate (and perhaps only when
DATEADD()is also used). And it can go both ways – if we swap
<=, the estimate becomes the whole table, so it seems that the optimizer is looking at the
SYSDATETIME()value as a constant, and completely ignoring any operations like
DATEADD()that are performed against it.
Paul shared that the workaround is simply to use a
datetimeequivalent when calculating the date, before converting it to the proper data type. In this case, we can swap out
SYSUTCDATETIME()and change it to
I suppose switching to GETUTCDATE isn’t too much of a loss, but it looks like (according to Paul White in the second linked Connect item) this appears to have been fixed in SQL Server 2014.
The CROSS APPLY and the old-school solutions are by far the best choice for dense indexes, i.e. when the first column has a low degree of uniqueness. The old-school solution is only that fast because the optimizer short-circuits the query plan.
LEAD() and the old school strategy are best for selective indexes, i.e. when the first column is highly unique.
There’s a nice set of options available so if one doesn’t work well with your particular data set, try out some of the others and see if they work for you.
WRITELOG waits are a scalability challenge for OLTP workloads under load. Chris Adkin has a lot of experience tuning SQL Server for high-volume OLTP workloads. So I’m going to follow his advice when he writes we should minimize the amount logging generated. And because I can’t improve something if I can’t measure it, I wonder what’s generating the most logging? OLTP workloads are characterized by frequent tiny transactions so I want to measure that activity without filters, but I want to have as little impact to the system as I can. That’s my challenge.
Check out the entire post, as this is a good exercise in investigating busy transactional systems.
Extended Event (XEvent) feature is available as public preview in Azure SQL Database as announcedhere. XEvent supports 3 types of targets – File Target (writes to Azure Blob Storage), Ring Buffer and Event Counter. Once we’ve created an event session, how do we inspect the event session target properties? This blog post describes how to do this in 2 ways: using the User Interface in SSMS and using T-SQL.
It’s nice to see Extended Events making their way into Azure SQL Database.