Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Troubleshooting Performance Problems in SQL Server 2005

By:   |   Updated: 2007-01-31   |   Comments   |   Related: More > Performance Tuning

Improving SQL Server Queries by Reading and Understanding Execution Plans

Free MSSQLTips Webinar: Improving SQL Server Queries by Reading and Understanding Execution Plans

In this webinar we will cover how to use the tools SQL Server provides to capture execution plans, how to read an execution plan and how to write better queries to improve performance.

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.

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

Last Updated: 2007-01-31

get scripts

next tip button

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Learn more about SQL Server tools