How to effectively troubleshoot?

I am a developer but I've been put in a position to have to troubleshoot
performance problems on SQL Server 2000 and 2005. So when users
complain that the "system is slow", I do various things, like run
"sp_who2 active" and looking for processes blocking each other.
However, if I don't find anything, I am not sure what to do next.
So I have a couple of questions:
1. I look at Management/Activity Monitor/Process Info window. Is the
Physical IO column cumulative? Or does it reflect the IO in the current
transaction?
2. I've noticed that the tempdb database is on the same drive as the
main database's log file. The box has 4 drives in a RAID5
configuration. Is this normal? Can it be impacting performance? If,
so, what should be the optimal configuration given the 4 available
drives in a RAID5 config?
3. Can someone recommend a good troubleshooting guide for both SQL 2000
and 2005?
Do yourself a favor if possible. Hire a professional to give you a
performance review - and mentor you at the same time! Win-Win for you.
A good tuner uses a mix of training, experience, art (and sometimes luck).
It is too wide and deep a topic to promulgate via a newsgroup. :-)
Answering your questions specifically
1) I believe I/O shown is cumulative for that spid's existence, which could
include much more work than just the ongoing 'current transaction'.
2) tempdb is best placed on a separate spindle from other dbs. Raid 5 is
not optimal for it, or for (especially) log files. It doesn't seem like you
have any flexibility if all you have is 4 drives in raid5 however.
3) Microsoft and other entities have training classes you can take on perf
tuning. I don't know of a good 'beginner's guide to tuning' though. There
are some very good ones for experienced people, but I think they may well
confuse/confound you more than help.
TheSQLGuru
President
Indicium Resources, Inc.
"Frank Rizzo" <none@.none.com> wrote in message
news:%233Fw47FqHHA.3892@.TK2MSFTNGP05.phx.gbl...
>I am a developer but I've been put in a position to have to troubleshoot
>performance problems on SQL Server 2000 and 2005. So when users complain
>that the "system is slow", I do various things, like run "sp_who2 active"
>and looking for processes blocking each other. However, if I don't find
>anything, I am not sure what to do next.
> So I have a couple of questions:
> 1. I look at Management/Activity Monitor/Process Info window. Is the
> Physical IO column cumulative? Or does it reflect the IO in the current
> transaction?
> 2. I've noticed that the tempdb database is on the same drive as the main
> database's log file. The box has 4 drives in a RAID5 configuration. Is
> this normal? Can it be impacting performance? If, so, what should be the
> optimal configuration given the 4 available drives in a RAID5 config?
> 3. Can someone recommend a good troubleshooting guide for both SQL 2000
> and 2005?
>
|||Hello Frank,
Usually you shall identity the bottleneck of server such as memory, IO or
CPU. Also, you may way want to identify and optimize slow runing queries
that often run on your server. the following articles might be a start
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#EYBAG
10 Baselining Tips for SQL Server:
http://www.sql-server-performance.com/gv_baselining_tips.asp
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/?id=224453
If you suspect slow running queries to be causing the performance problem,
please refer to the following article:
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/?id=243589
822101 The waittype and lastwaittype columns in the sysprocesses table in
SQL
http://support.microsoft.com/?id=822101
Hope this is helpful. Thank you.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.