September 9, 2004 at 5:44 pm
On one large table, I've got an inconsistency between the actual # of rows and the amount reported by EM when viewing the table properties - it's only reporting ~550,000 of the 650,000 rows. I'm wondering if this is some old metadata that's cached somewhere, or a bad index perhaps? I rebuilt all the indexes on the table last night, to no avail. Is there somewhere else I should be looking?
September 10, 2004 at 2:47 am
A rowcount can be done in two ways:
Most accurate and slowest: SELECT COUNT(*) FROM MyTABLE
Less accurate but instant: SELECT ROWCNT FROM Sysindexes WHERE ID = ObjectID('MyTable') AND Name = 'MyTable'
That rowcount is used by EM and is kept uptodate with the 'Auto Update Stats' setting for the database.
But sometimes it's out of sync. So you need to do some quick database maintenance to get them in sync.
DBCC UPDATEUSAGE ('MyDatabase')
Will get the database stats updated.
Hint: DBCC UPDATEUSAGE (0) will update whatever database you are currently in.
Julian Kuiters
juliankuiters.id.au
September 10, 2004 at 12:52 pm
I think EM reports off of the sysindexes table:
Select so.name, convert(int, sc.rowcnt) as RowsInTable
From sysobjects so (nolock)
JOIN sysindexes sc (nolock) on so.id = sc.id
WHERE sc.indid < 2
order by RowsInTable desc
If your statistics are not updated this table can have inconsistent information. As Julian pointed out, the DBCC UpdateUsage command will do this.
Signature is NULL
September 10, 2004 at 2:29 pm
Even after executing DBCC UPDATEUSAGE, the number reported in sysindexes is an estimate, so it may never show an exact count.
Steve
September 13, 2004 at 6:37 am
After you execute DBCC UPDATEUSAGE(0), you may not see the refresh until you log on using a new connection. The existing connection seems to display the "unrefreshed" results. Anyone know why?
smv929
September 13, 2004 at 7:22 am
Enterprise Manager is notorious for not refreshing information unless you explicitly refresh it. Don't have time right now to test it, but I figure if you refresh the database, and/or the server, and/or the entire tree, you probably will see the new numbers. Right click and select refresh, or single left click and press F5.
Steve
September 13, 2004 at 7:44 am
Sorry. I failed to specify, that if the row count is not refreshed in Query Analyzer (after executing DBCC UPDATEUSAGE(0) ), then log into Query Analyzer with a new connection and you should see the results.
smv929
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply