sp_spaceused is giving mixed results

[mjp483]

How come when I run select count(*) on my table I get a different number of rows than the ROWS attribute returned when I run sp_spaceused?


1) select count(*) = 36526

2) sp_spaceused 'EnrollmentDetail' ,@updateusage = 'TRUE' = 36365

Shouldn't these two numbers match?

 

[SQLBill]

Did you run them at the exact same time?

-SQLBill

Posting advice: FAQ481-4875

 

[mjp483]

Yes, One right after the other.

 

[SQLBill]

How many records are inserted into this database every minute? Every second?

For my database, if I ran those right after each other the difference would be in the thousands.

Your difference is only 161 records....could you have received that many in the time between the select count(*) query ran and the sp_spaceused command ran?

-SQLBill

Posting advice: FAQ481-4875

 

[mjp483]

There's no way the record count could change like this, over a period of about 1 second. I think it has something to do with my statistics not updated?

 

[SQLBill]

Why can't the record count change like that? Are you saying you don't get that many inserts in a short time?

My database gets anywhere from 0 to several thousand inserts a second.

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

Have you tried running:

SELECT COUNT(*) FROM EnrollmentDetail
GO
DBCC UPDATEUSAGE ('EnrollmentDetail')
GO
SELECT COUNT(*) FROM EnrollmentDetail
GO

to see if that makes a difference?

-SQLBill

Posting advice: FAQ481-4875

 

[RobertT687]

My $0.02.
I generally don't trust the row count results returned from anything other than 'SELECT COUNT(*) FROM mytable'. You're on the right track about updating statistics.
Try something like UPDATE STATISTICS yourtablename.
Then try the sp_spaceused and select count(*) commands.
If the table is inserted or deleted heavily the system rowcount statistic could get out of sync.

 

[mjp483]

I tried these things:

sp_spaceused 'EnrollmentDetail' ,@updateusage = 'TRUE'

select count(*)....still different counts

then I tried:

Update Statistics EnrollmentDetail

sp_spaceused 'EnrollmentDetail'

select count(*)....still different counts

When I bring this table to my local machine it works as expected. On production, which is offsite, this is still returning two different row counts, off by a couple hundred...