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 review of a robust performance monitoring tool from Quest Software, called Spotlight 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 need to have pro active monitoring that is visually friendly and alerts generated that are more granular and reliable than sql server generic alerts for several situations.
How Quest Spotlight helped us
Quest Spotlight provides a user friendly visual view of the server with all key areas.
Most of the counters are from perfmon counters interpreted in an user friendly way. You can drill into any icon for further detail. The icons typically are green if everything is normal, orange for warnings and red if there are errors or serious situations needing attention. You can drill down into any icon for further information. Such as in this case, Services shows me a red and I drill down, I can see that one of my SQL Server Agent based jobs has failed.
Spotlight screens auto refresh and managers or non SQL Server people often can take a glance at the screen to be reassured of server health and lack of issues. Spotlight also has another view for monitoring the physical server/OS.
![clip_image002[4]](http://beyondrelational.com/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/ashish/clip_5F00_image0024_5F00_thumb_5F00_3D97FDE1.jpg "clip_image002[4]")
This is very useful to cross verify if any issue has to do with SQL Server or the machine as a whole. Such as when you have multiple instances of SQL Server on the same box and one of them is consuming cpu, if you look at this view it will show you that without logging on to the server physically.You can also look at disk space, and other features that are server specific.
This is very useful to cross verify if any issue has to do with SQL Server or the machine as a whole. Such as when you have multiple instances of SQL Server on the same box and one of them is consuming cpu, if you look at this view it will show you that without logging on to the server physically.You can also look at disk space, and other features that are server specific.
Other interesting features
We have found the spotlight time log to be very useful to look into past issues particularly server issues.
![clip_image002[6]](http://beyondrelational.com/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/ashish/clip_5F00_image0026_5F00_thumb_5F00_59DB48F9.jpg "clip_image002[6]")
We also extensively use the alarms feature which we find to be lot more reliable than generic sql server alarms. Alarms are configurable for threshold and can either email via SMTP or run a custom executable as specified.
Spotlight also has an SSRS driven reporting feature which we have not explored yet but appears to be useful as well.
Conclusions
I highly recommend this tool for DBAs who need visual representation of server performance, reliable off hour monitoring/alarms and explaining situations to upper management who are not particularly techno savvy.
Additional Information