update statistics

Question

Post reply

update statistics

madhu-686862

SSChampion

Points: 10707
More actions
June 4, 2008 at 8:28 am

#183047

hi,
how to check the statistics are upto date or not?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Here is a way to check when stats where last updated.

[font="Courier New"]SELECT

ST.name AS stats_name,

COL_NAME(sic.OBJECT_ID, sic.column_id) AS column_name,

STATS_DATE(st.OBJECT_ID, st.stats_id) AS last_updated

FROM

sys.tables AS tbl INNER JOIN

sys.stats st ON st.OBJECT_ID=tbl.OBJECT_ID INNER JOIN

sys.stats_columns sic ON

sic.stats_id=st.stats_id AND

sic.OBJECT_ID=st.OBJECT_ID

WHERE

tbl.name=N'Config' AND

SCHEMA_NAME(tbl.schema_id)=N'security'[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

I use this:

USE dbName;
SELECTid
,indid
,OBJECT_NAME(id)
,[name]
,rowcnt
,rowmodctr
,STATS_DATE(id, indid) AS Date_LastUpdated
FROM sys.sysindexes WITH ( NOLOCK )
WHERE indid > 0 AND indid < 255 AND
OBJECTPROPERTY(id, 'IsUserTable') = 1 AND -- system tables excluded
INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id, name, 'IsHypothetical') = 0 AND
rowmodctr <> 0

Whether or now statistics is up-to-date is an important question, but, just as important is the sampling percentage of rows on which the stats was updated. Using sp_updatestats or relying on the Auto-Update-Statistics database option does not ensure an adequate row sampling percentage was employed to update stats. In the case of a large table with a skewed data distribution this means that the stats can be up-to-date BUT inaccurate, potentially causing serious performance issues.

Use the following to determine whether the stats of a certain table/index have been updated with adequate sampling:

dbcc show_statistics ( tableName, indexName );

The Rows and Rows Sampled columns in the first result set is what you need to look at.

Use the following to update the stats if Rows Sampled is much lower than Rows :

UPDATE STATISTICS tableName WITH FULL SCAN;

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

David Kranes SSCrazy Points: 2523 More actions · Answer 3

>>UPDATE STATISTICS tableName WITH FULL SCAN;

FULL SCAN should be one word like:

UPDATE STATISTICS tableName WITH FULLSCAN;

Best Regards,

~David

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

David Kranes (6/4/2008)
>>UPDATE STATISTICS tableName WITH FULL SCAN;
FULL SCAN should be one word like:
UPDATE STATISTICS tableName WITH FULLSCAN;

Oops, correct, thank you.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]