Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Troubleshooting Performance Problems in SQL Server 2005


By:   |   Read Comments   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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


Last Update:


signup button

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools