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.

up vote 7 down vote accepted

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:

<code>DBCC SHOW_STATISTICS</code> results

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

Original stats

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

Execution Plan with stats

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.

stats_gone

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.

new_exec_plan

  • @jyao this is exactly what I was after, thank you! After updating the statistics though with 'SAMPLE 0 ROWS' I need to update at least one row in the actual table to invalidate the statistics and induce an automatic update of statistics next time I issue a query against the table, otherwise the query optimiser won't even load the statistic object. – Fza Dec 9 '16 at 19:03

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
  • Two tiny concerns here: one is the potential log growth for big table (and I/O activity) because for a 10+ million row table, you may need to update a big chunk of rows, and another is the consequence if there is an update trigger for this table. – jyao Dec 11 '16 at 5:45

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.