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 TABLESAMLE, 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 behavior be explained?

Improve this question
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.
    – nbk
    Commented Nov 17 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
    – Alessandro Mortola
    Commented Nov 20 at 5:40
  • please add your information in your question, so it is hard to read
    – nbk
    Commented Nov 20 at 15:19

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.