Revisions to How does sampling work when updating statistics?

updated linked

Source Link

edited Jun 20, 2021 at 12:54

70.5k
22
177
321

For WITH SAMPLE 50 PERCENT it works as though for each data page in the table SQL Server flips a coin. If it lands heads then it reads all the rows on the page. If it lands tails then it reads none.

Tracing the UPDATE STATISTICS T WITH SAMPLE 50 PERCENT call in Profiler shows the following query is emitted

SELECT StatMan([SC0], [SB0000])
FROM   (SELECT TOP 100 PERCENT [SC0],
                               step_direction([SC0]) OVER (ORDER BY NULL) AS [SB0000]
        FROM   (SELECT [C] AS [SC0]
                FROM   [dbo].[T] TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) 
                WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
        ORDER  BY [SC0],
                  [SB0000]) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

With Plan

enter image description here

TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) is responsible for the sampling and is documented here is documented here

TABLESAMPLE SYSTEM returns an approximate percentage of rows and generates a random value for each physical 8-KB page in the table. Based on the random value for a page and the percentage specified in the query, a page is either included in the sample or excluded. Each page that is included returns all rows in the sample result set.

The documentation also states

Although the plan shows a table scan is performed, only those pages that are included in the result set are actually required to be read from the data file.

The STATMAN call is to an internal aggregate function briefly described here

For WITH SAMPLE 50 PERCENT it works as though for each data page in the table SQL Server flips a coin. If it lands heads then it reads all the rows on the page. If it lands tails then it reads none.

Tracing the UPDATE STATISTICS T WITH SAMPLE 50 PERCENT call in Profiler shows the following query is emitted

SELECT StatMan([SC0], [SB0000])
FROM   (SELECT TOP 100 PERCENT [SC0],
                               step_direction([SC0]) OVER (ORDER BY NULL) AS [SB0000]
        FROM   (SELECT [C] AS [SC0]
                FROM   [dbo].[T] TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) 
                WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
        ORDER  BY [SC0],
                  [SB0000]) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

With Plan

enter image description here

TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) is responsible for the sampling and is documented here

TABLESAMPLE SYSTEM returns an approximate percentage of rows and generates a random value for each physical 8-KB page in the table. Based on the random value for a page and the percentage specified in the query, a page is either included in the sample or excluded. Each page that is included returns all rows in the sample result set.

The documentation also states

Although the plan shows a table scan is performed, only those pages that are included in the result set are actually required to be read from the data file.

The STATMAN call is to an internal aggregate function briefly described here

For WITH SAMPLE 50 PERCENT it works as though for each data page in the table SQL Server flips a coin. If it lands heads then it reads all the rows on the page. If it lands tails then it reads none.

Tracing the UPDATE STATISTICS T WITH SAMPLE 50 PERCENT call in Profiler shows the following query is emitted

SELECT StatMan([SC0], [SB0000])
FROM   (SELECT TOP 100 PERCENT [SC0],
                               step_direction([SC0]) OVER (ORDER BY NULL) AS [SB0000]
        FROM   (SELECT [C] AS [SC0]
                FROM   [dbo].[T] TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) 
                WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
        ORDER  BY [SC0],
                  [SB0000]) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

With Plan

enter image description here

TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) is responsible for the sampling and is documented here

TABLESAMPLE SYSTEM returns an approximate percentage of rows and generates a random value for each physical 8-KB page in the table. Based on the random value for a page and the percentage specified in the query, a page is either included in the sample or excluded. Each page that is included returns all rows in the sample result set.

The documentation also states

Although the plan shows a table scan is performed, only those pages that are included in the result set are actually required to be read from the data file.

The STATMAN call is to an internal aggregate function briefly described here

added 199 characters in body

Source Link

edited Feb 17, 2013 at 15:24

Martin Smith

86.5k
15
252
342

For WITH SAMPLE 50 PERCENT it works as though for each data page in the table SQL Server flips a coin. If it lands heads then it reads all the rows on the page. If it lands tails then it reads none.

If you traceTracing the UPDATE STATISTICS T WITH SAMPLE 50 PERCENT call in Profiler you seeshows the following query is emitted

SELECT StatMan([SC0], [SB0000])
FROM   (SELECT TOP 100 PERCENT [SC0],
                               step_direction([SC0]) OVER (ORDER BY NULL) AS [SB0000]
        FROM   (SELECT [C] AS [SC0]
                FROM   [dbo].[T] TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) 
                WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
        ORDER  BY [SC0],
                  [SB0000]) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

With Plan

enter image description here

TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) is responsible for the sampling and is documented here

TABLESAMPLE SYSTEM returns an approximate percentage of rows and generates a random value for each physical 8-KB page in the table. Based on the random value for a page and the percentage specified in the query, a page is either included in the sample or excluded. Each page that is included returns all rows in the sample result set.

That source alsoThe documentation also states

Although the plan shows a table scan is performed, only those pages that are included in the result set are actually required to be read from the data file.

The STATMAN call is to an internal aggregate function briefly described here

For WITH SAMPLE 50 PERCENT it works as though for each data page in the table SQL Server flips a coin. If it lands heads then it reads all the rows on the page. If it lands tails then it reads none.

If you trace the UPDATE STATISTICS T WITH SAMPLE 50 PERCENT in Profiler you see

SELECT StatMan([SC0], [SB0000])
FROM   (SELECT TOP 100 PERCENT [SC0],
                               step_direction([SC0]) OVER (ORDER BY NULL) AS [SB0000]
        FROM   (SELECT [C] AS [SC0]
                FROM   [dbo].[T] TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) 
                WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
        ORDER  BY [SC0],
                  [SB0000]) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

With Plan

enter image description here

TABLESAMPLE is documented here

TABLESAMPLE SYSTEM returns an approximate percentage of rows and generates a random value for each physical 8-KB page in the table. Based on the random value for a page and the percentage specified in the query, a page is either included in the sample or excluded. Each page that is included returns all rows in the sample result set.

That source also states

Although the plan shows a table scan is performed, only those pages that are included in the result set are actually required to be read from the data file.

The STATMAN call is to an internal aggregate function briefly described here

For WITH SAMPLE 50 PERCENT it works as though for each data page in the table SQL Server flips a coin. If it lands heads then it reads all the rows on the page. If it lands tails then it reads none.

Tracing the UPDATE STATISTICS T WITH SAMPLE 50 PERCENT call in Profiler shows the following query is emitted

SELECT StatMan([SC0], [SB0000])
FROM   (SELECT TOP 100 PERCENT [SC0],
                               step_direction([SC0]) OVER (ORDER BY NULL) AS [SB0000]
        FROM   (SELECT [C] AS [SC0]
                FROM   [dbo].[T] TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) 
                WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
        ORDER  BY [SC0],
                  [SB0000]) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

With Plan

enter image description here

TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) is responsible for the sampling and is documented here

TABLESAMPLE SYSTEM returns an approximate percentage of rows and generates a random value for each physical 8-KB page in the table. Based on the random value for a page and the percentage specified in the query, a page is either included in the sample or excluded. Each page that is included returns all rows in the sample result set.

The documentation also states

Although the plan shows a table scan is performed, only those pages that are included in the result set are actually required to be read from the data file.

The STATMAN call is to an internal aggregate function briefly described here

added 199 characters in body

Source Link

edited Feb 17, 2013 at 15:19

Martin Smith

86.5k
15
252
342

For WITH SAMPLE 50 PERCENT it works as though for each data page in the table SQL Server flips a coin. If it lands heads then it reads all the rows on the page. If it lands tails then it reads none.

If you trace the UPDATE STATISTICS T WITH SAMPLE 50 PERCENT in Profiler you see

SELECT StatMan([SC0], [SB0000])
FROM   (SELECT TOP 100 PERCENT [SC0],
                               step_direction([SC0]) OVER (ORDER BY NULL) AS [SB0000]
        FROM   (SELECT [C] AS [SC0]
                FROM   [dbo].[T] TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) 
                WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
        ORDER  BY [SC0],
                  [SB0000]) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

With Plan

enter image description here

TABLESAMPLE is documented here

TABLESAMPLE SYSTEM returns an approximate percentage of rows and generates a random value for each physical 8-KB page in the table. Based on the random value for a page and the percentage specified in the query, a page is either included in the sample or excluded. Each page that is included returns all rows in the sample result set.

That source also states

Although the plan shows a table scan is performed, only those pages that are included in the result set are actually required to be read from the data file.

The STATMAN call is to an internal aggregate function briefly described here

If you trace the UPDATE STATISTICS T WITH SAMPLE 50 PERCENT in Profiler you see

SELECT StatMan([SC0], [SB0000])
FROM   (SELECT TOP 100 PERCENT [SC0],
                               step_direction([SC0]) OVER (ORDER BY NULL) AS [SB0000]
        FROM   (SELECT [C] AS [SC0]
                FROM   [dbo].[T] TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) 
                WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
        ORDER  BY [SC0],
                  [SB0000]) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

With Plan

enter image description here

TABLESAMPLE is documented here

TABLESAMPLE SYSTEM returns an approximate percentage of rows and generates a random value for each physical 8-KB page in the table. Based on the random value for a page and the percentage specified in the query, a page is either included in the sample or excluded. Each page that is included returns all rows in the sample result set.

That source also states

Although the plan shows a table scan is performed, only those pages that are included in the result set are actually required to be read from the data file.

The STATMAN call is to an internal aggregate function briefly described here

For WITH SAMPLE 50 PERCENT it works as though for each data page in the table SQL Server flips a coin. If it lands heads then it reads all the rows on the page. If it lands tails then it reads none.

If you trace the UPDATE STATISTICS T WITH SAMPLE 50 PERCENT in Profiler you see

SELECT StatMan([SC0], [SB0000])
FROM   (SELECT TOP 100 PERCENT [SC0],
                               step_direction([SC0]) OVER (ORDER BY NULL) AS [SB0000]
        FROM   (SELECT [C] AS [SC0]
                FROM   [dbo].[T] TABLESAMPLE SYSTEM (5.000000e+001 PERCENT) 
                WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
        ORDER  BY [SC0],
                  [SB0000]) AS _MS_UPDSTATS_TBL
OPTION (MAXDOP 1)

With Plan

enter image description here

TABLESAMPLE is documented here

TABLESAMPLE SYSTEM returns an approximate percentage of rows and generates a random value for each physical 8-KB page in the table. Based on the random value for a page and the percentage specified in the query, a page is either included in the sample or excluded. Each page that is included returns all rows in the sample result set.

That source also states

Although the plan shows a table scan is performed, only those pages that are included in the result set are actually required to be read from the data file.

The STATMAN call is to an internal aggregate function briefly described here

added 178 characters in body

Source Link

edited Feb 17, 2013 at 12:27

Martin Smith

86.5k
15
252
342

Loading

Source Link

created Feb 17, 2013 at 12:21

Martin Smith

86.5k
15
252
342

Loading

Stack Exchange Network

Return to Answer