9

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.

13

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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