Skip to main content
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
Source Link

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

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

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

Source Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663

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:

DBCC SHOW_STATISTICS results