Malathi Mahadevan works as a Senior DBA with Kindred Healthcare – a healthcare services company based out of Louisville, KY and ranked first in Fortune magazine's Most Admired Companies "Health Care: Medical Facilities" category. She is the founder-lead of the local chapter of Professional Assn of SQL Server and have 12 years of experience using SQL Server.
Introduction
This is a ReALview™ of a robust performance monitoring tool from Quest Software, called Quest Performance Analysis for SQL Server.
The Environment
The environment I work on comprises of approximately 150 SQL servers, 50 each on Dev/QA and Production. Majority of these servers are on SQL Server 2005, some are on SQL Server 2000 and two of them are on SQL Server 2008. Being a healthcare company we have very sensitive data and high expectations on system uptime.
The Challenge
We often have to provide back dated reports on performance or troubleshoot performance issues that happened sometime ago when they come to light by developers or other concerned people. We also have to give comparative performance reports on server performance before and after certain critical changes are done. Also given the number of servers we have we really need an interactive tool that shows graphically how the server is performing on a minute to minute basis.
How Quest Foglight Performance Analysis helped us
Quest Performance Analysis is a very unique tool that is oriented towards monitoring and trouble shooting query or TSQL heavy SQL Servers. The interactive interface that it provides allows the DBA to see how server is performing on a continues basis and dig down into problem areas.
The screen you see above is the workload summary (each resource has its own drilldown on details of resource usage). The second interactive view is the real time graphic summary below, which has the breakdown of each resource in terms of wait times. It is also possible to see this breakdown in terms of each component of the workload such as CPU, I/O, Memory and so on. The ‘current sessions’ will tell you the sessions that are contributing to this wait.
These screens autorefresh so all a DBA needs to do is to keep them up on the monitor and look at it. For us this has saved valuable time since it is proactive performance monitoring, and we have many times found problems such as a run away query before users report performance issues. We also have been able to show them graphically how much their query is straining the system and why it needs to be tuned/stopped
Other interesting features
PA has a huge number of very useful features. Some we use more commonly is History
It stores a history of queries, wait times and so on which is much longer than what you get with SQL Server DMVs. The time period is configurable. We keep it 90 days. This helps us to answer any questions on past performance accurately.
There is a huge number of reports you can generate from PA. The ones we commonly use are the Executive Summary and the Top n Resource consuming Queries.
We also run a ‘comparison’ from time to time of resource consumption from one time frame to another. We have found that to be hugely useful after significant changes have been made to the server.
Conclusions
I highly recommend this tool for busy DBAs who have to monitor multiple servers or have query intensive servers with frequent problems. It is worth every $ spent on it and Quest offers very good tool support also.
Additional Information