Summary

In this chapter, you looked at how you troubleshoot performance problems in a SQL Server 2005 environment.

You first looked at the complexity of the client/server stack and the need to adopt some sort of structured approach when troubleshooting performance problems in SQL Server 2005.

You looked at the various Windows and SQL Server tools that can potentially help you troubleshoot performance. It is important to be aware of what tools exist and when to use them.

You then looked at how you typically narrow down the performance problem by initially examining the physical server and then the SQL Server 2005 instance before finally drilling down into a specific database.

After that you looked at how when you troubleshoot performance at the physical server level, you initially need to identify what subsystem is experiencing the bottleneck before trying to determine whether it is the Windows operating system or the SQL Server 2005 instance that is responsible.

The next topic was how to troubleshoot the SQL Server 2005 instance by going through the same processor, memory, and I/O subsystems at the Windows level, but this time with the various SQL Server 2005 tools and commands.

We also discussed the importance of the tempdb system database to SQL Server 2005, when it is used, and how to monitor it.

Finally, you looked (at a high level) at how to troubleshoot performance at the database level. You did not look at how to troubleshoot queries and indexes within this chapter because this topic will be covered in detail in subsequent chapters.