RMJCS Logo
 

Statistics are not transactional

 
 

If SQL Server statistics are created or updated inside a transaction which is subsequently rolled back, the new stats are NOT rolled back. Whether this is a problem depends on the significance of the data that existed at stats creation time that no longer exists, as well as how frequently you rollback data modification transactions.

 
     
 

Statistics are not transactional demonstration description

 
 

The T/SQL script below demonstrates that auto stats creation is not transactional. The steps taken are as follows;

  1. The script creates a simple table with a clustered primary key. dbcc show_statistics is used to show that there are no stats on the new table.
  2. Within a transaction some data is inserted into the table. The row count in the table goes from zero to greater than zero , crossing one of the auto stats thresholds, causing the need for stats to be created. dbcc show_statistics is used to show that the stats are not actually created at this point, only the fact that the stats are out of date is recorded internally.
  3. Still within the transaction a query is executed with a range predicate on the primary key column. When the query processor creates the execution plan for this query it notices that the stats on the primary key column are out of date and the stats are created (if auto_update_statistics_async is on the creation will be performed asynchronously by a separate worker process). dbcc show_statistics is used to show that there are now stats on the primary key column.
  4. At this point the transaction is rolled back - the originally empty table is now (still) empty.
  5. dbcc show_statistics is used to show that the stats created during the compilation of the query remain.
 
     
 

Statistics are not transactional demonstration script

 
 
set nocount on
go
select is_auto_create_stats_on, is_auto_update_stats_on from sys.databases where database_id = db_id()
go

-- Create a new table.
create table dbo.MyTestTable (id int not null)
alter table dbo.MyTestTable add constraint pk_MyTestTable primary key clustered(id)  
go

-- Show that there are no stats for the primary key of the newly created table (as expected).
dbcc show_statistics ('dbo.MyTestTable', pk_MyTestTable)
go

begin tran
-- Increase rowcount from zero to greater than zero, causes the stats on the table to become out of date.
insert dbo.MyTestTable(id) select top 100 row_number() over (order by message_id) from sys.messages

-- Show that stats DO NOT exist after the insert.
dbcc show_statistics ('dbo.MyTestTable', pk_MyTestTable)
go

-- Cause the stats creation to occur (assumes is_auto_create_stats_on and is_auto_update_stats_on are on).
declare @i int; select @i = count(*) from dbo.MyTestTable where id between 1 and 1 option (recompile)

-- Show that stats DO exist after the query.
dbcc show_statistics ('dbo.MyTestTable', pk_MyTestTable)
go

-- Rollback the INSERT so the data no longer exists.
rollback
go

-- Show that stats STILL exist after the query.
dbcc show_statistics ('dbo.MyTestTable', pk_MyTestTable)
go

if object_id('dbo.MyTestTable') is not null drop table dbo.MyTestTable
go
 
     
Copyright 2007-2015 RMJCS Ltd