Statistics update with automatic sampling messes up density vector and histogram

Question

I have a table that contains 22 millions of records.

I have noticed that statistics for a column were way off, even if that column has a constant distribution: in fact, every value is repeated twice.

To help you visualize this scenario, think of a table with check-in and check-out dates (2 distinct records) for every unique id of another table.

These are the statistics with a full scan:

These are the statistics with automatic sampling:

Why this weird behaviour? Is there some rule of thumb to determine a correct sampling rate or is the fullscan necessary? Should I create a statistics update job with fullscan for some statistics? And if that's the case, how do I know which are the statistics that need this kind of treatment?

Additional informations:

• There is no foreign key constraint on that column
• The column is a numeric(14,0) NULL

Answer 1

Why this weird behaviour?

Suppose someone gave you a book of a million integers and told you to make a guess about the overall distribution based on ten sampled numbers. The ten sampled numbers that you receive are 1, 1, 125000, 125000, 250000, 250000, 375000, 375000, 500000, and 500000. One perfectly reasonable guess is that the book contains 500000 unique integers in pairs ranging from 1 to 500000. Another equally valid guess is the book contains five unique integers with each integer present 200000 times. For your data distribution, SQL Server is making an inference about your data based on the sample that's closer to the latter interpretation. The algorithms to build histograms don't work well for all possible sampled data distributions, including one with each unique integer showing up exactly twice. I was able to easily reproduce your described issue in SQL Server 2017.

Is there some rule of thumb to determine a correct sampling rate or is the fullscan necessary?

The only rule of thumb that I'm aware of is that the correct sampling rate is at least equal to the sampling rate that gives you acceptable query or system performance. I've found DBAs to use a 100% sampling rate to avoid that analysis, and it's a perfectly reasonable shortcut to take for tables that aren't huge.

You could consider replacing your table with an updatable view built on two underlying tables. Each underlying table could contain one half of the data. SQL Server will build separate statistics objects for each table and you may get better performance without having to tweak statistics. I cannot say if this is a good approach for your specific scenario.

Should I create a statistics update job with fullscan for some statistics?

That is a common solution to the problem that you're describing. For another option, SQL Server 2016 SP1 CU4 introduces the PERSIST_SAMPLE_PERCENT keyword for STATISTICS commands. You can use it to instruct SQL Server to always sample statistics at a certain rate for this column whenever an auto-stats update is triggered. If async stats updates are disabled then keep in mind that query compilation times may occasionally run long for queries that use your column.

And if that's the case, how do I know which are the statistics that need this kind of treatment?

The usual answer is to monitor your workload for unacceptable query performance, determine which queries have statistics issues, and to fix those issues with statistics changes. If you wish to be proactive you could consider analyzing statistics on a copy of production. First get sampled statistics for all relevant columns on the database and save all of the density vectors to a table. You can use DBCC SHOW_STATISTICS WITH DENSITY_VECTOR to do that. Then update all of the same statistics with FULLSCAN and save those density vectors as well. You can then compare the density vectors to find the statistics objects with the largest differences. This won't find all statistics issues, but it will find those similar to what you observed in this question.

Answer 2

Performing a full scan update of statistics for a column allows SQL Server to understand the exact nature of the column, whereas the default scan looks at a "statistically significant" sample of rows for the table. In your table, the "statistically significant" sample of rows results in an incomplete picture of the data.

For tables with atypical value distributions, full scan stats updates will almost always result in a far more accurate histogram. There is no across-the-board "best" choice; the default sample rate is a helpful balance between stats update performance and query performance for most tables.

Understanding the data contained in a column will allow you to determine if a full scan results in better statistics.

As a rule-of-thumb, I always setup a statistics rescan job that runs nightly or weekly, and barring some requirement to minimize I/O, I setup that job to do FULLSCAN updates of those statistics. The job I create only updates tables that have received "x" number of updates/inserts/deletes, and that have not had a stats update in "x" amount of time. Running a job on a schedule during periods of lower activity will, most of the time, prevent SQL Server running stats updates during periods of high activity.

I wrote a blog post showing how I update statistics via a stored proc in master, and a SQL Server Agent Job: http://www.sqlserver.science/maintenance/statistics-update-job/