In one of my recent client visits, I was asked if an official guide was released by Microsoft that contained instructions on how to troubleshoot issues in SQL Server 2005.
Microsoft did release a few whitepapers related to troubleshooting performance problems in SQL Server 2005 and I am sharing it for those who are not aware of these whitepapers.
It is not uncommon to experience the occasional slow down of a SQL Server database. A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problem. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective actions to fix the problem. This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005.
OLTP work loads are characterized by high volumes of similar small transactions. It is important to keep these characteristics in mind as we examine the significance of database design, resource utilization and system performance. The top performance bottlenecks or gotchas for OLTP applications are outlined in the document.
Relational Data Warehouse or Reporting work loads are characterized by low volumes of very large transactions. These applications are often identified as having predominately read workloads (e.g. Decision Support, Analysis, and Reporting) with periodic feeds or batch loads. It is important to keep these characteristics in mind as we examine the significance of database design, resource utilization and system performance. The top performance bottlenecks or gotchas to avoid for Data Warehouse or Reporting applications are outlined in the document.
Apart from the ones shown above, I would also recommend users to read SQL Server 2005 and 2008 Diagnostics Queries and Configuration Checklist which contains set of Diagnostic Queries written by Glenn Berry to provide a high level overview of how your SQL Server 2005/2008 is configured and to monitor its performance.
If you know of a few more useful documents like the ones shared above, please use the comments section to share with other viewers.