Choosing the Appropriate Information to Monitor


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.

image from book
Real World Scenario-A Holistic Approach to Monitoring Performance

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

image from book



MCITP Administrator. Microsoft SQL Server 2005 Optimization and Maintenance Study Guide (70-444)
MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance (Exam 70-444) Study Guide
ISBN: 0470127457
EAN: 2147483647
Year: 2004
Pages: 146

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net