How do I... Install and configure Spotlight on SQL Server?

For database administrators, peak performance of their SQL Servers is always a priority. Spotlight on SQL Server from Quest Software is a tool to help SQL Server DBA's monitor the performance of their SQL Servers, and be alerted when there are problems. It provides valuable information in an easily accessible format. Josh Hoskins shows you how to install and configure Spotlight on SQL Servers.

Spotlight on SQL Server from Quest Software is a tool to help SQL Server DBA's monitor the performance of their Microsoft SQL Servers, and be alerted when there are problems. It provides valuable information in an easily accessible format.

This includes not only data about operations happening in your SQL environment such as your cache hit ratio, and pages in memory, but also operating system level information such as disk queue length, page file usage, and all process running on the system, not just SQL processes.

This full featured program can monitor multiple SQL instances simultaneously (multiple simultaneous monitoring is only available in Enterprise edition) without the need to install software on your SQL Servers, though you will need a SQL Server to house your work databases.

This blog post is also available in PDF form as a TechRepublic download.


After starting the installation wizard (Figure A), your first step in installing Spotlight is to choose the server you will use for your Diagnostic Server. This is the SQL server that will house the database that is used for collecting data from all of the SQL Servers you wish to monitor (Figure B).

Figure A

Installation wizard

Figure B

Diagnostic Server

The workload given this server will vary based on the SQL servers you are monitoring. Even though the overall footprint is fairly light, you should attempt to make your Diagnostic Server its own server if you are monitoring a significant number of servers, or at least a server that has a very light workload. You will only need one Diagnostic Server for your network, as multiple clients and servers can connect to it.

When you choose to install your Diagnostic Server you can not only install it locally, but also remotely to any SQL Server you have access to. You need to verify that every SQL Server you wish to modify can also access the chosen server (Figure C). If you are testing Spotlight, feel free to use a local SQL Developer install or even SQL Express on your local box. Again the footprint will be light, but there will be some overhead to running it.

Figure C

Remote server
Once you have finished your install you will need to make your initial connection to a SQL Server (Figure D). These connections will be to the servers that you wish to monitor. You will need to use credentials for both the SQL Server and the OS on which the SQL Server resides. You can use the credentials you used for your Diagnostic Server, or you can specify different credentials for each.

Figure D

Initial connection

This is useful if you have your SQL Server spread across multiple domains, or use various workgroup versions of SQL Server. You will need to create a database on your monitored systems. A database named QuestWorkDatabase will be created by default using your SQL Server defaults. If you wish the database to be created or named differently, you should create it manually in SQL Server ahead of time.

You will next need to set up the initial calibration for your server (Figure E). This process takes a baseline of the operation of your server. The initial time to do this is an hour, and this is recommended as a minimum. While you can use a shorter period, you will not have as good a data sample for your baseline. You can also use a longer period to get a better overall baseline. The calibration can be re-run at any time.

Figure E

Initial calibration
Once your calibration is begun, you will be presented with your current activity screen (Figure F). On this screen you can see the current status of many aspects of your SQL server. This page is continually updated with new information, and is constantly in motion. Items that are at a safe level will show in green, items that are at a warning level will show in yellow, and items that are at an alarm level will show in red.

Figure F

Current activity

You can right click on any item on screen to see menu options relating to that item. This is very useful, as it can be very difficult to monitor this information in SQL itself without using PerfMon. And using PerfMon has its own drawbacks; because many of the counters have differing levels of measurement, it can be very difficult to make sense of the data.

You can also modify what levels Spotlight considers safe, warning, and alarm in the Server Configuration tab (Figure G).

Figure G

Server Configuration

You can also customize many other settings in Spotlight. You can specify a benchmark query to run, and the acceptable return rate on that query. You can set what types of events from the SQL Server logs you wish to be alerted to, specify any filters to the data Spotlight collects, and even publish HTML reports to a Web server, so that the performance of your environment can be seen or even distributed. Because you can change the drop folder for each server, you can use the security on your Web server, or separate Web servers, to segregate who has access to what reports.

By right clicking on an individual server in the console you can set the e-mail alerts for that server (Figure H). This allows you to customize the alerts so that different persons can be alerted to various events on servers.

Figure H

E-mail alerts

This is useful if you have multiple DBA's responsible for different systems, or if you want set recipients based on alert levels, such as low level alerts go to Jr. DBA's while sever alerts go to Sr. DBA's. Or if you have a Replication manager, you can have replication alerts go to him, while any other alerts go to your DBA's.

You can also view a page of data similar to your SQL data, but for Windows performance (Figure I).

Figure I

Windows performance

This is very useful, as many DBA's may not have OS level access to these systems, but system performance often is a critical factor in SQL Server. Having this information is great benefit to DBA's as it allows them to either pinpoint or rule out system performance when tuning their SQL Server systems.