December 17, 2002 at 12:54 pm
Hello all, we have a table that shows different counts through EM and Query Analyzer. I was told that this had happened before (on SQL 2000) also, EM showed 1 million rows on another table and yet QA showed 500,000. The true number was 1 million. This time, QA shows more rows (300 more) and EM shows the wrong number of rows. I ran a update statistics on the table but the same numbers show. Any ideas would be greatly appreciated. Thank you.
December 17, 2002 at 1:27 pm
What methods are you using to get your results? Is it from properties on tables tab in EM, or a query? And in QA?
December 17, 2002 at 2:03 pm
EM might be looking at the sysindexes.rows to find out the number of rows in the table while QA is running select count(*). Try to run sp_spaceused instead or select from sysindexes. This could be due to uncommited transaction, not only to unupdated statistics.
December 20, 2002 at 1:35 pm
In EM we double click a table, which opens up the properties page. In QA we run a select count() command.
My user tells me that the first time this happened on another table, it was QA's select count(*) that returned the wrong number. I will run the sp_spaceused and will let you know what happens. As far as commited or uncommited, the table was loaded from one file, using one DTS process. Thank you.
December 20, 2002 at 2:04 pm
That helps, then. In enterprise manager, your results are being generated from the sysindexes table, which is only correct if the modified row count column has a zero value. In QA, a Select Count would give the correct number of rows, but the difference is that it is an actual count at the time, not a reference to the sysindexes table.
As far as what the user says about the row count, I would be hard pressed to believe it. Not that I wouldn't look into it, I would, it's just that I've never even heard of an instance that a Select Count was inaccurrate.
December 20, 2002 at 6:28 pm
I concurr with Scorpion. The problem with sysindexes and the data there is it is updated via sp_updatestats or by having Auto Update Statistics turned on for the DB. The problem with the later is a percentage of change has to occurr before it generally runs. Overall since it requiresa outlying factors for sysindexes to remain accurate it is most likely not. I only trust select count(*) or if they need more to believe have the pick a small column and do a select for that column and let run to completion and see what the rowcount is then in QA.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply