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 |
|
cshah1
Constraint Violating Yak Guru
347 Posts |
 Posted - 2009-01-15 : 13:43:04
|
| More I read more I am getting confused. I thought it is good idea to update statistics once a week atleast (despite you are rebuilding indexes daily)Even though auto create statistics and auto update statistics are "ON" Should I still run a) sp_updatestats ORb) UPDATE STATISTICS TableName WITH SAMPLE 25 PERCENT, ALLWhich one above is better? a) has an advantage that it will skip the table if it does not need to udpate statistics but it will take a *while* for large dbsb) you are doing sample of 25 percent records so for large tables you do not have to wait hours (e.g. FULL SCAN) Sometimes, I have found that running FULL SCAN has improved performance of my query. I have to setup a job and not sure which one of the above I should be using in the job? Thanks, |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-15 : 14:19:17
|
quote:
Originally posted by cshah1
More I read more I am getting confused. I thought it is good idea to update statistics once a week atleast (despite you are rebuilding indexes daily)Even though auto create statistics and auto update statistics are "ON" Should I still run a) sp_updatestats ORb) UPDATE STATISTICS TableName WITH SAMPLE 25 PERCENT, ALLWhich one above is better? a) has an advantage that it will skip the table if it does not need to udpate statistics but it will take a *while* for large dbsb) you are doing sample of 25 percent records so for large tables you do not have to wait hours (e.g. FULL SCAN) Sometimes, I have found that running FULL SCAN has improved performance of my query. I have to setup a job and not sure which one of the above I should be using in the job? Thanks,
Updating Stats on weekly or nightly depends how high your DML operation? We do it weekly for almost static one and regularly for highly DML ? |
 |
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2009-01-15 : 14:47:11
|
| Thank you all. Tara, I think isp_UPDATE_STATISTICS does not reflect what you just said. May be I am looking an older version. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
UPDATE Statistics