Spotlight on SQL Server for New or Non-DBAs

If you're in charge of SQL Server, my experience as an SC tells me that there's about a 50% chance that SQL Server administration is only one of the roles you play in your organization.  It seems that there is a bit of a shortage of DBAs in the market, and that many companies don't want to hire someone to wear solely the DBA hat anyway.  I've seen Network Admins, Developers and others who, having been given responsibility for their shop's SQL Servers, are in need of a tool that can help. With an intuitive interface that brings all of SQL Server's critical performance indicators together into one place, Spotlight on SQL Server is just such a tool:

 

What you're seeing above is really what Spotlight on SQL Server is all about: Everything You Need to Know in One Place!  This matters because while SQL Server itself does make performance indicators available through some native utilities, that information is spread across a number of different places (Management Studio, DMVs, Extended Events, Perfmon, etc.), so it's hard to get a quick, big-picture understanding of where there are issues in your SQL Server environment.  Additionally, the information in the native utilities is not always in the most usable and actionable format, requiring you to do some processing to get much out of it (and even if you are adept at navigating the native toolset and processing the data from it, the time it would take you to do so is better used elsewhere).

Spotlight can actively notify you exactly where the problems are with an email or push notification to your mobile phone, and then provide you clear navigation from a global view of your environment...

...which highlights using color and tile size which systems are the worst off, and then with one click takes you to a dashboard for that system:

As you can see above, once you're on the dashboard of the system in question, you'll see that the component representing the part of your SQL Server that is having issues will be colored something other than green.  It will be yellow for low level alarms, orange for medium and red for critical.  Clicking on the component will give you a popup that describes the problem in general, gives you specifics around where and how it's appearing in your environment and will often provide some suggestions for resolving the issue.  From there, a click on the Diagnose button will take you to the appropriate detail page (we call them Drilldowns) for the problem at hand, which in this case is the Database Drilldown, and specifically the Fragmented Indexes tab:

Now you know, down to the level of exact objects and/or processes, where the problem is coming from.  Keep in mind that this is just one example of a metric that we can alert you on.  There are hundreds of data points that Spotlight collects, and various drilldowns that Spotlight will lead you to when an alarms occurs on different metrics.  The SQL Server Drilldowns pane in the navigation ribbon at the top of the Spotlight user interface lets you browse the variety of detail screens we provide.

That covers Resources, so now let's talk about Statement Analytics

Hopefully this has demonstrated to you how Spotlight helps the new or Non-DBA by providing a workflow that includes some context and guidance so that you're not lost in a sea of raw metrics that you don't know how to interpret or use. But that's just really just half of what Spotlight has to offer. What we've just covered is the functionality Spotlight provides for operational monitoring - in other words, watching for bottlenecks on your SQL Servers' resources so as to alert you in real-time to performance and up-time threats, and then provide you a fast way to identify the root cause so you can take action.

There's another important aspect of database administration that Spotlight handles beautifully, and that's watching all of the statements executing against SQL Server and then giving you a clear breakdown of what type of resource consumption they caused, and enabling you to see those statements along with the context of the user and application that executed it, and the database hit by it.  This helps you know what statements you might want to consider optimizing. I'm talking about Spotlight's Wait Events drildown:

The link to the Wait Events drilldown in inconspicuously placed in the SQL Server Drilldown pane as if it were just another drilldown, but make no mistake, this is information distinct from what we've seen so far, and that could be (and in the past was) worthy of a separate application.  That's because it really has a different purpose than the rest of Spotlight: rather than focusing on resources in real-time, Wait Events helps you see which statements have been the heavy-hitters in your environment, over various time ranges that you might want to explore.  Let's take a look:

In the screenshot above, you can see in three simple steps how to use the Wait Events drilldown (please double-click the image to get a better view).  First, you select a time range (from that last 1 or more weeks) that you'd like to explore.  Second, you expand out a dimension from the tree control on the left.  Third, you observe the data in the dimension you're viewing.  In the example above we are simply looking at the last 15 minutes, and then by expanding the Statements dimension are able to see what executed during that time.  The table in the middle of the screen is an ordered list with the heaviest statements right at the top.

Please notice the "% of Total Wait Time" column marked as step 3: that's our sort column.  As a new or Non-DBA, you might not be familiar with Wait Statistics, but the good news is you don't need to be.  One main purpose of this post is to let you know that you can really just think of this column as the % of the total LOAD that (all executions of) each statement put on the SQL Server instance during the selected time.  Again - the heavy hitters are at the top, so if you're looking for the statements that would give you the best tuning ROI, there they are.  When you're ready for a more detailed look at Spotlight's Wait Events drilldown (and a related drilldown called Workload Analysis) click here.

So the take-away from this post is to remember that Spotlight on SQL Server provides two distinct types of workflow useful to you as a DBA: One to help you keep an eye on real-time performance, and another to help you discover opportunities for optimization in the T-SQL code running against your SQL Server.

Anonymous