Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ranvir_2k
Posting Yak Master
180 Posts |
 Posted - 2009-10-13 : 14:10:37
|
| I know about the UPDATE STATS and sp_updatestats commands to update the statistics.But before running them I would like to know if the stats actually need to be updated. I want to check the quality of the current stats.How can I do this? |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-10-13 : 15:28:57
|
| If you set the autp_update stats to true, SQL Server automatically updates the stats when there is enough data movement to outdate the stats. There is no manual way to know if stats are up to date. There is a stats_date function that tells you when the stats were updated. Unless you are running some really high end OLTP apps with 10s of thousands of transactions per min in which case you might want to disable auto update stats, setting the auto update stats property to TRUE should do pretty good job between your scheduled operations window where you update stats with fullscan.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2009-10-13 : 15:32:28
|
| Shukriya yaar! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-10-14 : 15:44:30
|
| sp_updatestats will only update statistics for needed one. |
|
|
|
BravehearT1326
Starting Member
24 Posts |
Posted - 2009-10-23 : 10:09:12
|
From what I have read in books online and seen in the real world it appears best to update statistics using the stored procedure sp_updatestats instead of UPDATE STATISTICS xxxx . The reason for this is that it is intelligent enough to look at the sys.sysindexes view and depending on the rowmodctr column value decide whether the statistics should or should not be updated and as such will be updated using the sampling ratio from the old statistics. In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items. Be careful if you are using a maintenance plan to update the statistics. Again from what I have seen in the real world and from experience the SSIS packatge it uses to update the statistics runs the UPDATE STATISTICS xxxx T-SQL code and will therefore rebuild ALL the indexes instead of just the ones that are needed. This will cause the job to run longer than necessary.For the Auto_Stats_Update option to work I believe 20% of the rows in the table must be amended before this take effect. My experience was that although this is turned on automatically in SQL 2005 the tables I was working with had billions of rows and the daily inserts/updates/deletes never reached this 20% threshold and therefore the stats wer not being updated.The following white paper from Microsoft talks about how the stats are collected and you may find it useful.http://technet.microsoft.com/en-us/library/cc966419.aspxI have used the following code as part of a Maintenance Plan T-SQL task to get round this.EXECUTE master.sys.sp_MSforeachdb 'print " " ; print "Working on Database -  " ; print "******************************************" ; print " " ; USE ; EXEC sp_updatestats' Hope this helps.PS - the ? in the code should be replaced by the [ ? ] |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
How to check index/table stats are good