7

I have a statistic on a Primary Key column in a table. When I update the statistic with the default options:

UPDATE STATISTICS dbo.MyTable PK__MyTable__CB394B3946083350

I get a histogram as follows (abridged)

                    RANGE_HI_KEY                      RANGE_ROWS                         EQ_ROWS             DISTINCT_RANGE_ROWS                  AVG_RANGE_ROWS
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
                      3400002201                               0                               1                               0                               1
                      3400009992                        18103.04                               1                            7790                        2.323882
                      3400040033                        26083.68                               1                           26080                        1.000144
                      3400050456                        13029.09                               1                           10422                        1.250153
                      3400087676                        26083.68                               1                           26080                        1.000144
                      3400103858                        19556.38                               1                           16181                        1.208602
                      3400126866                        13029.09                               1                           13029                               1
                      3400162832                        39138.27                               1                           35965                        1.088232
                      3400213115                        45665.56                               1                           45641                        1.000547
                      3400238444                        26083.68                               1                           25328                        1.029836
                      3400242626                        13029.09                               1                            4181                        3.116262
                      3400262174                        19556.38                               1                           19547                         1.00048
                      3400283983                        26083.68                               1                           21808                         1.19606
                      3400304837                        19556.38                               1                           19556                               1
                      3400316046                        13029.09                               1                           11208                        1.162481
                      3400346666                        13029.09                               1                           13029                               1
                      3400368443                        19556.38                               1                           19556                               1
                      3400385634                        26083.68                               1                           17190                        1.517375
                      3400390548                        13029.09                               1                            4913                        2.651962
                      3400398297                        13029.09                               1                            7748                        1.681607
                      3400417467                        13029.09                               1                           13029                               1
                      3400428728                        13029.09                               1                           11260                        1.157113
                      3400462206                        32610.97                               1                           32600                        1.000332
                      3400477978                        13029.09                               1                           13029                               1
                      3400492969                        19556.38                               1                           14990                        1.304629
                      3400507579                        13029.09                               1                           13029                               1
                      3400529627                        32610.97                               1                           22047                        1.479157
                      3400535909                        13029.09                               1                            6281                        2.074366
                      3400556632                        26083.68                               1                           20722                        1.258743
                      3400576037                        19556.38                               1                           19404                        1.007853
                      3400588565                        19556.38                               1                           12527                        1.561139
                      3400630507                        39138.27                               1                           39120                        1.000457
                      3400655236                        19556.38                               1                           19556                               1
                      3400670940                        19556.38                               1                           15703                        1.245392
                      3400691760                        19556.38                               1                           19556                               1
                      3400701959                        19556.38                               1                           10198                        1.917668
                      3400718913                        19556.38                               1                           16953                        1.153565
                      3400745176                        19556.38                               1                           19556                               1

If we look at the Hi Key 3400009992, the histogram tells us:

There is one row equal to this value There are 18,103 rows where the value > 3400002201 and < 3400009992, however, of these 18,103, only 7,790 are distinct.

How can this be? A primary key must be unique

If I update the statistic with FULLSCAN, I get the histogram below (complete) which seems to accurately represent the data

                RANGE_HI_KEY                      RANGE_ROWS                         EQ_ROWS             DISTINCT_RANGE_ROWS                  AVG_RANGE_ROWS
----------------------------------------------------------------------------------------------------------------------------------------------------------------
                      3400000000                               0                               1                               0                               1
                      3401474769                         1474758                               1                         1474758                               1
                    600004687218                           16383                               1                           16383                               1
                    600005089447                           65535                               1                           65535                               1
                    600005665352                           98303                               1                           98303                               1
                    600006294532                           81919                               1                           81919                               1
                    600008729190                          294911                               1                          294911                               1
                    600012125564                          425983                               1                          425983                               1
                    600014952842                          376831                               1                          376831                               1
                    600017609236                          344063                               1                          344063                               1
                    600017776836                           24575                               1                           24575                               1
                    600022385710                          598015                               1                          598015                               1
                    600022698873                           38234                               1                           38234                               1
                    600022698878                               0                               1                               0                               1

Why doesn't SQL server's sampled histogram represent the uniqueness of the primary key?

0

1 Answer 1

6

