Troubleshooting Performance Problems in SQL Server 2005

By: Greg Robidoux | Read Comments | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: More

Problem
With the introduction of SQL Server 2005 came many changes.  One of these dramatic changes was how to find and troubleshoot performance issues. SQL Server 2005 introduced dynamic management views which gives you insight into various aspects of SQL Server.  A previous tip was written about DMVs to introduce the topic as well as highlight some of the DMVs.  Even thought Microsoft has exposed a lot more performance related information in the DMVs, interrupting the results or even knowing what to do is still a problem.  Although there is not a hard and fast rule as to what to do, I did come across a great whitepaper that does shed some light as to what can be done and also how to interpret some of the results.

Solution
There is a whitepaper called Troubleshooting Performance Problems in SQL Server 2005 which was written by several people that delves into real world performance issues that you might face and how to diagnosis the problem with the tools that Microsoft makes available with SQL Server 2005.  The paper is lengthy (104 pages), but does give you great insight into these new components and how to use them when troubleshooting SQL Server 2005 performance issues.  There is an online version as well as a Word document which you can download from the above link.

The paper explores several areas, but some of the key points that it touches upon include:

• Bottlenecks
  • Resources
  • Memory
  • I/O
  • CPU
• Which DMVs to use and when
• Sample queries that go beyond the DMVs
• Stored procedures that give you additional insight including:
  • Blocking
  • Index statistics
  • Waitstats
• Performance Monitor counters to use 
• TempDB troubleshooting
• Setting and using "Blocked process threshold"

This paper may not giving you the insight on how to solve your problem, but this will definitely give you more tools to use to help identify and diagnosis the problem.  There is still not a lot written about the DMVs, so anything you come across is worth reading.

Next Steps

• If you are looking for a good resource to jump start your knowledge of SQL Server 2005 and how to troubleshoot performance issues take the time to read this document to better understand the differences with SQL Server 2005.
• Review the online version of download the Word doc and add it to your library of resources: Troubleshooting Performance Problems in SQL Server 2005

Related Tips: More | Become a paid author

Last Update: 1/31/2007

Share:

Share

Tweet

Free SQL Server Learning Waiting on Waits Let's connect on facebook! Tweet...tweet...tweet... Follow us on twitter

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL doctor ROCKS! As soon as I ran it, problems that have been giving me headaches were identified and cured." SQL Monitor – For database professionals who need results on Day One. Try it online. Need SQL Server help and not sure where to turn? Reach out to the Edgewood consultants for a Health Check. Get SQL Server Tips Straight from Kevin Kline. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Free Learning - Introduction to SQL Azure Delivered by Herve Roggero on Wednesday, June 13 @ 3:00 PM EST