We knew something was wrong when queries at various times of the day started taking minutes instead of milliseconds on an SQL Server database. The question was, what?
About The Table:
- Over 1000 Partitions, partitioned on a date value
- Data is compressed using page compression
- Every business day, between 600 million to 1.1 Billion records added
- Several thousand records share the same time value, indicating a batch
- A new batches is added between .8 and 1.5 seconds
- Batches have about 17,000 records.
- The table has about 940 Billion (with a B) records.
The Statistics Issue:
When I investigated the issue, I made a discovery about the distribution statistics for the clustered index (which was also the partition key) on the table. When looking at the histogram for the clustered index, we saw some steps that covered less than a day and some that covered weeks with widely different range_row values. I was surprised that with over 800 billion records at the time and 1,000 partitions, I still had less than 200 steps in the histogram. I contacted Microsoft about this and didn’t get any useful information. Their recommendation was to “Upgrade to the latest version of SQL Server”.
Doing an update statistics did not resolve the issue. We solved this by forcing a larger sample size than the default size it used. Once we increased the sample size (but it is still tiny relative to the size of the table) the histogram made a lot more sense and the range_rows value for each step made more sense.
If you want a better understanding of distribution statistics and how they can skew I would recommend this post from Kimberlly Tripp.
The Query:
Select max(SnapDate) from TheBigTable where SnapDate between @ValueA and @valueB WITH RECOMPILEThe query returns the greatest time slice within a 5 to 15-second time period. The query is called thousands of times an hour with different ranges for SnapDate (but the size of the range is never more than 10 minutes) and most of the time slices are within the last hour (so not more than records an hour old)
The query performs as expected when the time slice being searched is for values that occurred before the last statistics update; if the time window is after the last statistics update, the performance is significantly slower. There were some cases where it would take minutes vs well under .10 of a second when using a time window preceding the last statistics update.
We tried various things to solve the problem, such as
- Doing a top 1 with and order by
- Using > and < instead of between
- Using RECOMPILE to reduce the risk of a stale plan (we thought that was the issue for a while)
There is a scheduled job that updates statistics with our forced sample size every 2 hours to try to reduce the time windows when we might see the issue.
What I eventually figured out was happening.
When taking a deeper dive into the query plans for the slow vs the fast queries and looking at the properties of the index seeks, we found that for queries that had a time covered by the statistics it was doing a REVERSE scan on the clustered index fand a FORWARD scan on the clustered index when the statistics did not cover the values. As a result, when doing the FORWARD scan we were doing a large number of logical reads vs. when doing the REVERSE scan.
Fixing The Issue:
It became obvious that when the plan called for a REVERSE seek on the it ran quickly, a FORWARD seek was slower.
How To Force A REVERSE Seek?
I was unable to find a query hint that did that. Even if I had, I suspect I would have been so deep in the weeds that I would be looking at significant risks of unintended consequences.
Re-writing the query could force a REVERSE by specifying a descending sort order. I found that would then force a REVERSE scan of the correct index.
Select TOP SnapDate from TheBigTable where SnapDate between @ValueA and @valueB
ORDER BY SnapDate DESC WITH RECOMPILEBecause of the ordering of the underlying index doesn’t even have to do a full REVERSE scan to get the value. It understands that it has gotten what it needs upon reading of the first relevant page.
