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:
- Which DMVs to use and when
- Sample queries that go beyond the DMVs
- Stored procedures that give you additional insight including:
- Index statistics
- 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.
- 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: 2007-01-31
About the author
View all my tips