Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 9 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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