November 17, 2006 at 2:39 pm
I have run accross this problem before and I believe I had to update statistics, but I do not remember how I did it. Is it a SProc? Something in EM? (Sql2K.) Thanks in advance.
November 17, 2006 at 3:33 pm
Could you described what you found in more detail?
November 17, 2006 at 3:57 pm
TSQL -- select count(*) from empMstr = 12073 (correct)
Enterprise Mgr -- double click table empMstr count = 12048(incorrect.
Is that a better picture??
November 17, 2006 at 4:00 pm
They're not the same because EM uses the rowcounts in sysindexes ( or the Information_Schema equivalent) to give you an APPROXIMATE row count, while count(*) actually counts the rows. Updating the statistics on the table will sync them up temporarily.
Syntax is below (or use EM, or the maintenance plan wizard)
UPDATE STATISTICS table | view
[
{
{ index | statistics_name }
| ( { index |statistics_name } [ ,...n ] )
}
]
[ WITH
[
[ FULLSCAN ]
| SAMPLE number { PERCENT | ROWS } ]
| RESAMPLE
| <update_stats_stream_option> [ ,...n ]
]
[ [ , ] [ ALL | COLUMNS | INDEX ]
[ [ , ] NORECOMPUTE ]
] ;
November 19, 2006 at 7:08 am
I agree with David.
My findings shows that Enterprise Manager fires below query to get information about table.
exec sp_MStablespace N'<TableName>'
Now, this fires
SELECT @rows = convert(int, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2 and id = @id
to get the number of rows. The reason is that doing a count(*) will be much more costly if we take the data from "Real" table.
update Statistics <Table> with FULLSCAN should help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply