An explanation of the process of creating statistics

Question

During the process of creating automatic statistics, Sql Server internally executes a statement like the following (based on AdventureWorks database)

SELECT StatMan([SC0], [SB0000])
FROM (
    SELECT TOP 100 PERCENT [SC0]
        ,step_direction([SC0]) OVER (
            ORDER BY NULL
            ) AS [SB0000]
    FROM (
        SELECT [OrderDate] AS [SC0]
        FROM [Sales].[SalesOrderHeaderEnlarged] TABLESAMPLE
             SYSTEM(5.485069e+00 PERCENT) WITH (READUNCOMMITTED)
        ) AS _MS_UPDSTATS_TBL_HELPER
    ORDER BY [SC0]
        ,[SB0000]
    ) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 6)

Paul White has already explained here how the percentage value is calculated. I noticed that if I execute repeatidly the most internal statement, the one with TABLESAMPLE, each time it returns a different number of rows, and the reason is that the REPEATABLE clause is not used. As a consequence, dropping and re-creating the statistic several times, I expected to get different statistics, each time, in terms of Rows Sampled and Histogram; instead the statistic is always the same. How can this behaviour be explained?

I noticed the following facts:

1. When an auto-statistic is created a statement like the one posted above is executed and it is based on the most internal query that uses TABLESAMPLE SYSTEM.

2. If you execute that query several times you get always a different set of rows (because REPEATABLE clause is not used).

3. If you drop and re-create the auto-statistic you get always the same result. I suspect that the statement grabbed with Profiler/XE is not the real one that the Optimizer utilizes. If it used TABLESAMPLE without the REPEATABLE clause we should get different statistics each time

Answer 1

I've found the answer here: Strange behaviour with sample sizes for statistics updates

Under the covers, it seems that for sampled UPDATE STATISTICS, the REPEATABLE clause is used and its value is equal to 1, even if it is not shown.