Announcing Spotlight Developer v6.5

Robert Reeves

Spotlight Developer v6.5

The key features added in this release

  • SQL Server Discovery.
    • Scanning SQL Server instances can very useful especially in large and dynamic environment. The scan engine can be filtered by constraining IP address range and/or selecting one or more network domains. Each scan result can be kept and used to compare previous scan results. Thus you are able to determine new instances and retired instances. I will go deeper into this feature in a follow up blog (stay tuned).
  • Configurable Port Numbers.
    • As you maybe aware Spotlight Developer is multi-tiered. The “client” installed into SSMS, the Diagnostic Server on your network and Spotlight Essentials’ Cloud services. The communication between these use http(s) which means tcp/ip ports are required. The default installation uses ports 443, 60000, 59883. Sometimes customer have installed other vendor software using these one or both port(s) (i.e. 60000, 59883). Thus customers had to find another machine to install. In previous releases these ports where hard coded! Now we have configurable files for both the SSMS component and the Diagnostic Server. Please contact Spotlight Developer support ( to apply non default ports.
  • Fixed an issue when the installer cannot connect to a SoSSE 11.x DS.
    • Some Customer reported difficulty configuring a connection to a Spotlight on SQL Server Diagnostic Server during the install process. The workaround is to install a local Diagnostic Server, then launch Spotlight Developer to change to the desired SQL Server Diagnostic Server via the Settings.
  • Change the support email to
    • Spotlight Developer now has its own support email address (previously shared with Spotlight Cloud services). For you the customer this change has little impact.



Announcing Spotlight Developer v6.4

Robert Reeves

Spotlight Developer v6.4

The key features added to this small release

  1. Now Supports SSMS 2016.
  2. Spotlight Developer still monitors SQL Server 2008, 2008r2, 2012, 2014 and 2016.
  3. However Spotlight Developer can only be installed in SSMS versions 2012, 2014 and 2016.
  4. Enhancements to SQL Tuning and Analysis

If you have not investigated the SQL Analysis and Tuning features, checkout this short video (by clicking the image)  to view many of its features


As far back as v6.2 a new VISUALIZATION PLAN was implemented. The video demonstrates some animation features to help identify top costly operators – useful with large plans. For example the diagram below shows top two operators with surrounding glow to emphasize their location in the plan. Very cool.



Please leave a comment or questions.


Spotlight Developer v6.3 release

Robert Reeves

The much anticipated Spotlight Developer v6.3 has finally been released. Over the next few blog entries I will present some of the key features especially new ones in this release. (Note v6.3 will not install in SSMS 2016).

New great features for this release include Integration with Wiat-o-pedia, muliple users on the same machine and enhancements to SQL Analysis.

For me the single most important new features is sharing Spotlight on SQL Server Enterprise’s Diagnostic Server with Spotlight Developer. This allows Spotlight Developer users to connect to a Spotlight on SQL Server Enterprise Diagnostic Server, thus all the connections being monitored by Spotlight on SQL Server Enterprise are now visible to all Spotlight Developer’s screens and more.

To simplify typing and reading I will use these acronyms

  • Spotlight on SQL Server Enterprise desktop client -> SoSSE
  • Spotlight Developer SSMS plugin-> SpotDev
  • Diagnostic Server -> either SpotDev-DS or SoSSE-DS
  • SQL Server Management Studio->SSMS


The simplest configuration where one SOSSE-DS is shared by both SoSSE and SpoDev, as per diagram

Simple Setup

This setup can be achieved while installing SpotDev or within SpotDev settings. Lets look at the install steps.

Configuration using Installer.

First there must be an installed and running SoSSE and SoSSE-DS (I assume the SpotDev user has obtained the name of SoSSE-DS server).

During SpotDev installation the following screen is reached, at this point the user can choose to not install a Diagnostic Server rather enter or select a SoSSE-DS for connection. The name of the diagnostic server is the hosting server name. Please confirm with SoSSE as to the name or address if needed.



The installer proceeds to install SpotDev into the SSMS as per usual, but does not install a Diagnostic Server as it is now configured to use the entered or selected SoSSE-DS.

So what do I get for connecting to SoSSE-DS

Launch SSMS following the above install. Open the HEATMAP (Spotlight->Heatmap) where the user should see a list of connections and a Heatmap for the connections as monitored by SoSSE. The alarms and the monitoring screens are fully available  plus the workload analysis and wait events (more on these new screens in another blog).

Currently (ie this SpotDev release v6.3) the connections being monitored cannot be managed by SpotDev. Adding or removing connections must be achieved via SoSSE.

While connected to SoSSE-DS, none of the connection settings are SpotDev, The relationship is simply read-only.

Using the Settings feature I can set the SoSE-DS or SpotDev-DS that are available on my network.

So both DBA and Developers can share monitoring of SQL Server instances with less overhead and increased flexibility.

Till next blog entry – please feel free to leave a reply.







Spotlight on SQL Server 11.5 has arrived!

Pavel Chuchuva

The latest version of Spotlight on SQL Server Enterprise includes powerful new features that enhance your ability to monitor your SQL Server environment.


With Version 11.5 you can

  • Create a customized “view” of your connections.
  • Apply configurations to multiple connections at once.
  • Locate connections faster and more easily using our new search tool.
  • Send push notifications to Android and iOS mobile devices.
  • Send Spotlight alarm notifications to a PagerDuty dashboard.
  • Monitor SQL Server 2016 instances.



Announcing Waitopedia

Pavel Chuchuva

We are happy to announce a new waits knowledge base, Waitopedia.

Waitopedia is a comprehensive resource of information about SQL Server waits. It combines data analysis with descriptions and insights that are edited by the community.

The analysis is based on data collected from SQL Server instances and uploaded by Spotlight Essentials users.

For each wait you can see its category and SQL Server versions that it can occur on:wait-category-versions

You can also check how common a given wait is. For example, PAGELATCH_EX is very common:pagelatch-ex-very-common

Whereas PREEMPTIVE_COM_GETROWSBYBOOKMARK is very rare:preemptive_com_getrowsbybookmark-very-rare

Also you can check wait latency – how much time does the wait take for our users:latency

Waitopedia is a collaborative resource. We seeded wait descriptions with the Microsoft documentation, but sometimes the waits are undocumented. Please go over to Waitopedia and provide information you know, or join the discussion!

Check out Waitopedia

Version 3 of Spotlight Mobile App for Android

Pavel Chuchuva

The new release of Spotlight mobile app for Android brings you server performance metrics. Now you can view performance diagnostics similar to those on the home page of Spotlight on SQL Server Enterprise.


You can tap on a metric to see any associated alarms and a chart displaying recent historical data.


Note that you will need at least version 11.0 of Spotlight on SQL Server to get these new features. You can get it at the Quest website.

Get mobile app now

Get it on Google Play

Android App Now Has a Widget

Pavel Chuchuva

By popular demand we have added a widget to Spotlight app for Android. You can add the widget by following these steps:

  1. Tap the Apps button.
  2. Swipe past the apps until you reach the Widgets section.
  3. Long press the Spotlight Widget.
  4. Drag and drop onto an available home-page position.

The widget looks like Spotlight logo. It changes its color depending on the severity of the alarms that you have in your environment. widget

By looking at this screen I know that I have high severity alarms. The widget updates itself once an hour. Hopefully it’s going to be green for you most of the time.


Get the new version of Spotlight app at Google Play.



Monitoring a Tempdb Database in Spotlight on SQL Server 11.2

Pavel Chuchuva

Version 11.2 of Spotlight on SQL Server Enterprise adds monitoring tempdb database.

The new tempdb status is located in the disk storage section on the home page.

The new drill-down conveys at a glance how much space is being consumed by tempdb.



Let’s say I set up this test database:

USE IsoTest;
CREATE TABLE dbo.marbles
    color CHAR(5)
INSERT dbo.marbles VALUES ( 1, 'Black' );
INSERT dbo.marbles VALUES ( 2, 'White' );

Next, I execute a massive number of updates – this will trigger version store growth:

use IsoTest
set nocount on;
DECLARE @i int = 0
WHILE @i < 2000000
    SET @i = @i + 1
    UPDATE dbo.marbles SET color = 'White' where id = 2
    UPDATE dbo.marbles SET color = 'Black' where id = 2
    IF @i % 1000 = 0
        print @i

In a minute or two I get an alarm from Spotlight: ‘the tempdb version store creation rate is high compared to cleanup rate’.


If I click ‘Diagnose’ button I can clearly see that most of the space in my tempdb database is consumed by version store (yellow):


Another Example

Spotlight gives me a helpful hint to disable auto-grow of tempdb database:


I go to Management Studio, open tempdb properties, switch to the Files tab and set autogrowth to none:


Next, I create another table:

USE IsoTest;
CREATE TABLE dbo.posts
      id INT PRIMARY KEY ,
      body nvarchar(4000)
INSERT dbo.posts VALUES  ( 1, 'Hello world');
INSERT dbo.posts VALUES  ( 2, 'Hi there');

Then, I start a transaction:

USE IsoTest;
UPDATE dbo.posts SET body = 'Hello updated world' WHERE id = 1

Oops, I forgot to close that transaction. Let’s see what happens if I execute a lot of updates in the posts table. I create a new query in Management Studio – this will start another session:

use IsoTest

set nocount on;
DECLARE @i int = 0
WHILE @i < 2000000 BEGIN
    SET @i = @i + 1
    UPDATE dbo.posts SET body = REPLICATE('hello', 800) where id = 2
    UPDATE dbo.posts SET body = REPLICATE('bye  ', 800) where id = 2
    IF @i % 1000 = 0
        print @i

Now cleanup cannot kick in because there is an open transaction. So version store grows and grows. Luckily, Spotlight saves the day and raises an alarm:


If I click the Diagnose button I can see that tempdb has only 0.94 MB free:


I can click on tempdb database to see usage break-down:


I can clearly see that version store is growing too big again.





See also: Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: a Guide

Version 2 of Spotlight Mobile App for iPhone

Pavel Chuchuva

We are happy to announce the new release of Spotlight mobile app for iPhone. Now you can view performance diagnostics similar to those on the home page of Spotlight on SQL Server Enterprise.


You can tap on a metric to see any associated alarms and a chart displaying recent historical data.


Using historical playback, locate the root cause of an alarm by viewing performance diagnostics at the time it was raised.


Note that you would need at least version 11.0 of Spotlight on SQL Server to get these new features. Get it at Quest website.

Get mobile app now


Version 5 of the Spotlight Essentials Plug-in for SQL Server Management Studio

Pavel Chuchuva

Version 5 of the Spotlight Essentials Plug-in for SQL Server Management Studio includes real-time Performance Health. This powerful new feature examines the wait events occurring on an instance to determine its overall health.

New features in Version 5

  • Real time Performance Health displayed as an info-graphic report.
  • Install, or connect to a Diagnostic Server remotely so if you’re running SQL Server Management Studio on a machine without internet access you can connect to a Diagnostic Server that does.
  • By connecting multiple users to one remote Diagnostic Server users can have a shared view of their data.

I/O Latency Graph

I/O latency chart

The latency of single page read is a good indicator of the responsiveness of the I/O sub system.

Download plug-in