EM and QA show different counts

Question

EM and QA show different counts

shahgols

SSCrazy Eights

Points: 9874
More actions
August 25, 2003 at 12:17 pm

#59478

Hi everyone. I have a dilema. Enterprise Mananger and Query Analyzer show different counts on one of my tables. QA is showing the right count. What is going on? Thanks in advance.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Larry C SSCommitted Points: 1543 More actions · Answer 1

How are you doing the counts in EM? If you are just looking at properties of the table, then you can possibly get a wrong count in EM. I am not sure why, but it might be related to statistics needing to be updated for the table.

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 2

Agree will also update statistics.

Plan B run a DBCC UPDATEUSAGE.

Can you post the QA code

shahgols SSCrazy Eights Points: 9874 More actions · Answer 3

I look at the counts in EM through the table properties. In QA, I run a simple "select count(1)" statement.

I have already ran a "Update statistics" statement. But it didn't help. Just ran DBCC UPDATEUSAGE, and it didn't help either.

Does anyone know where EM gets the count from?

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 4

Did you run

 DBCC UPDATEUSAGE (DB_NAME(),'TableName') WITH COUNT_ROWS

EM is getting it from SysIndexes.

-- sp_MStablespace

SELECT @rows = convert(int, rowcnt)

FROM dbo.sysindexes

WHERE indid < 2 and id = @id

How many records in the table

Maybe try a DBCC Checktable.

bj007 SSCommitted Points: 1928 More actions · Answer 5

TableName->OpenTable->Return All Rows; does it bring the same count as QA?

After all your update statistics, can you do a table->refresh from EM, and check ?just curious...

297t73g SSChasing Mays Points: 660 More actions · Answer 6

quote:
How are you doing the counts in EM? If you are just looking at properties of the table, then you can possibly get a wrong count in EM. I am not sure why, but it might be related to statistics needing to be updated for the table.

-- exactly

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 7

No flames please but you are on the same box same database

Did happens once, many moons ago many servers and many dbs after lots of coffee, etc.

shahgols SSCrazy Eights Points: 9874 More actions · Answer 8

This did the trick:

DBCC UPDATEUSAGE (DB_NAME(),'TableName') WITH COUNT_ROWS

Thanks for your input everyone!!

homebrew01 SSC Guru Points: 55566 More actions · Answer 9

I've come across the same thing .... now I only use the count from EM properties as an approximation. I use select count(*) from either QM or EM if I need the "real" # of rows. Why doesn't EM update the statistics dynamically ?? Running update statistics is cumbersome and impractical.

shahgols SSCrazy Eights Points: 9874 More actions · Answer 10

It is a terrible "bug" in my opinion. A lot of my tables had very different counts than those that EM showed. Some tables had thousands more rows of data than EM showed. I have set up a job to correct the numbers every morning, that should help a little.