On one customer's system, one of our database's tables has about 123 million rows. This table is a transaction table that is grouped by a batch key. The clustered index is 4 columns: batch key, transaction version, shift number, transaction number. One batch can have any number of transactions in it, but a query to count by batch shows the maximum number of transactions per batch is 764, and the count drops sharply from that peak.
Some of our queries that join to this transaction table are randomly getting bad query plans that choose to perform parallel index scans on the transaction table. Many of these queries run in separate connections. When this occurs, the parallel scans are causing constant deadlocking on this table.
The query plan shows a very large estimate for the number of rows in a batch-- about 20,000. I already know the maximum per batch is 764, so I was puzzled where this estimate comes from.
DBCC SHOW_STATISTICS ('MyTable', 'MyTableClusteredIndex'). The statistics histogram that came back looks weird, because the EQ_ROWS numbers are incorrect. For example, here are the first 3 entries in the histogram. The EQ_ROWS numbers are very different than the actual number of rows found with a query using the same key (shown in the right-most column):
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS ActualRows
58287 0 1 0 1 121
78045 664341.4 14512.91 1308 507.9888 134
86487 569858.3 14512.91 1208 471.8449 118
94282 574411.7 9070.57 538 1067.721 128
Since this is a production server, I do not want to try to mess with the statistics during business hours.
We have an internal dev server that we use for performance testing. The dev machine has 2 10-core processors on it and is using about 500 GB of memory. On this server, the transaction table has about 1.1 billion rows. The dev server shows a larger disparity between EQ_ROWS and the actual number of rows for each key. For example, some EQ_ROWS numbers are over 60,000, but the actual number of rows for those keys are between 130 to 160 rows.
If I run a manual
UPDATE STATISTICS MyTable on our dev server, the statistics do not change. The EQ_ROWS are still the same incorrect numbers.
So far, I have found the same strange EQ_ROWS behavior on SQL 2014 SP 2 and SQL 2012 SP 3.
Our database servers run with the following trace flags enabled: 1222, 2312, 2453, 4199. I verified that disabling these trace flags globally does not change the behavior when updating statistics.
I verified that the
UPDATE STATISTICS MyTable is not running in parallel. When it runs, its degree of parallelism is 1, so it does not look like parallelism is causing some kind of statistics estimation bug.
Aren't the EQ_ROWS values supposed to be the exact number of rows for that key value? If so, why are they incorrect? Does this look like a SQL Server bug?
(Note that I have ignored parameter sniffing, because this issue looks related to the statistics estimates. The query plan does not look related to parameter sniffing to me except for randomly optimizing the query for one of these keys.)