February 26, 2011 at 12:51 am
Please do not get mad at me, i did go through different postings related to Upate Stats here but couldn't find the right one:-). We have about 500gb database and we have AUTO_Update stats set to ON. Please look at the below concerns i have:
i) there are many tables which have frequent insert,updates going on. I read in an article for databases which have frequent insert, updates going on AUTO_update should be set to OFF? Is it?
ii) i am planning to frequently update stats on the big tables(more than 10 million) records with full scan? does this make sense?
iii)how can i know if AUTO_stats set to ON is causing an issue. I know there is a counter in profiler trace to monitor .But how long should i monitor and what would be the threshold?
And of-course i am doing all this because of performance issue:-P
Thanks.
February 26, 2011 at 1:58 am
Leave it on unless you have absolutely proven that it is hindering performance. If you turn it off, you absolutely must maintain the statistics yourself
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
February 26, 2011 at 8:35 am
I am planning to use profiler to find if Auto_stats is an issue or not, is there a better way?
February 26, 2011 at 9:24 am
There is a new option that will help - it is the Auto Update Stats Asynchronous. Turn that on and if a request to auto update stats occurs, it will not cause the initiating process to wait for the stats to be updated.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 26, 2011 at 9:56 am
I have both the options turned ON. My understanding was that i have a table of 12 millions records and if the data in table changes upto 20% then statistics are updated. Updating statistics on this table takes a lot of resources. What do u say?
February 26, 2011 at 10:19 am
Not necessarily - update statistics does not block user processes because it is run in read uncommitted transation isolation.
If you don't have a lot of memory and/or you have a slow IO subsystem, then yes it could definitely cause problems.
If it is not causing you any issues now, I wouldn't go an fix a problem that doesn't exist.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
February 27, 2011 at 9:49 pm
Thanks Jeff. I am very positive memory is not an issue, how can i measure if AUTO stats is causing an issue?
March 1, 2011 at 7:27 am
sqldba_icon (2/27/2011)
Thanks Jeff. I am very positive memory is not an issue, how can i measure if AUTO stats is causing an issue?
1) set up a to-disk (to avoid GUI slowness issues) profiler capture to capture the Performance: AutoStats event along with RPC and TSQL batch completed events.
2) set up a job to track both waitstats and file IO stalls on a recurring basis
3) correlate the waits and IO stalls to when autostats events fired in your trace capture, taking into consideration the current load on the server.
This type of stuff is standard fare for a professional tuning consultant and I HIGHLY recommend you consider getting one on board for a day or three to help you understand WHY your system is slow, fix the problem(s) and mentor you on how to do these things. Huge win for you AND your company!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 1, 2011 at 10:09 am
DO you know of any good affordable performance tuning consultants?
March 1, 2011 at 4:26 pm
sqldba_icon (3/1/2011)
DO you know of any good affordable performance tuning consultants?
Yup, the person who posted right before you.
where are you in the world and do you want someone onsite?
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
March 1, 2011 at 4:35 pm
sqldba_icon (3/1/2011)
DO you know of any good affordable performance tuning consultants?
There's a number of them (us?) around here, Gail being one of the best around here. As she already mentioned, if you were looking for a person, where are you, what requirements do you have for the person, and what do you consider affordable? 😉
What you consider affordable will make the difference of if you get someone like me with a solid rounding of experience, or someone like Gail who has beaten the SQL Engine into submission and makes it do tricks on command. 😎
EDIT: I should probably mention that Gail, or another guru, would probably be cheaper in the long run, despite the expected price tag. What they could do in 5 days will take someone like me even longer, and it's a fair bet on dollars to work-item that they're a lot more efficient.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 2, 2011 at 4:01 pm
sqldba_icon (3/1/2011)
DO you know of any good affordable performance tuning consultants?
Affordable is a VERY relative term. Value can come from being able to do something that others cannot do at all, or by being able to do things more quickly than others can. I know quite a few tuners. Some are CHEAP on a per-hour basis but not GOOD. Others are relatively EXPENSIVE on a per-hour basis but GREAT at what they do and thus also a good VALUE in my opinion.
Another thing that flavors one's idea of affordable is what scale company you work at. Mom & Pop Widgets may think $75/hour is outrageous while Fortune 100 Widgets may not blink an eye at $300/hour for the same level of consultant.
If you are interested, drop me a PM with some more information about what you are really looking for and at what level and I will try to give you some appropriate options.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 2, 2011 at 8:18 pm
thanks everyone. I would really like to have a performance tuning specialist look at our environment , however we are aware that our SQL code is not written the right way. I will definitely get back here. Thanks
March 2, 2011 at 9:30 pm
(1) set up a to-disk (to avoid GUI slowness issues) profiler capture to capture the Performance: AutoStats event along with RPC and TSQL batch completed events.
2) set up a job to track both waitstats and file IO stalls on a recurring basis
3) correlate the waits and IO stalls to when autostats events fired in your trace capture, taking into consideration the current load on the server.
This type of stuff is standard fare for a professional tuning consultant and I HIGHLY recommend you consider getting one on board for a day or three to help you understand WHY your system is slow, fix the problem(s) and mentor you on how to do these things. Huge win for you AND your company!
Best,
Kevin G. Boles
TheSQLGuru
SQL Server MVP
kgboles at earthlink dot net) Thanks for sharing some useful info!!..
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply