Quickest Method To Invalidate Statistics

Question

I'm doing some testing on sync vs async auto statistics updates. I'd like to quickly invalidate all statistic objects (headers, density vectors, and histograms) to ensure that next time the statistic is used that it will be updated.

I'm trying to simulate an auto update of statistics, not an auto creation.

Ideally I don't want to change the row count so I've dismissed INSERT/DELETE operations. Ideally, I don't want to change any data values either, I have considered using UPDATE statements but I'm thinking this could take too long on some of my larger tables.

I had looked at UPDATE STATISTICS WITH ROWCOUNT, PAGECOUNT but I don't think this is what I'm after. I was hoping there was maybe a trace flag or undocumented command that would invalidate statistics.

Is there an quick, efficient way to do what I want to achieve that I haven't considered?

I'm testing on SQL Server 2016.

Answer 1

The most reliable sequence I can find to cause an automatic statistics update is:

1. Update statistics sampling zero rows
   This results in an empty statistic object.

   -- Example
   UPDATE STATISTICS 
       Person.[Address] 
       IX_Address_StateProvinceID 
       WITH SAMPLE 0 ROWS;
   

2. Update the target column(s) in a single row of the table
   This increments the column modification counter. The combination of an empty statistics object and an incremented modification counter enables a special case statistics update (it simulates creating statistics on an empty table, then adding a row).

   -- Example
   BEGIN TRANSACTION;
       UPDATE TOP (1) 
           Person.[Address] 
       SET StateProvinceID = StateProvinceID;
   ROLLBACK TRANSACTION;
   

3. Run the query with OPTION (RECOMPILE)
   This causes an automatic update of detected stale statistics, even if a matching plan for the query is already present in cache. The resulting statistics update will subsequently cause an optimality-based recompilation for the original cached plan, if it is matched again.

   -- Example
   SELECT
       A.City,
       A.AddressLine1,
       A.AddressLine2
   FROM Person.[Address] AS A
   WHERE
       1 = 1
       AND A.StateProvinceID = 54
   OPTION (RECOMPILE);
   

Demo

Using a similar AdventureWorks query as used in jyao's answer, the following script puts everything above together:

DBCC FREEPROCCACHE;
GO
-- Cache a plan for the query
GO
SELECT
    A.City,
    A.AddressLine1,
    A.AddressLine2
FROM Person.[Address] AS A
WHERE
    1 = 1
    AND A.StateProvinceID = 54;
GO
DBCC SHOW_STATISTICS 
(
    'Person.Address', 
    'IX_Address_StateProvinceID'
) WITH STAT_HEADER;
GO
-- Empty stats object
UPDATE STATISTICS 
    Person.[Address] 
    IX_Address_StateProvinceID 
    WITH SAMPLE 0 ROWS;
GO
-- Perform and rollback a single row update
BEGIN TRANSACTION;
    UPDATE TOP (1) 
        Person.[Address] 
    SET StateProvinceID = StateProvinceID;
ROLLBACK TRANSACTION;
GO
DBCC SHOW_STATISTICS 
(
    'Person.Address', 
    'IX_Address_StateProvinceID'
) WITH STAT_HEADER;
GO
-- Run the query again to trigger stats update
GO
SELECT
    A.City,
    A.AddressLine1,
    A.AddressLine2
FROM Person.[Address] AS A
WHERE
    1 = 1
    AND A.StateProvinceID = 54
OPTION (RECOMPILE);
GO
DBCC SHOW_STATISTICS 
(
    'Person.Address', 
    'IX_Address_StateProvinceID'
) WITH STAT_HEADER;
GO

Output

The DBCC SHOW_STATISTICS results show the original statistics header, the empty header, and the desired updated header at the end of the process:

Answer 2

Not sure whether the following method will meet your requirement, I would say that you can try to do this:

update statistics <your_table_name> with sample 0 rows

This will actually wipe out all info in your stats (which are sql server objects themselves and are still there, i.e NOT delete the stats itself)

According to MSDN

We recommend against specifying 0 PERCENT or 0 ROWS. When 0 PERCENT or ROWS is specified, the statistics object is updated but does not contain statistics data.

To me, emptying out your stats can be considered as one way to "invalidate" your stats. :-)

[Update] I did a quick test on SQL2014 as follows:

use AdventureWorks2014
dbcc show_statistics ('person.address', 'IX_Address_StateProvinceID')

and I can see the following

and I can then run the following

select city, addressline1, addressline2 from person.address where StateProvinceID = 54

I will get an Execution Plan as follows

Now I will do the following

update statistics person.address  with sample 0 rows
dbcc show_statistics ('person.address', 'IX_Address_StateProvinceID')

and I will see stats is wiped out.

Now if I run

dbcc freeproccache
dbcc dropcleanbuffers
select city, addressline1, addressline2 from person.address where StateProvinceID = 54

I will get a different Exec Plan.

Answer 3

Another method that just occurred to me that I didn't consider initially is using a non-updating update to modify just enough rows to trigger the threshold which could be achieved using something similiar to the below T-SQL:

UPDATE TOP (31465) AdventureWorks2012.Sales.SalesOrderHeader
SET ShipMethodID = ShipMethodID;
GO