This is partially explained in KB3202425 - Improvement: Improves the query performance for SQL Server 2016 by changing the use of histograms on UNIQUE columns:

Improvement

In the current cardinality estimation model of Microsoft SQL Server 2016, uniqueness isn't explained when histograms are scaled. This may cause unique columns having a frequency that's greater than 1. The method for dealing with this in RTM is to ignore the histogram completely on UNIQUE columns. This may cause poor estimates when the distribution of the column is non-uniform.

After this update is installed, the histogram will be used properly if it has all step values within 10% of unique.

Notes

This change will be enabled only if trace flag 4199 is enabled, if the database scope of database configuration QUERY_OPTIMIZER_HOTFIXES is set to ON, or if query hint ENABLE_QUERY_OPTIMIZER_HOTFIXES is used.

The ENABLE_QUERY_OPTIMIZER_HOTFIXES query hint is available starting in SQL Server 2016 SP1.

As so often with modern KBs, you have to imagine the meaning they're really trying to convey:

uniqueness isn't explained when histograms are scaled

One could understand that to mean sampled statistic histograms (which are the only kind needing to be 'scaled') don't reflect any uniqueness constraints, which is the heart of your question.


An interesting difference occurs if you set the database compatibility level to 120 or earlier. This obviously affects more than just statistics sampling. For example, on SQL Server 2019 CU 19 using the 2010 Stack Overflow sample database:

USE StackOverflow2010;
GO
ALTER DATABASE StackOverflow2010 SET COMPATIBILITY_LEVEL = 150;
GO
UPDATE STATISTICS dbo.Posts (PK_Posts__Id);
GO
SELECT TOP (5) 
    H.step_number,
    H.range_high_key,
    H.range_rows,
    H.equal_rows,
    H.distinct_range_rows,
    H.average_range_rows
FROM sys.tables AS T
JOIN sys.schemas AS SCH
    ON SCH.[schema_id] = T.[schema_id]
JOIN sys.indexes AS I
    ON I.[object_id] = T.[object_id]
JOIN sys.stats AS S
    ON S.[object_id] = T.[object_id]
    AND S.stats_id = I.index_id
CROSS APPLY sys.dm_db_stats_histogram
(
    S.[object_id],
    S.stats_id
) AS H
WHERE
    SCH.[name] = N'dbo'
    AND T.[name] = N'Posts'
    AND I.[type_desc] = N'CLUSTERED'
ORDER BY
    H.step_number ASC;
step_number range_high_key range_rows equal_rows distinct_range_rows average_range_rows
1 467 0 1 0 1
2 471 111.9387 1 3 37.31289
3 594 167.908 1 122 1.376295
4 5071 1735.05 1 1735 1
5 11035 3078.314 1 3078 1

Replacing compatibility level 150 in the above script with 120:

step_number range_high_key range_rows equal_rows distinct_range_rows average_range_rows
1 467 0 1 0 1
2 51352 25401.67 1 25402 1
3 115718 42914.28 1 42888 1.000605
4 141859 14267.46 1 14267 1
5 161001 14267.46 1 14267 1

Notice range_rows and distinct_range_rows are much closer to being equal.

On reflection, that's probably because CL 120 didn't allow parallel statistics updates, but I'll leave the example up anyway. You can see similar differences under CL150 if you mess around with the optional MAXDOP clause for UPDATE STATISTICS.


As I say right at the start, this is a partial explanation. Only someone with access to the source code and internal design documentation could tell you exactly how and why things have changed.

Presumably, this is all part of the ongoing work to make selectivity estimation more consistent and easier to maintain. I think it's fair to say it hasn't been an unmitigated success story so far.

Related Q & A:

2
  • Hi @Paul White Thanks for your reply. If I am understanding correctly, this KB doesn't affect the histogram itself but the way the optimizer uses the histogram? I have tried the UPDATE STATISTICS without the FULLSCAN on compat 110,130,150 and the histogram remains the same.
    – SE1986
    Mar 23 at 13:53
  • 2
    @SE1986 I added some more words and an example. As I say right at the start, this is a partial explanation. Only someone with access to the source code and internal design documentation could tell you exactly how and why things have changed.
    – Paul White
    Mar 23 at 15:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.