January 25, 2012 at 3:37 pm
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
January 25, 2012 at 3:41 pm
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
January 25, 2012 at 4:56 pm
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
January 25, 2012 at 5:01 pm
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. SelburgJanuary 25, 2012 at 5:12 pm
Also, what's the result of SELECT COUNT(*) FROM {table} WITH (INDEX(0))?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 25, 2012 at 5:24 pm
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
January 25, 2012 at 5:41 pm
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
January 25, 2012 at 5:48 pm
Dito..... METADATA OUT OF SYNC... worked
But why would it? Do you have any articles..
Thanks for your help
Shinoj
January 25, 2012 at 5:49 pm
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
January 25, 2012 at 5:55 pm
Shinoj Ramanathan (1/25/2012)
Dito..... METADATA OUT OF SYNC... workedBut 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
January 25, 2012 at 7:13 pm
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
Change is inevitable... Change for the better is not.
January 25, 2012 at 7:23 pm
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
January 25, 2012 at 8:16 pm
Heh... yeah. I should know better by now. Thanks, Paul.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 26, 2012 at 5:37 pm
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. SelburgJanuary 26, 2012 at 5:41 pm
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply