statistics Info

Question

statistics Info

SQL Galaxy

One Orange Chip

Points: 29472
More actions
July 15, 2011 at 1:26 am

#253774

Hi,
select
schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.rowmodctr as modifiedRows,
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,
convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,
stats_date( i.id, i.indid ) as lastStatsUpdate
from sysindexes i
inner join sysobjects tbls on i.id = tbls.id
inner join sysusers schemas on tbls.uid = schemas.uid
inner join information_schema.tables tl
on tbls.name = tl.table_name
and schemas.name = tl.table_schema
and tl.table_type='BASE TABLE'
where 0 < i.indid and i.indid < 255
and table_schema <> 'sys'
and i.rowmodctr <> 0
and i.status not in (8388704,8388672)
and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0
Below output as per this script,
Modified Rows result display 6890, here these many rows are not updated stats up to date. after last update stats.
LastStatsUpdate : 2/16/2010 4:08:28 AM - here I done manually on 16th Feb 2011, after that stats are not updated till date even-though database option setting auto_update_stats ON. please tell me why stats are not update by automatically?
table_schema : dbo
table_name : Stock_Location_Master
index_name : PK_Stock_Location_Master
table_id : 1182015342
index_id : 1
modifiedRows : 6890
rowcnt : 18858
ModifiedPercent : 0.36536218
lastStatsUpdate : 2/16/2010 4:08:28 AM
Thanks
ananda

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

Possibly they haven't been needed since. Stats are updated the first time that they are used after the 20% threshold is reached. If no query has tried to use them yet, they wouldn't have got an update.

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

GilaMonster (7/15/2011)
Possibly they haven't been needed since. Stats are updated the first time that they are used after the 20% threshold is reached. If no query has tried to use them yet, they wouldn't have got an update.

Thanks for reply..

I understand, stats are updated first time and they are used the 20% threshold is reached. If no query has tried to use them yet.

Please tell me, Is it not rquired to Run update stats command?

UPDATE STATISTICS <tablename> <index_name> WITH FULLSCAN, NORECOMPUTE;

Thanks

ananda

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You can run it if you want, though running it with NORECOMPUTE means that you do not EVER want that statistic to be automatically updated. If you've been updating your stats with that, it means that they won't be automatically updated and that you'll have to do it manually.

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

GilaMonster (7/15/2011)
You can run it if you want, though running it with NORECOMPUTE means that you do not EVER want that statistic to be automatically updated. If you've been updating your stats with that, it means that they won't be automatically updated and that you'll have to do it manually.

Thank you so much for clarification about stats.

Could I mention FULL SCAN option, or If run

UPDATE STATISTICS <tablename> <index_name> - by atumatically take FULLSCAN option.

Thanks

ananda

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Did you look in Books Online for what the defaults are?

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

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

GilaMonster (7/15/2011)
Did you look in Books Online for what the defaults are?

Yes, I got it, If I mention fullscan option - all the row in table gathering the statistics. If not, only modified rows only updated statistics which is reaching default threshold value.

Thanks

ananda