SQL 2000 row count differences

[djj55]

Hello,
When I do a properties of a table I get Rows: 108083, however, when I do SELECT Count(*) FROM SameTable I get 108513. This through me off as in another database a copy of the table has for the property Rows: 108454 and the count is 108513.

The SQL server was acting strange this morning in that when I did a refresh on a database to see a table I had just created in Enterprise Manager I got the hour glass for several minutes. I then tried to stop and start the services but was unable to. I ended up rebooting the Windows 2003 server. I then noticed the problem.

Why the differences?

Is there a bigger issue?

Thank you,
djj

 

[mutley1]

2000 or 2005? And do you have any auto stats options set on the database / do you ever run sp_updatestats? Check them out in BOL. STATS_DATE should give you the date of the current stats (well, when last updated).

Rgds,

M.

 

[djj55]

Thank you that worked. I ran sp_updatestats and now it shows the proper information.

I still do not know why the other problems from this morning but this forum can not solve those.

Please note that I said 2000 in the header.

Thanks again,
djj

 

[mutley1]

np.

Sorry - I got distracted when I had read it before replying so lost my train of though.......

Cheers,

M.

 

[djj55]

I have missed information in the header more than once.



djj

 

[djj55]

Well it is happening again. My tables are not showing the current number of records when I look at the table properties in SQL 2000 Enterprise Manager.
I tried exec sp_updatestats and am still having problems. I am beginning to wonder if my SQL server is okay. When I run

dbcc show_statistics(MyTable, PK_MyTable)

it says I have

UPDATED ROWS SAMPLED STEPS DENSITY AVERAGE KEY LENGTH
Nov 21 2008 8:54AM 32028 7791 200 3.122268E-5 16.0

but if I run

SELECT COUNT(*) AS CNT 
FROM MyTable

I get 32245

Why the difference?
Thanks
djj

 

[gmmastros]

Run this in a query window and let us know what the output is:

Select DatabaseProperty(DB_Name(),'IsAutoUpdateStatistics') As [Auto Update],
       DatabaseProperty(DB_Name(),'IsAutoCreateStatistics') As [Auto Create]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Auto Update = 1
Auto Create = 1

djj

 

[JayKusch]

Your DBCC command IS NOT returning the number of actual rows ... it is returning the "ROWS SAMPLED" which is different.

Thanks

J. Kusch

 

[JayKusch]

Also keep in mind that the row count in the DBCC SHOW_STATISTICS is being pulled from the sysindexes table which may not be up to date (catch 22).


Thanks

J. Kusch