Statistics – Single Threaded & Parallel Operations

With default database settings, SQL Server automatically creates and updates statistics to use during query optimisation in order to build efficient query execution plans.  Let’s take a look at how these statistics operations are processed internally and determine which operations run in parallel (across multiple threads) and which operations run in serial (single-threaded).

Sampled Statistics

During query compilation, if the optimiser decides that cardinality estimates would be improved by having statistical information about the data in a column it will attempt to create the necessary column statistic during this process.  The column statistic creation is done over a random sample of the pages to keep compilation time to a minimum.

Similarly, the query optimiser looks at how many changes have occurred for existing statistic objects as part of query optimisation. If the number of rows that have changed exceed certain thresholds, SQL Server will, by default, update the statistic synchronously and then optimise the query.  The statistic update in this scenario is again done over a random sample of selected pages and will only be triggered when/if the threshold has been reached AND the index/column is touched by the query.  This as a Recompilation Threshold, not a Statistics Threshold.

The default sample size is calculated using a non-linear algorithm where the sample size decreases as the table size increases.  The default sampling rate algorithm is as follows:

  • If the table < 8MB then the statistic is gathered using a fullscan.
  • If the table > 8MB then the statistic is gathered using a non-linear algorithm where the sampling rate reduces as the number of rows in the table increases.

The sampling algorithm does not randomly sample rows, it actually samples all of the rows on “randomly” selected pages as highlighted by Conor Cunningham.  When statistics are created and updated, the optimiser must choose an access path to gather the statistics.  Where possible, for sampled statistics SQL Server will try to find an access path that is NOT physically sorted on the leading statistics key column in order to provide a more random sample and ultimately, more accurate statistics.  For statistics gathered with a full scan, the sort order of the access paths is not significant for statistical accuracy, so the lowest cost access path is chosen.

Sometimes the default sampling algorithm doesn’t capture enough interesting points in the data and the resulting histogram doesn’t accurately represent the distribution of data in the column.  In these cases, a higher sample rate can be manually/explicitly specified with the UPDATE STATISTICS command to produce a better histogram.

Sampled Statistics Demo

Restore the AdventureWorks2012 DB onto a SQL Server 2012 SP3 VM and then use Jonathan Kehayias’ scripts to create the Sales.SalesOrderHeaderEnlarged table.  Use the below T-SQL to take a look at the statistics on this table:

SELECT
sp.last_updated, OBJECT_NAME(so.object_id) AS [table], stat.stats_id,
stat.name as stats_name, STUFF((SELECT ', ' + cols.name
FROM sys.stats_columns AS statcols
JOIN sys.columns AS cols ON statcols.column_id=cols.column_id
	AND statcols.object_id=cols.object_id
WHERE statcols.stats_id = stat.stats_id
	AND statcols.object_id=stat.object_id
ORDER BY statcols.stats_column_id
FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') as stat_cols,
sp.modification_counter, sp.rows, sp.rows_sampled
FROM sys.stats as stat
CROSS APPLY sys.dm_db_stats_properties (stat.object_id, stat.stats_id) AS sp
JOIN sys.objects as so ON stat.object_id=so.object_id
JOIN sys.schemas as sc ON so.schema_id=sc.schema_id
WHERE
	sc.name = 'Sales' -- Schema Name
	AND so.name = 'SalesOrderHeaderEnlarged' -- Table Name
ORDER BY 1 DESC OPTION (RECOMPILE)
GO

1

Configure an Extended Events session to capture the following events; auto_stats, query_post_execution_showplan, sp_statement_completed and sql_statement_completed.  Add a filter on the database name to capture only events for the AdventureWorks2012 database and then start the XE session:

CREATE EVENT SESSION [StatsCapture] ON SERVER
ADD EVENT sqlserver.auto_stats(
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2012')),
ADD EVENT sqlserver.query_post_execution_showplan(
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2012')),
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1),collect_statement=(1)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name],N'AdventureWorks2012')),
ADD EVENT sqlserver.sql_statement_completed(
    WHERE ([sqlserver].[database_name]=N'AdventureWorks2012'))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\StatsCapture.xel',max_file_size=(256))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Issue the below T-SQL query to initiate the auto-creation of a column statistic:

SELECT * FROM Sales.SalesOrderHeaderEnlarged
WHERE Status <> 5;
GO

There are no existing column statistics for the status column so we would expect the optimiser to automatically create a column statistic in order to get an accurate cardinality estimate.  We would also expect sampled statistics to be gathered to keep compilation time to a minimum.  Reviewing the trace, there should be a number of entries:

xe1

The first two events query_post_execution_showplan & sp_statement_completed are as follows:

01

SELECT StatMan([SC0], [SB0000])
FROM
(
	SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM
		(SELECT [Status] AS [SC0] FROM [Sales].[SalesOrderHeaderEnlarged] TABLESAMPLE SYSTEM (6.223095e+000 PERCENT) WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
	ORDER BY [SC0], [SB0000]
) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 1)

These first two events show how the auto-creation of the column statistic on the status column is processed internally.  The first event, query_post_execution_showplan, is the graphical rendering of the execution plan that is used to generate the column statistic.  The second event, sp_statement_completed, shows the actual internal query executed to create the column statistic.  A few things to note:

  • The internal query uses the TABLESAMPLE clause, ‘TABLESAMPLE SYSTEM (6.223095e+000 PERCENT)’, which is responsible for the sampling and is documented here.
  • SQL Server also generates an ‘OPTION (MAXDOP 1)’ query hint on the internal query. The graphical rendering above shows the serial execution plan that was generated.
  • The StatMan function referenced in the internal query is a special internal aggregate function used for statistics operations and is represented by the stream aggregate operator in the graphical execution plan. The details of StatMan are undocumented, but Conor Cunningham provides some information here.

The third event, auto_stats, also lets us know that a statistic object has been automatically created.  The fourth and fifth event represent the original SELECT query submitted.  Note the order of the events here – the statistic creation is initiated and the query optimiser waits for the statistics before compiling and executing the user submitted query.  This is the default behaviour.

Re-running the same T-SQL query against sys.dm_db_stats_properties, the new column statistic will now be visible:

SELECT
sp.last_updated, OBJECT_NAME(so.object_id) AS [table], stat.stats_id,
stat.name as stats_name, STUFF((SELECT ', ' + cols.name
FROM sys.stats_columns AS statcols
JOIN sys.columns AS cols ON statcols.column_id=cols.column_id
	AND statcols.object_id=cols.object_id
WHERE statcols.stats_id = stat.stats_id
	AND statcols.object_id=stat.object_id
ORDER BY statcols.stats_column_id
FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') as stat_cols,
sp.modification_counter, sp.rows, sp.rows_sampled
FROM sys.stats as stat
CROSS APPLY sys.dm_db_stats_properties (stat.object_id, stat.stats_id) AS sp
JOIN sys.objects as so ON stat.object_id=so.object_id
JOIN sys.schemas as sc ON so.schema_id=sc.schema_id
WHERE
	sc.name = 'Sales' -- Schema Name
	AND so.name = 'SalesOrderHeaderEnlarged' -- Table Name
ORDER BY 1 DESC OPTION (RECOMPILE)
GO

3

79803 rows were sampled out of a total 1258600 rows. This further confirms that sampling was used when the column statistic was automatically created.  The sampling rate can be calculated as:

Sampling Rate = (Sampled Rows / Total Rows) * 100
Sampling Rate = SELECT (79803.000/1258600.000) * 100.000 = 6.341

The 6.341 sampling rate derived from the above calculation is not far away from the value specified in the ‘TABLESAMPLE SYSTEM (6.223095e+000 PERCENT)’ clause of the internal StatMan query.

Now that we have a column statistic for the status column, we’re going to invalidate the statistic and then re-issue the same query to this time initiate an auto-update of statistics.  Run the below T-SQL:

UPDATE STATISTICS Sales.SalesOrderHeaderEnlarged
_WA_Sys_00000006_24B26D99 WITH SAMPLE 0 ROWS;

The update statistics sampling zero rows command basically wipes out all statistics information and will result in an empty statistic object, this is documented here.  Using DBCC SHOW_STATASTICS we can confirm this:

DBCC SHOW_STATISTICS ('Sales.SalesOrderHeaderEnlarged', _WA_Sys_00000006_24B26D99)
WITH STAT_HEADER;

4

Now, issue a non-updating update.  The below T-SQL will update the status column in a single row of the table which will increment the column modification counter. The combination of an empty statistics object and an incremented modification counter simulates creating statistics on an empty table, then adding a row which will initiate a statistic update as per the recompilation thresholds described earlier.

BEGIN TRANSACTION;
    UPDATE TOP (1)
        Sales.SalesOrderHeaderEnlarged
    SET Status = Status;
ROLLBACK TRANSACTION;

This strategy of updating the statistic object with ‘SAMPLE 0 ROWS’ and then issuing a non-updating update for one value of that column is a very useful way to quickly invalidate statistics on very large tables when you want to trigger an auto-update of specific statistics.

Restart the XE session and re-execute the query:

DBCC FREEPROCCACHE;

SELECT * FROM Sales.SalesOrderHeaderEnlarged
WHERE Status <> 5;
GO

xe2

Reviewing the trace information, the order of events clearly show that the column statistic is updated during query optimisation.  Looking at the first query_post_execution_showplan event and the sp_statement_completed event we see the same graphical execution plan and the same internal query as before and from this information we can deduce that the automatic update of column statistics also uses default sampling.  We can further deduce that gathering sampled statistics, during both auto-creation or auto-updating operations, is a single-threaded operation.

What if statistics are updated manually specifying a higher sample percentage than the default sample rate would generate?  This can be useful when the default sampling algorithm doesn’t capture enough interesting points in the data and the resulting histogram doesn’t accurately represent the distribution of data in the column and you maybe don’t want to pay the full cost of updating statistics using a full scan:

UPDATE STATISTICS Sales.SalesOrderHeaderEnlarged
_WA_Sys_00000006_24B26D99 WITH SAMPLE 50 PERCENT;

xe2-5

updstats50

SELECT StatMan([SC0], [SB0000])
FROM
(
	SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000]  FROM
		(SELECT [Status] AS [SC0] FROM [Sales].[SalesOrderHeaderEnlarged] TABLESAMPLE SYSTEM (4.999828e+001 PERCENT) WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
	ORDER BY [SC0], [SB0000]
)
AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 1)

Reviewing the XE session information above it is clear that the gathering of sampled statistics for a manual update using the sample clause is also a single threaded operation.

FULLSCAN Statistics

SQL Server implicitly creates an associated statistic object when an index is created.  Similarly, when an index is rebuilt, the associated index statistic is updated.  The index statistic created/updated as a by-product of the index creation/rebuild operation will be gathered with a full scan as the index operations involve reading all of the pages into memory anyway.  This is an optimisation, the index operations are costly so SQL Server performs the statistic creation/update as part of this process to save having to re-read those pages again later.  Column statistics can also be manually created/updated with fullscan.

The gathering of statistics using FULLSCAN can be run in parallel since SQL Server 2005.

FULLSCAN Statistics Demo

Restart the XE session and issue the below T-SQL command to manually update the column statistic with a full scan:

UPDATE STATISTICS Sales.SalesOrderHeaderEnlarged
_WA_Sys_00000006_24B26D99 WITH FULLSCAN;

Reviewing the trace file, the query_post_execution_showplan event below shows a parallel plan was generated.  Notice also the higher CPU and reads for this full scan statistic operation compared to the sampled statistic operation in the previous demo, which is obviously to be expected.

xe3

7

The sp_statement_completed event shows the internal query:

SELECT StatMan([SC0])
FROM
(
	SELECT TOP 100 PERCENT [Status] AS [SC0] FROM [Sales].[SalesOrderHeaderEnlarged] WITH (READUNCOMMITTED)
	ORDER BY [SC0]
)
AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 16)

The ‘OPTION (MAXDOP 16)’ query hint cannot be manually overridden, if you’d like to be able to manually specify a MAXDOP value, which is available for index rebuilds and database consistency checks, then be sure to upvote this connect item.   Resource Governor can be used to reduce the parallelism if required but it is an Enterprise Edition only feature and probably overkill for this use case as vented by Aaron Bertrand here.

Run the query against sys.dm_db_stats_properties again to check how many rows were sampled:

statsfullscan

As expected, every row was sampled for the _WA_Sys_00000006_24B26D99 column statistic as the statistics were gathered using a full scan.

Now, drop the column statistic:

DROP STATISTICS Sales.SalesOrderHeaderEnlarged._WA_Sys_00000006_24B26D99;

Create an index on the status column and re-run the query against sys.dm_db_stats_properties again to confirm an index statistic was created as a by-product of the index creation and all the rows were sampled as the index operation had to process all of the rows anyway:

CREATE INDEX IX_SalesOrderHeaderEnlarged_Status
ON Sales.SalesOrderHeaderEnlarged (Status)

8

Rebuilding the index has the same effect:

ALTER INDEX IX_SalesOrderHeaderEnlarged_Status
ON Sales.SalesOrderHeaderEnlarged REBUILD;

9

Drop the index now to cleanup:

DROP INDEX Sales.SalesOrderHeaderEnlarged.IX_SalesOrderHeaderEnlarged_Status;

Parallel Sampled Statistics In SQL Server 2016

“Starting with SQL Server 2016, sampling of data to build statistics is done in parallel, when using compatibility level 130, to improve the performance of statistics collection. The query optimiser will use parallel sample statistics whenever a table size exceeds a certain threshold.”

As per the previous demos, prior to SQL Server 2016, the gathering of sampled statistics are serial, single-threaded operations.  Only statistic operations gathered using a full scan would qualify for parallelism.  Now in SQL Server 2016, all automatic and manual statistic operations qualify for parallelism.

Parallel Sampled Statistics In SQL Server 2016 Demo

Backup the AdventureWorks2012 DB, restore it to a SQL Server 2016 instance and set the database compatibility level to 130.  As before, configure an Extended Events session to capture the same events.  Issue the below T-SQL query to initiate the auto-creation of a column statistic:

SELECT * FROM Sales.SalesOrderHeaderEnlarged
WHERE Status <> 5;
GO

There are no existing column statistics for the status column so we would expect the optimiser to automatically create a column statistic in order to get an accurate cardinality estimate.  We would also expect sampled statistics to be gathered to keep compilation time to a minimum.  Since we are on a SQL Server 2016 instance and the database is at the latest compatibility level, we would also expect the sampled statistics to qualify for parallelism.  Let’s verify this by reviewing the trace file:

xe4

The first two events query_post_execution_showplan & sp_statement_completed are as follows:

10

SELECT StatMan([SC0], [SB0000])
FROM
(
	SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM
		(SELECT [Status] AS [SC0] FROM [Sales].[SalesOrderHeaderEnlarged] TABLESAMPLE SYSTEM (6.223095e+000 PERCENT) WITH (READUNCOMMITTED)) AS _MS_UPDSTATS_TBL_HELPER
	ORDER BY [SC0], [SB0000]
)
AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 4)

These first two events show how the auto-creation of the column statistic on the status column is processed internally.  The first event, query_post_execution_showplan, is the graphical rendering of the execution plan that is used to generate the column statistic and it clearly shows parallel operators in the execution plan.  Furthermore, the dop column in the Extended Event trace file highlighted in red above, shows that the value for degrees of parallelism was four.

The second event, sp_statement_completed, shows the actual internal query executed to create the column statistic.  In SQL Server 2016 SP1, SQL Server generates an ‘OPTION (MAXDOP 4)’ query hint on the internal query which is respecting the ‘Max Degree Of Parallelism’ (MAXDOP) instance level setting.  With the introduction of ‘Database Scoped Configurations’ in SQL Server 2016, MAXDOP can be set at the database level and the parallel plan generated by the statistic operation would respect this setting if set.

The third and fourth auto_stats events also lets us know that a statistic object has been automatically created and loaded.  Looking at sys.dm_db_stats_properties we can again verify that the auto-creation of the column statistic used default sampling:

11

Now that we have a column statistic for the status column and proved that the auto-creation of the column statistic used default sampling and ran in parallel, we’re going to invalidate the statistic and then re-issue the same query to initiate an auto-update of statistics.  Run the below T-SQL:

BEGIN TRANSACTION;
    UPDATE TOP (314655)
        Sales.SalesOrderHeaderEnlarged
    SET Status = Status;
ROLLBACK TRANSACTION;

Restart the XE session and re-execute the query:

DBCC FREEPROCCACHE;

SELECT * FROM Sales.SalesOrderHeaderEnlarged
WHERE Status <> 5;
GO

xe5

Reviewing the trace information, the order of events clearly show that the column statistic is updated during query optimisation.  Looking at the second event, query_post_execution_showplan, the dop column again contains a value of four meaning that the statistic update operation ran in parallel.  Looking at sys.dm_db_stats_properties we can again verify that the auto-update of the column statistic used default sampling:

11

What if statistics are updated manually specifying a higher sample percentage than the default sample rate would generate?

UPDATE STATISTICS Sales.SalesOrderHeaderEnlarged
_WA_Sys_00000006_24B26D99 WITH SAMPLE 50 PERCENT;

xe6

This also qualifies for parallelism.  With this information, we can deduce that gathering sampled statistics, during the auto-creation, auto-updating and manual updating of statistics, qualifies for parallelism in SQL Server 2016.

Conclusion

Prior to SQL Server 2016, the gathering of sampled statistics are serial, single-threaded operations.  Only statistic operations gathered using a full scan would qualify for parallelism.  Now in SQL Server 2016, all automatic and manual statistic operations qualify for parallelism.

Resources

https://sqlperformance.com/2016/07/sql-statistics/statistics-maxdop

https://technet.microsoft.com/en-us/library/cc966419.aspx?f=255&MSPPError=-2147217396#XSLTsection129121120120

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/05/23/query-optimizer-additions-in-sql-server/

http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned-lies-and-statistics-part-i.aspx

http://dba.stackexchange.com/questions/34883/how-does-sampling-work-when-updating-statistics

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/05/23/query-optimizer-additions-in-sql-server/

Advertisements
This entry was posted in Statistics, CE & QO. Bookmark the permalink.

One Response to Statistics – Single Threaded & Parallel Operations

  1. Pingback: Parallel Stats Sampling – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s