Temporary Statistics for Database Snapshots in SQL Server 2012

You may have already heard that SQL Server 2012 adds automatic generation of statistics for AlwaysOn availability group readable secondaries, storing the statistics in tempdb.  This functionality was also extended to other areas beyond just AlwaysOn availability groups as I’ll demonstrate next…

Using the AdventureWorksDWDenali database, I’ll start by checking the current statistics on the FactInternetSales table:

USE [AdventureWorksDWDenali];


— Confirm statistics on FactInternetSales

EXEC sp_helpstats 'FactInternetSales';


statistics_name statistics_keys

_WA_Sys_00000008_1273C1CD  SalesTerritoryKey

_WA_Sys_0000000A_1273C1CD  SalesOrderLineNumber

_WA_Sys_0000000B_1273C1CD  RevisionNumber

_WA_Sys_0000000C_1273C1CD  OrderQuantity

_WA_Sys_0000000D_1273C1CD     UnitPrice

_WA_Sys_0000000E_1273C1CD  ExtendedAmount

_WA_Sys_0000000F_1273C1CD  UnitPriceDiscountPct

_WA_Sys_00000010_1273C1CD  DiscountAmount

_WA_Sys_00000011_1273C1CD  ProductStandardCost

_WA_Sys_00000012_1273C1CD  TotalProductCost

_WA_Sys_00000013_1273C1CD  SalesAmount

_WA_Sys_00000014_1273C1CD  TaxAmt

_WA_Sys_00000015_1273C1CD  Freight

_WA_Sys_00000016_1273C1CD  CarrierTrackingNumber

_WA_Sys_00000017_1273C1CD  CustomerPONumber

_WA_Sys_00000018_1273C1CD  OrderDate

_WA_Sys_00000019_1273C1CD  DueDate

_WA_Sys_0000001A_1273C1CD  ShipDate


Next, I’m going to add a UnitPrice value to an existing row (creating just one row out of the table with this value):

UPDATE TOP (1) dbo.FactInternetSales

SET UnitPrice = 777.77;

After that, I’ll drop the statistics for the UnitPrice column.  Why?  I want demonstrate a scenario where automatic statistics may not have already been generated due to incoming query requests – but may still be needed for queries against the database snapshot:

— UnitPrice stats

DROP STATISTICS FactInternetSales._WA_Sys_0000000D_1273C1CD;


Now I’ll create a database snapshot on the database:

— Create the database snapshot



( NAME = AdventureWorksDWDenali_Data,


'C:\Program Files\Microsoft SQL Server\MSSQL11.CAESAR\MSSQL\Data\AdventureWorksDWDenali_AWDW_S1.ss' )

AS SNAPSHOT OF AdventureWorksDWDenali;


What happens if I use the database snapshot to query for a specific UnitPrice value?  There are no statistics on the read-write database and now that I’m using the database snapshot, I’m not triggering automatic-statistics on the source database:

USE AdventureWorksDWDenali_AWDW_S1;


SELECT ProductKey, ProductStandardCost

FROM dbo.FactInternetSales

WHERE UnitPrice = 777.77;

Looking at the actual plan for this query, I see that the estimated rows vs. actual do indeed match (and as an aside – the optimizer “guess” for this predicate would have been 3,852.72 without the supporting stats):


But I can also check to see if there are automatic statistics generated by qualifying is_temporary = 1:

SELECT name, stats_id

FROM sys.stats

WHERE OBJECT_ID = object_id('FactInternetSales')

     AND is_temporary = 1;

This returns:


The stats name even gave me an additional hint of its origins via the “readonly” naming convention.  If I look up the column for the automatically generated temporary stats, I see the following:

SELECT     object_name(object_id) tblname,

           COL_NAME(object_id, column_id) columnname

FROM sys.stats_columns

WHERE stats_id = 512000;


If you need a supporting index, you still need to provide this structure in the read-write database, but when a query can be optimized using statistics alone – this is definitely a welcome new feature.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.