December 29, 2006 at 4:12 pm
Guys,
I am trying to figure out what is the best way to update statistics.
Currently, all of our databases have a default setting of "Auto Update Statistics" and "Auto Create Statistics" as ON. Having these features, I believe, tells the server to update/create statistics any time it sees fit.
I would like to know how exactly the server makes the decision when to update statistics and in doing so does it take the current "busy-ness" level of the server as a parameter in its decision making?
If the "busy-ness" level is not taken in as a parameter, it implies that statistics could be updated during the busy time of the server. In that case, wouldn't it be better to set the default OFF and then create a job and schedule it to run at times we know the server is not busy?
Thanks in advance
December 29, 2006 at 4:51 pm
Read the following article to get how update stats works...
http://support.microsoft.com/kb/195565
MohammedU
Microsoft SQL Server MVP
December 30, 2006 at 3:10 am
this really depends on how big your tables are.
if you only have small tables then there won't be any impact of auto stats, but for alrge tables (say for example 1 million rows+) then auto stats updates can severely affect performance when you don't want it to.
the problem is not so much CPU cycles, but table locks....
MVDBA
December 30, 2006 at 1:50 pm
Becareful if want to disable the auto update stats...some times it effects performance too...
When it is disabled make sure you update stats manually when you load the lot of data using bcp or dts or insert ....
In sql server 2005 MS introduced option called asychronous update stats...
MohammedU
Microsoft SQL Server MVP
December 31, 2006 at 12:51 am
Hi,
Regarding your queries:
1) The "Auto Create Statistics" and "Auto Update Statastics" for the databases is a common setting that you will find most of the cases as ON. You are right in saying that SQL Server automatically decides to create/update statistics depending upon and algorithm that is based on the number of rows in the table (whose statstics is to be updated) and the selectivity of the columns used. Whenever the number of modifications in a table exceeds that number of rows in a table, SQL Server triggers the statastical updation. (However there are some rules applied to it depending upon the algorithm. Please refer to BOL for more details.)
2) As I already specified in the first point above that the parameters required for the SQL Server to update statstics are number of rows and selectivity, hence SQL Server CAN trigger this updation/creation during "busy" time. However, only 4 concurrent such operations can take place per processor.
3) If you really think that because of these "auto" settings", your production server performance is hampering, you can always switch these settings to OFF state, but beware. In such a case, you will have to take extra care in creation/updation of the statastics. Each time major modifications take place in a table, you have to manually update statistics.
Hope this writing clarifies your doubts.
Pankaj Khanna
Database Administrator - SQL Server 2000
Keep hope to keep you intact...
January 2, 2007 at 12:47 pm
Thanks guys.
After getting all the advice here and reading the article pointed out by Mohammed (http://support.microsoft.com/kb/195565), I decided to leave autostatistics with its default value of ON.
I am also considering to have a job running at night to update statistics as well, just in case.
January 3, 2007 at 12:14 am
You can profile or check the procedure cache to see if auto update stats are running when you don't want them running. The reality is that the auto-update stats does far less than you might actually think, whilst once your database has been running for a typical day unless you fire a load of adhoc queries at it the auto create will probably have created all the stats it thinks it should.
You must schedule an update stats, ideally every day, as to rely upon the autostats will not give optimum performance. You can check the stats to see what's there and when it was last updated and thus get to understand how your database works.
To be honest if there's lots of auto stats stuff then your indexing is probably in need of a review. It doesn't hurt to remove all the auto created stats from time to time either. I've established that auto stats created are not always useful.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply