June 10, 2008 at 8:55 am
Most likely you have a mixture of badly written queries and inadequate indexes. There's no quick fix for this. If it's as critical as you say, you may be best off hiring a competent consultant with expertise in performance tuning for a short time.
What I suggest to start is the following:
Run profiler for an hour or so during a period of high load. Capture the RPC completed and the SQLBatchCompleted events. Make sure that you have the TextData, duration, cpu and reads at a minimum.
Take the profiler results and find the top 5 worst performing queries in terms of duration, of CPU, of reads. There's a good chance that there's an overlap here.
Take those queries (I suggest no more than 10) and do what you can to optimise them. That may involve changing the query, it may involve changing or adding indexes.
Repeat until system performance is adequate. It probably will take no more than 3 itterations to get acceptable performance. Most often it's a small number of queries causing all the problems.
Adding things like nolock, readpast, etc are just temporarily fixing the symptoms of the problem and throwing hardware at a performance problem should always be the last thing that you do, not the first. That said, your server looks a little light on memory. Suggest an upgrade to 4GB memory, possibly even 8GB.
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
Viewing post 1 (of 2 total)
You must be logged in to reply to this topic. Login to reply