April 25, 2002 at 12:08 pm
I have a large Peoplesoft Financials database(~10GB). I currently have the below Options checked/enabled in the Database Options Tab.
Auto Create Stats
Auto Update Stats
Should I create jobs to run these on the weekend ?
Would this be better for performance ?
April 25, 2002 at 12:28 pm
Are you having performance issues? Are there unexplained slow times? Jobs might help, but if not, then might not be a benefit.
Of course, it won't hurt, but then your window of downtime for doing things on the weekend skrinks. Also you might see slower performance as the week progresses for heavily used tables. Might annoy some users.
Steve Jones
April 25, 2002 at 12:33 pm
no performance complaints yet.
I have from Friday 8:30pm to Monday 7:30am for downtime.
what do you think ? create the jobs ?
do i need to run sp_creatstats and sp_updtestats --> both of them ???
April 25, 2002 at 12:46 pm
I don't think you need to run sp_createstats but as for sp_updatestats I would consider. I was involved with a thread about the stats this past month and someone noted an article about "auto update stats" that confirmed what I always thought. Auto update stats only updates the stats when a cirtain percentage of change has occurred in the tables (I believe the answer was 5% but cannot find the thread right off or the article). So taking this into account, if the 10GB was all one table then 500MB of data has to change for auto update to fire. I suggest especially on large databases leave auto update on but do run sp_updatestats once a week to make sure you get as much performance out of it anyway.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 25, 2002 at 1:00 pm
"I suggest especially on large databases leave auto update on but do run sp_updatestats once a week to make sure you get as much performance out of it anyway."
which one Auto ? whch one run as a job ?
are you saying not to bother about Auto Create stats ???
April 25, 2002 at 1:48 pm
also,
what is the difference between Auto Create Stats and Auto Update Stats ?
April 25, 2002 at 3:45 pm
Sorry leave both on. Create and Update.
As for the second question this is from BOL
quote:
AUTO_CREATE_STATISTICSWhen set to ON, statistics are automatically created on columns used in a predicate. Adding statistics improves query performance because the SQL Server query optimizer can better determine how to evaluate a query. If the statistics are not used, SQL Server automatically deletes them. When set to OFF, statistics are not automatically created by SQL Server; instead, statistics can be manually created. For more information, see Statistical Information.
By default, AUTO_CREATE_STATISTICS is ON.
The status of this option can be determined by examining the IsAutoCreateStatistics property of the DATABASEPROPERTYEX function.
AUTO_UPDATE_STATISTICS
When set to ON, existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed. When set to OFF, existing statistics are not automatically updated; instead, statistics can be manually updated. For more information, see Statistical Information.
By default, AUTO_UPDATE_STATISTICS is set to ON.
The status of this option can be determined by examining the IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.
Create basically decides if a field is used for accessing the data in a where clause then build a stats value for it. Update keeps it current with the nature of ever changing data. These statistics are especially usefull when it comes to indexes in that the higher the statistics value the higher the uniqeuness of the column the more likely the index will be used. Without these stats query plans do not develope for these columns sometimes the way it is expected and when stats are out of date a field that may have the best possiblities for uniqueness (this means fewer records fit the profile) may not get used.
Hoep I got all that right and makes sense.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 25, 2002 at 5:23 pm
I leave both settings enabled. I do have a job that checks all dbs so that if any have the auto update turned off, it runs sp_updatestats once a week. Just in case!
Andy
April 25, 2002 at 9:12 pm
We do like Antares suggests. If, during the week, there is a large enough update where stats should automatically refresh, they need to. However, as Antares has cited, on a large table what it takes to get a refresh may be very large and as a result, not refreshing once a week may lead to a table performing poorly.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
April 26, 2002 at 7:51 am
What about blocking and locking here ?
April 26, 2002 at 10:23 am
quote:
What about blocking and locking here ?
If you are questioning if it will cause these issues then I have not seen this and it has more the opposite effect in the long run by helping query decisions along.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply