trying to confirm # of rows from sysindexes in SQL2005

i ran the below:
USE arp
exec sp_msforeachtable 'Alter index ALL on ? Rebuild'
 and then ran the below:

USE arp
EXECUTE sp_MSforeachtable
@precommand = 'CREATE TABLE ##Results(DBName varchar(60),name varchar(128),rows char(11))',
@command1 = 'INSERT ##Results SELECT distinct db_name(),CAST(OBJECT_NAME(id) AS VARCHAR(60)) AS ''Table'',CAST(rowcnt as varchar(60)) AS ''#Rows'' FROM sysindexes WHERE indid IN (0,1) AND OBJECTPROPERTY(id, ''IsUserTable'') = 1 GROUP BY id, rowcnt, reserved, dpages ORDER BY ''Table'';'
Go

SELECT distinct rows FROM ##Results where name= 'DateSent' --#2
select count(*) from DateSent  -- #2
--#1 is diff than #2
select (select count(*) from DateSent)-(SELECT distinct rows FROM ##Results where name= 'DateSent' )/*is not zero*/
DROP TABLE ##Results
 
what could cause this? some other tables are returning fine.. it is just a handful of tables where the results are not matching, on this database.

thanks.
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
the sysindexes tables only contains a "statistics" value, not the "real" value.
so, unless you updated the "stats" , the rowcnt value indeed may "mismatch".
http://www.mssqltips.com/sqlservertip/1044/getting-a-sql-server-rowcount-without-doing-a-table-scan/

that column is only used by the sql engine to check "roughly" how many records your table (from the query) has to determine which explain plan to go ...
0
25112Author Commented:
ok..

now, i ran

sp_msforeachtable'UPDATE STATISTICS ?'
on the database.. still the #s are not matching..

what may i be missing?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
apart from the technical aspect: what are those tables?
i mean, if it's:
* large tables  => if the difference is small, you should not bother.
               -- if you really have to bother, and/or the difference is not neglectable, you will have to do a SELECT COUNT(*) anyhow
* small tables => the difference cannot be huge (except if you calculate percentages)
               --> for small tables, you could run a SELECT COUNT(*) anyhow ...


apart from that, any ongoing transactions might influence the counts ...
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

25112Author Commented:
these are the actual #s:
123093    
301840    
167588    
3527347    
56710      
163        
165133    

the query brings back the following from sysindexes, respectively:
120927     (2166 difference)
301770     (70 difference)167151     (437 difference)
3523649    (3698 difference)
56183      (527 difference)
164        (1 difference)
164557      (576 difference)

so, bottomline is even with update stats or rebuild index, sysindex can never be trusted 100% for the rowcount, right?

(this is sql 2005, would sysindex be more correct in sql 2008+?)
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so, bottomline is even with update stats or rebuild index, sysindex can never be trusted 100% for the rowcount, right?

exactly.

note for the understanding: the UPDATE STATS will only request a statistics update, but it will only run in background, and eventually taking some time before it will actually do it.

no change in sql 2008+ from what I know.
0
25112Author Commented:
thanks angelIII.

>>note for the understanding: the UPDATE STATS will only request a statistics update
when i ran the command:
sp_msforeachtable'UPDATE STATISTICS ?'
it was showing 'executing query...' for 53 seconds.. and then i got the prompt
"Command(s) completed successfully."

are you suggesting that the update may be still not be totally complete? how will i know when it is totally complete?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
http://technet.microsoft.com/en-us/library/ms187348.aspx
you may try this:
sp_msforeachtable'UPDATE STATISTICS ? WITH FULLSCAN' 

Open in new window

otherwise, only a "sample" of the table will be taken
0
25112Author Commented:
Angel, I ran with FULLSCAN.. still the sysindexes is not updated :(

so what did FULLSCAN practically achieve, compared to a sample?

thanks again!
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it should read the full table, and not only partial table to update the stats.
not sure why it doesn't work ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
at least we tried.. if it is a 'not sure' for you, i think i am ok :)

thanks a lot!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.