There is no substitute for knowing your stuff. It’s as simple as that.
When designing a monitoring strategy, you should have in mind the factors that can affect performance. This is important because you need to correctly identify which factor is causing the performance problems before responding to it. In the case of enterprise environments, this might involve bringing in specialized information technology (IT) personnel.
As we discussed in depth in Chapter 1, you can isolate the different components of the client/server architecture into the following layers or components:
Hardware
Network
Operating system
SQL Server database engine
SQL Server components
Database
Client application
An effective monitoring strategy should evaluate the entire database solution, taking into account the history of the database solution. Sometimes the performance problem can be a number of steps removed from the SQL Server database engine. For example, a web application can be poorly written and lead to the poor performance of SQL Server 2005 because of excessive network traffic.
So, remember to not draw any conclusions early and to take a holistic approach.
I was asked to come in for one particular client to help troubleshoot a performance problem. They had implemented a performance monitoring strategy for a number of their SQL Server 2005 instances.
To minimize the overhead of their monitoring strategy, they had decided to monitor only the absolutely essential performance object counters. But they overdid it. They captured too little information, which made it more difficult, or virtually impossible, for a root cause analysis of the potential problem.
As an example, they decided to monitor the Processor(_Total) : % Processor Time performance object counter, but not the Process(sqlservr) : % Processor Time performance object counter, so it was impossible to determine what the cause for excessive processor utilization was-SQL Server 2005 or the Windows operating system.
Don’t forget that the various subsystems are interconnected, so it is important to capture a representative set of metrics from all the subsystems.
-Victor Isakov