August 15, 2008 at 1:14 am
Hi Everyone,
I have to do performance tuning for my some of the sql server. I read so many topics and now i am confused that from where to start. I have a slow running server and timeout problem. I ran the performance monitor and looked at that
batch request\sec > %Processor Time
I also ran the trace to find out queries that takes long time. RPC out and , statement complete, statement recompile counters but still don't know how to tackle with this problem and go step by step to tune my server.
Like this i want to tune my server in different areas. Can anyone please let me know that what is the good way to strat the tuning and is there any good articles, white papers or material that i can refer. Please provide me the link.
Thank you very much in advance.
Zombi
August 15, 2008 at 1:40 am
search this site for some of Brad McGhee's tutorials, they're very helpful
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 15, 2008 at 2:41 am
Take your trace file and look for the queries that overall take the longest and/or have the highest number of reads. Take those queries/procs and see if you can tune them to be faster and/or less resource intensive. For starters, just take the top 3 or so.
Fixing the queries may require rewriting or it may require indexeing or both. If you need help on the specifics, post here, we can help.
Once you've fixed the top 3 or so, do another profiler trace, look for the top 3 worst performing queries (should be different ones) and repeat the tuning.
Normally it doesn't take more than 4 iterations to have a noticeable effect on a server's overall performance.
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 15, 2008 at 2:48 am
See http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
Usually in such a case I use the "tuning" template provided by the profiler. I set up a "server-side" trace and run it during the working hours, to capture a realistic workload. Whenever possible, I establish a baseline together with the customer (for example this query shouldn't last more than "n" seconds, etc.)
Also don't forget the "tuning advisor", which can be really helpful.
August 15, 2008 at 4:21 pm
Hello,
Where to find for the Brad McGhee's tutorials. Please provide link Thanks,
Zombi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply