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

sql-tuning-video

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.

 

PlanVisualization

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.

SetupDS

 

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 available.to 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.

views

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.

views-detail

Download

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.

homepage

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

processes

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 Dell Software 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.

tempdb-space-used-chart

Example

Let’s say I set up this test database:


CREATE DATABASE IsoTest;
GO
ALTER DATABASE IsoTest SET READ_COMMITTED_SNAPSHOT ON;
GO
USE IsoTest;
GO
CREATE TABLE dbo.marbles
(
    id INT PRIMARY KEY,
    color CHAR(5)
);
GO
INSERT dbo.marbles VALUES ( 1, 'Black' );
INSERT dbo.marbles VALUES ( 2, 'White' );
GO

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


use IsoTest
GO
set nocount on;
DECLARE @i int = 0
WHILE @i < 2000000
BEGIN
    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
END

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

tempdb-alarm

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

tempdb-high-version-store

Another Example

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

auto-grow-issue

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

database-properties

Next, I create another table:

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

Then, I start a transaction:

USE IsoTest;
GO
BEGIN TRAN
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
GO

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
END

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:

space-used-alarm

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

databases

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

high-version-store2

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

 

 

Download

 

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.

homepage

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

processes

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

disk-storage

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

Get mobile app now

download-on-the-app-store

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

Version 11 of Spotlight on SQL Server Enterprise

Pavel Chuchuva

Version 11 of Spotlight on SQL Server Enterprise includes powerful new features that enhance your ability to monitor your SQL Server Environment.

New features in version 11:

  • Examine the amount of resources being used by SQL Statements and processes using Workload Analysis.
  • You can now compare database performance over time in all line charts with Baselining.
  • Send messages to an SNMP manager as an alarm action.

workload analysis in Spotlight on SQL Server Enterprise

Download