SQL Server Statistics – How to Reset Statistics After UPDATE STATISTICS WITH ROWCOUNT?

index-statisticssql serversql server 2014

For query tuning and testing purposes, you can manually assign a rowcount and pagecount to a table's index statistics by running UPDATE STATISTICS. But how do you recompute/reset the statistics to the table's actual contents?

--- Create a table..
CREATE TABLE dbo.StatTest (
    i      int NOT NULL,
    CONSTRAINT PK_StatTest PRIMARY KEY CLUSTERED (i)
);
GO

--- .. and give it a thousand-or-so rows:
DECLARE @i int=1;
INSERT INTO dbo.StatTest (i) VALUES (@i);

WHILE (@i<1000) BEGIN;
    INSERT INTO dbo.StatTest (i) SELECT @i+i FROM dbo.StatTest;
    SET @i=@i*2;
END;

A dummy query:

SELECT i%100, COUNT(*) FROM dbo.StatTest GROUP BY i%100;

… will return the following query plan (the row estimate in the Index Scan is 1024 rows).

10 000 rows

Run the UPDATE STATISTICS command..

UPDATE STATISTICS dbo.StatTest WITH ROWCOUNT=10000000;

… and the plan looks like this, now with an estimate of 10 million rows:

10 million rows

How do I reset the rowcount to the actual contents of the table without using WITH ROWCOUNT?

I've tried WITH FULLSCAN, WITH RESAMPLE and WITH SAMPLE n ROWS, but the statistics rowcount remains 10 million rows. Inserting a row or even deleting all of the rows doesn't update the statistics, because the change is too small.

Best Answer

Use DBCC UPDATEUSAGE with the COUNT_ROWS option.

DBCC UPDATEUSAGE 
(   { database_name | database_id | 0 } 
    [ , { table_name | table_id | view_name | view_id } 
    [ , { index_name | index_id } ] ] 
) [ WITH [ NO_INFOMSGS ] [ , ] [ COUNT_ROWS ] ] 

Documentation