0

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

3
  • hm so you get and expect different numbers? can you eleborate your question with soe numbers of waht you get and what you expected. Commented Nov 17, 2024 at 19:04
  • 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 Commented Nov 20, 2024 at 5:40
  • 1
    please add your information in your question, so it is hard to read Commented Nov 20, 2024 at 15:19

1 Answer 1

0

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.