| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2011-11-23 : 03:04:54
|
| Hi,At present, in sql server 2008, I have set up a job which runs every day at 3:00 am.This job has in it the following sql:use DBName1exec sp_updatestatsGOuse DBName2exec sp_updatestatsQuestions:1- Is what I am doing a good thing?2- How about doingthe following instead:alter database DBName1 set auto_update_statistics on3- Is there a property to make sure is on so that it does the necessary auto-stats, etc?Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-23 : 03:41:56
|
| Auto Update Statistics is usually a good thing, 'coz if the server discovers the statistics are out-of-shape getting them rebuilt will help the queries. But SQL will trigger that update when the index goes out of shape which will, usually, be when the server is at its busiest - i.e. the time when inserts/updates are happening most.I prefer to update statistics in the quiet period of our day, and to be able to use options that suit me; and then Auto Update is just a fall back in case something goes out of shape between scheduled updates. |
 |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-23 : 03:46:19
|
| So, is my job doing the right thing? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-23 : 08:25:17
|
We do:UPDATE STATISTICS [owner].[table] WITH FULLSCAN |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-23 : 09:06:07
|
| You are doing it for one table.But I am doing it for all the tables at 3:00 amIs that not a good idea?Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-23 : 13:51:30
|
quote:
Originally posted by arkiboys
You are doing it for one table.
No, that's an example. We do it for tables on which we want to update the statistics.sp_updatestats doesn't have the option to use FULLSCAN - unless it happens to have been the previously used option, and remains, always, the previously used open (and you use 'resample' option), and it also does all tables - whether they need it or not. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-23 : 14:48:09
|
Tara your use of "large" and mine are probably different!! We use Full Scan on everything because we then know we are on a level playing field. Perhaps I should trust SQL to use a scan-sample, but ... how accurate/reliable is it?And if your last Update Stats on a given table was FullScan isn't sp_updatestats 'resample' going to so the same thing? (Perhaps you don;t use 'resample'?)Anyways ... I prefer to be in control  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-11-23 : 15:57:36
|
| I don't use resample anymore, used to, but not anymore.Most of my systems are very busy 24/7. The performance hit that occurs with fullscan is too great to do it. For the ones that can do it though, I do use fullscan. It's just rare that I can use fullscan. The sampling is fine on our systems. Very rarely will I have to step in and run it manually with fullscan, very, very rarely. Typically recompiling a stored procedure fixes our issues, and we're working towards not needing to do that either.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-24 : 02:53:27
|
| In that case I should probably trust it more than I do . Useful input, thanks. |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-11-24 : 17:58:36
|
| Note: sp_updatestats will only affect statistics that need to be updated. It no longer works against all statistics. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-11-25 : 01:50:32
|
| Just answering Kristen statement that sp_updatestats affects all tables/stats whether they need it or not. It no longer does that and only affects statistics that need to be updated. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-25 : 11:08:20
|
| Useful to know, thanks for that |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
update statistics