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).
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:
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 theCOUNT_ROWS
option.Documentation