SQL Server – Quickest Method to Invalidate Statistics

sql serversql-server-2016statistics

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.

Best Answer

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

Related Question