August 6, 2008 at 9:23 am
Its really difficult to know the right thing to do when making "sweeping recommendations" for SQL Servers in an enterprise environment. An area of contention amongst DBAs is the enabling or disabling of the "Auto Create" and "Auto Update" options for stats.
I've shifted back and forth between leaving these options enabled or disabled over the years. Recently I've come to the realization that in environments where I had to manage a bunch of SQL Servers at once, leaving these options enabled and handling query tuning on a case-by-case basis was a bit more "manageable".
I'm curious to see what the rest of the community recommends, especially for DBAs managing a large number of SQL Server instances. Particularly when dealing with a mixed environment where the majority of SQL Servers are SQL Server 2005, and a minority are SQL Server 2000.
August 6, 2008 at 9:54 am
Both turned on, and scheduled maintenance plans to update stats periodically and rebuild certain indexes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2008 at 11:02 am
I agree with GSquared. Both enabled, everywhere. Also a script that checks indexes and then does a rebuild or reindex based on fragmentation. Also, there is the occasional index or table that needs a manual update on the statistics becuase of odd data distribution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 7, 2008 at 4:12 am
My experience:
In general, it is best to leave auto create statistics and auto update statistics on.
In this case, statistics are automatically recalculated after a big change of the data.
However, I have a a database (SQL 2000) with a very large table with a lot of columns.
On a regular base, a job runs to correct certain values (a kind of interpolation).
This Interpolation runs with cursors (I really hate cursors for a job like this, but can't change this due to support reasons)
To speed up this interpolation I changed some of the statistics behavior of this table (with sp_autostats). I excluded the statistics of the fields that are being updated by the procedure.
The recalculation of the statistics is done as first part of the job.
August 7, 2008 at 11:05 am
There are always specific instances where the general rule doesn't apply. If you have a table where auto-generation and auto-update of statistics doesn't do what you need, then by all means brew your own. Test both, see which works best, use that.
In general, auto is good enough. In most cases, brew your own is more likely to cause problems than fix them. But certainly not in all cases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 8, 2008 at 3:03 am
I agree with most of the respondent regarding Auto Update Statistics ON but I did not see any good reason to keep Auto Create Statistics ON on Production database.
Why we need to have create statistics ON?
What's the use of having more statitiscs than we already have due to Index creation?
I will prefer having this option ON in development environment. We can run the application in development environment and analyse the statistics created by the SQL Server. I will manually create require statistics into Production database.
I feel that if we keep AUTO CREATE STATISTICS ON than it will create some unnecessary statistics and SQL Server will also have to maintain (update) those statistics too because AUTO UPDATE STATISTICS ON. It will hurt performance on the server.
August 8, 2008 at 6:22 am
If I were you, I'd do some serious testing. I suspect the cost of maintaining statistics in most systems, for most databases, is a lot lower than the cost of not having statistics available for a query that needs them. There are exceptions, but, as with most exceptions, they're exceptional.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 8, 2008 at 6:33 am
HI All,
I'm new to statistics and all that, but finding it interesting 🙂
Yesterday on our production system we had a query that ran like a dog however if I put a top 100 on the query it ran brilliantly even though only 5 rows were return.
As a result someone pointed out that I should update the stats or at least check them.
I did a manual update and everything is great now.
Please note we do have auto update on the system. This stat as obvious just a bit out.
Now as a result I started doing some reading found this link:
http://www.mssqltips.com/tip.asp?tip=1550
and wanted to know what the Frag % is at the bottom of this article.
What does Fragmentation do?
Is a high number bad or a low number bad?
What do you do when you find bad fragmentation?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 8, 2008 at 6:42 am
Grant,
Do you have any tips/advice on scripts that you mentioned above, that check indexes and fragmentation etc?
and how often you do this kind of thing on your system.
I'm just worried as we don't have a DB in our company and no-one to my knowledge does maintenance on the servers he he he
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 8, 2008 at 6:44 am
Auto create stats on, auto update stats on. If you find stats that aren't getting updated often enough, or find ones where the sampling is too low, create a job that manually updates those.
The optimiser creates column statistics when it needs to know the distribution of data in a column. If auto create is off, your query plans may be less optimal than is possible, because the optimiser has to guess about number of rows affected.
Chris: Fragmentation is the % of pages in an index leaf level that are out of order. If it's high (> 30%) rebuild your index. It applies to indexes, not to statistics, as statistics don't have pages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2008 at 6:49 am
Thanks for the reply...
Looks like I might spend my afternoon rebuilding half the indexes on the server 🙁
as most are 66.66%
Also is this just simply a matter of manually droping and recreating the indexes?
thanks again
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 8, 2008 at 6:56 am
Last question he he he
Does anyone have any good procedures for searching an entired DB for Index Fragmentation?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 8, 2008 at 6:58 am
Christopher Stobbs (8/8/2008)
Also is this just simply a matter of manually droping and recreating the indexes?
ALTER INDEX ... REBUILD
Don't do it while the systems are in use. It's a maintenance operation that should be done in a quiet time. As for checking on the entire DB, look at the sys.dm_db_index_physical_stats view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 8, 2008 at 7:29 am
THis pretty exciting stuff 🙂
Thanks for all the help everyone.
Whats cause index fragmentation?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 8, 2008 at 8:15 am
Christopher Stobbs (8/8/2008)
Grant,Do you have any tips/advice on scripts that you mentioned above, that check indexes and fragmentation etc?
and how often you do this kind of thing on your system.
I'm just worried as we don't have a DB in our company and no-one to my knowledge does maintenance on the servers he he he
We're currently using one that I originally got from the scripts posted here. I've done modifications to it since then to update some the methods, but the original was good. Do a search through the scripts. You'll find a bunch. Use the one that seems appropriate to your environment. Minimum behavior should be to check the fragmentation prior to doing anything and then it should allow for both reindex and defrag based on that fragmentation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply