Statistics are not updated in clustred index

Question

Statistics are not updated in clustred index

SQL Galaxy

One Orange Chip

Points: 29472
More actions
August 25, 2011 at 5:51 am

#242271

Hi,
I had checked Statistics one of the database recently upgrade to SQL server 2008.
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
t.name ASC
,i.type_desc ASC
,i.name ASC
34 CLUSTERED INDEX stats are not updated since today date, but NONCLUSTERED index stats are updated.
please give me suggestion, or manually run Statistics those clustered index. even auto update & create stats on this database.
thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

Automatic update of statistics occurs as the data changes. After an update the stats will update as the indexes are accessed. If you're not seeing updated statistcs, those indexes haven't been used.

If you've recently upgraded, you should run sp_updatestats to get all the statistics updated once. It's a best practice of the update process.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

sp_updatestats or update with fullscan IIRC they don't do the same thing?

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

Ninja's_RGR'us (8/25/2011)
sp_updatestats or update with fullscan IIRC they don't do the same thing?

100% accurate, they don't do the same thing. For most stats in most cases, the sampled scan, sp_updatestats runs sampled, is fine.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

Grant Fritchey (8/25/2011)
Automatic update of statistics occurs as the data changes. After an update the stats will update as the indexes are accessed. If you're not seeing updated statistcs, those indexes haven't been used.
If you've recently upgraded, you should run sp_updatestats to get all the statistics updated once. It's a best practice of the update process.

Thanks for reply..

After upgrade to SQL 2008, I have done rebulid index, sp_updatestas and checking consistenancy error.

After update stats - HEAP and Clustred index are show NULL value of last update date. Can you clarify since those index are not used both SQL 2000 and 2008.

Thanks

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

A heap doesn't have statistics, so I'm not sure what you're looking at there.

Clustered indexes should show the last update date. How are you looking at the statistics information?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

Thanks for reply..

This is script for find out last stats updated date.

select a.id as 'ObjectID', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName',

stats_date (id,indid) as stats_last_updated_time

from sys.sysindexes as a

inner join sys.objects as b

on a.id = b.object_id

where b.type = 'U'

Here attached my database stats report. plese find it. also calrify me.

Thanks

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 7

ananda.murugesan (8/26/2011)
Thanks for reply..
This is script for find out last stats updated date.
select a.id as 'ObjectID', isnull(a.name,'Heap') as 'IndexName', b.name as 'TableName',
stats_date (id,indid) as stats_last_updated_time
from sys.sysindexes as a
inner join sys.objects as b
on a.id = b.object_id
where b.type = 'U'
Here attached my database stats report. plese find it. also calrify me.
Thanks

Like I said, there are not stats on heaps, so it's hardly a surpise that it's null. If you're getting null on the other values, then they have not been updated, probably. This is not how I'd suggest going about getting stats information. This is the more traditional mechanism:

DBCC SHOW_STATISTICS('Person.Address','PK_Address_AddressID')

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

A clustered index (or any other index for that matter) shows null for the stats date if there are no rows in the table, or if the index is disabled.

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 9

GilaMonster (8/26/2011)
A clustered index (or any other index for that matter) shows null for the stats date if there are no rows in the table, or if the index is disabled.

Ah, didn't even occur to me think about a lack of data.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

Understood about the heap index statistices, Mr. Grant told me Heap index does not have maintain statistices.

Please sugesstion & give me script For the heap index to change the non clustred index.

I want to change & how to identified Qualify column name those index.

those clustred index does not have stats date because those table are not have recoreds, that is correct as Mr.Gail response.

Eventhough unused index could not able to delete due reference to another table. once confirm with developer those tables are valid or not.

thanks

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 11

I'm sorry, but you've completely lost me. What is it you're looking for now?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

ananda.murugesan (8/26/2011)
Understood about the heap index statistices, Mr. Grant told me Heap index does not have maintain statistices.

Heaps aren't indexes. It's not that they don't have to maintain statistics, it's that they don't have any statistics

Please sugesstion & give me script For the heap index to change the non clustred index.

Doesn't make sense. You change a heap to a clustered index by adding a clustered index. You can't change a heap to a nonclustered index

those clustred index does not have stats date because those table are not have recoreds, that is correct as Mr.Gail response.

And just for your info, I'm not 'Mr' anything....

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