When were the statistics for a table last updated?

Question

Post reply

When were the statistics for a table last updated?

Mani-584606

SSChampion

Points: 11788
More actions
July 22, 2010 at 4:30 pm

#227119

Hi,
I'm trying to find When were the statistics for a table last updated using the below query and noticed that some Indexes have their statistics last updated date as NULL -- Here what does NULL means?
and some indexes have their Statistics last updated as 6 months ago!!
Here the Auto update stats option is enabled and want to know why the statistics NOT updated for last 6 months??
SELECT
t.name AS Table_Name
,i.name AS Index_Name
,i.type_desc AS Index_Type
,STATS_DATE(i.object_id,i.index_id) AS Date_Updated
FROM
sys.indexes i JOIN
sys.tables t ON t.object_id = i.object_id
WHERE
i.type > 0
ORDER BY
date_updated
Thanks

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

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

kladibeeto SSCommitted Points: 1582 More actions · Answer 1

Auto update statistics fire when collumn change 10% of rows on tables larger then app 500 rows.

Is it possible in your situation?

Best regards,

Mani-584606 SSChampion Points: 11788 More actions · Answer 2

Auto update statistics fire when collumn change 10% of rows on tables larger then app 500 rows.

Is it possible in your situation?

Thanks..I will check for no.of rows in each table that have their statistics last updated 6 months ago.

But what does NULL means?

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

Mani-584606 (7/22/2010)
Hi,
I'm trying to find When were the statistics for a table last updated using the below query and noticed that some Indexes have their statistics last updated date as NULL -- Here what does NULL means?

Typically that the item in question isn't a statistic. Check the Index_id. If it's 0, that refers to the heap. Since heaps aren't really indexes, they don't have statistics. Could also be a hypothetical index or a disabled index. Could also be a table that has 0 rows in it.

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

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

kladibeeto (7/22/2010)
Auto update statistics fire when collumn change 10% of rows on tables larger then app 500 rows.

20% + 500 rows.

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

kladibeeto SSCommitted Points: 1582 More actions · Answer 5

Yes, that is correct by the books but...

Optimizer will decide when to update statistics (seek operation good example)

You can try this.

-- create simple table

create table test_stats (ID int primary key identity (1,1),Ime char (200))

-- insert some rows

insert into test_stats

select 'zzzz'

go 600

insert into test_stats

select 'rrr'

go 30

-- find clust index

select name

from sys.sysindexes

where ID = object_id ('test_stats')

-- check statistics

dbcc show_Statistics ('test_stats', 'NAMEOFCLUSTINDEX')

-- statistics should be null in this moment

--trivial query

select *

from test_stats

-- check statistics

dbcc show_Statistics ('test_stats', 'NAMEOFCLUSTINDEX')

-- statistics still should be NULL

--clustered index seek

select *

from test_stats

where ID = 234

-- check statistics

dbcc show_Statistics ('test_stats', 'NAMEOFCLUSTINDEX')

-- statistics should be updated in this moment

Conlclusion:

For clustered index scan Optimizer will only read numbers of rows from sys.indexes and he don't need to use statistics.

p.s Sorry for my bad english

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

Statistics are invalidated when the threshold of changed rows is exceeded. I can't recall the thresholds for below 500 rows (they're hardcoded values), but there's a threshold at 500 rows and thereafter at 20% of the rows in the table + 500 rows.

Once the stats are invalid, the next time the optimiser needs them, an update statistics is triggered. It's not the optimiser choosing when to update the stats, it's that the optimiser needs them and they are invalid.

In your example, the stats were invalidated after a small number of changes, but only when they were finally needed (the select) did the update happen.

This was changed after (I believe) SQL 7. In that version, the stats were updated immediately after they were invalidated. This resulted in lots of necessary stats updates in the case of lots of data changes without any queries happening.

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

kladibeeto SSCommitted Points: 1582 More actions · Answer 7

" It's not the optimiser choosing when to update the stats, it's that the optimiser needs them and they are invalid. " - that was I ment when I said "optimizer choose" but sometimes my english don't allow me to say what exactly I mean 🙂