Spotlight on SQL Server Enterprise‘s main purpose in life is to inform you of SQL Server performance problems, and then give you a quick way to diagnose their root causes so you can take action quickly.  Email and mobile app notifications along with an intuitive, clickable dashboard-style interface make Spotlight a humdinger of a tool for Operational DBAs:

But for a couple of years now, Spotlight has some screens that are super-duper for SQL Developers as well, in the form of the Wait Events and Workload Analysis drilldowns:

The Wait Events drilldown lets you see the workload on an instance for any time range within the last week or more.  You can see the statements that made up this workload, ordered from heaviest to lightest, along with the exact amount of workload each statement contributed to the total, down to the exact millisecond and percent.

The Workload Analysis drilldown is a very similar interface but lets you observe queries grouped and sorted by a different set of metrics,including query duration.

So these screens provide great information for anyone who creates SQL or is looking to find heavy statements in their environment to tune for better performance.

The Spotlight for SQL Server User Interface: Spread it Around

With information that’s so useful to the SQL development side of the house, you might wonder whether it will cost you an arm and a leg to give those folks access to these screens.  The good news is that there is no per-seat charge with Spotlight on SQL Server – after you’ve licensed it for the SQL Servers you’d like to manage, you can go hog-wild and give the Spotlight client to everyone in your company, even the toddler/intern!

But before you go willy-nilly giving everyone in your shop access to Spotlight on SQL Server it’s important to consider who you would like to be able to configure it, as well as take actions on SQL Server through it, such as killing a session:

You should be persnickety about who you give this capability to, or you might find some whippersnapper creating befuddlement in your environment.

The Spotlight on SQL Server user interface: Deciding Who can do What

The “Kill this session” option in the screenshot above should get your attention, because not everyone should have that capability.  Luckily with Spotlight, you can decide who can take actions like that, and who can configure and customize Spotlight. And not to worry – giving someone access to Spotlight with the correct permissions doesn’t involve a lot of rigmarole – it just takes two steps:

1. Install the Spotlight client to their workstation, and

2. Find the three Windows local users groups that the Spotlight installation process created on the machine hosting your Diagnostic Server, and put the new user’s login in the appropriate group:

• Those in the Spotlight Diagnostic Read-Only group can’t configure Spotlight or take actions on SQL Server through it.
• Spotlight Diagnostic Users can configure Spotlight.
• Spotlight Diagnostic Administrators can both configure Spotlight and take actions on SQL Server through it.

Bonus Info: Who changed that?

So there it is!  Now you know how to let people use Spotlight without granting them the ability to do all kinds of tomfoolery with it.

But even if there are others in your shop whom you trust to be configure Spotlight, you might still want to track who is doing what.  You can do this by running Spotlight's Audit Report.

This report will tell you what Spotlight configurations were changed to, and who did it, and when.

Now go spread Spotlight around!

All of my Quest Database Performance blog posts can be found here.