Table Rowcount Different

Question

Table Rowcount Different

Shinoj Ramanathan

SSC Eights!

Points: 894
More actions
January 25, 2012 at 3:37 pm

#263450

I have three statements to find the row counts in a heap
SELECT COUNT(*) FROM f_sw.VWWobTimeOut
GO
sp_spaceused 'f_sw.VWWobTimeOut'
GO
SELECT * FROM sys.partitions AS P WHERE object_id = OBJECT_ID('f_sw.VWWobTimeOut')
GO
---Please find output in attachment
Questions:-
1) Why would the number of row in index = 0 differ from rows in index 2 and 3 in sys.partitions output
2) Why would Count(*) return the result from index = 2 or 3 and sp_spaceused return count from index = 0? (why are the results of sp_spaceused different from count(*)
BTW, I have tried the same query after executing DBCC UpdateUsage
Shinoj

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

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Edit: Nevermind...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150457 More actions · Answer 2

What version & build of SQL Server is this?

Have you run a DBCC check on the database, or at least on the table?

Is there anything unusual about the table/view?

Can you reproduce this on another system?

If so, can you provide a script to allow us to do the same?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

Jumping in because I want to know too. 😀

Statistics?

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Paul White SSC Guru Points: 150457 More actions · Answer 4

Also, what's the result of SELECT COUNT(*) FROM {table} WITH (INDEX(0))?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Shinoj Ramanathan SSC Eights! Points: 894 More actions · Answer 5

What version & build of SQL Server is this?

- Microsoft SQL Server 2008 R2 (SP1) (Build 7601: Service Pack 1)

Have you run a DBCC check on the database, or at least on the table?

- Yes! No issues

Is there anything unusual about the table/view?

- Nothing unusual.. (It is a transactional heap)

Can you reproduce this on another system?

- Yes.. On the same table

If so, can you provide a script to allow us to do the same?

- As in the message. Just replace the table names

SELECT COUNT(*) FROM {table} WITH (INDEX(0))?

- Same as that in Count(*) -- 772 rows

Paul White SSC Guru Points: 150457 More actions · Answer 6

It seems the metadata is slightly out of sync for this heap. This can happen for lots of reasons, and the rows column in sys.partitions is documented in Books Online as only returning an approximate number of rows in the partition. I think sp_spaceused uses sys.partitions internally, so that would explain your results. One way to re-sync the metadata might be to execute:

ALTER TABLE f_sw.VWWobTimeOut REBUILD;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Shinoj Ramanathan SSC Eights! Points: 894 More actions · Answer 7

Dito..... METADATA OUT OF SYNC... worked

But why would it? Do you have any articles..

Thanks for your help

Shinoj

Paul White SSC Guru Points: 150457 More actions · Answer 8

In fact sp_spaceused uses sys.dm_db_partition_stats:

SELECT

@reservedpages = SUM (reserved_page_count),

@usedpages = SUM (used_page_count),

@pages = SUM (

CASE

WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)

ELSE lob_used_page_count + row_overflow_used_page_count

END

),

@rowCount = SUM (

CASE

WHEN (index_id < 2) THEN row_count

ELSE 0

END

)

FROM sys.dm_db_partition_stats

WHERE object_id = @id;

You might also try using:

EXECUTE sp_spaceused @objname= 'f_sw.VWWobTimeOut', @updateusage='true';

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150457 More actions · Answer 9

Shinoj Ramanathan (1/25/2012)
Dito..... METADATA OUT OF SYNC... worked
But why would it? Do you have any articles..

Well it is documented as being not guaranteed - that says it all really. There are some well-known cases where this happens for tables originally created in SQL Server 2000 and upgraded to 2005, 2008...without ever rebuilding the table (or, equivalently for clustered tables, rebuilding the clustered index). You might like to read http://sqlblog.com/blogs/kalen_delaney/archive/2009/12/07/how-many-rows.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Hmmm... I wonder what they did from 2k5 to 2k8 because it used to be "guaranteed" as of 2k5. From the 2k5 BOL on the subject of DBCC UPDATEUSAGE (the bolding is mine).

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure. [font="Arial Black"]In SQL Server 2005, these values are always maintained correctly.[/font] Databases created on SQL Server 2005 should never experience incorrect counts, however, databases upgraded to SQL Server 2005 may contain invalid counts. We recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

Now they just say that it's used to correct inaccuracies. From BOL 2k8R2 on the same subject.

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

I'm glad you guys brought this up because I was still believing the old 2k5 definition.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 11

Jeff,

In my experience, BOL is a bit optimistic here. In "Databases created on SQL Server 2005 should never experience incorrect counts", the operative word is 'should'. If the product were bug-free at all times, the use of the word 'always' in the previous sentence might be justified 🙂

Certainly, row counts and such are much more likely to be correct 2K5+, but I would be cautious about taking this as an absolute guarantee. For example: http://sqlblog.com/blogs/michael_zilberstein/archive/2009/12/08/19582.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

Heh... yeah. I should know better by now. Thanks, Paul.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 13

Jason Selburg (1/25/2012)
Jumping in because I want to know too. 😀
Statistics?

I must admit, I only just now "re-found" the Subscribe To Topic under Topic Options. Man it's been a long year. 🙂

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Paul White SSC Guru Points: 150457 More actions · Answer 14

Jason Selburg (1/26/2012)
I must admit, I only just now "re-found" the Subscribe To Topic under Topic Options. Man it's been a long year. 🙂

Well that's new to me, thanks :smooooth:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi