[EN] Sunday Market–SSMS Spotlight Plugin by Dell (part one)

It Sunday again. And some shops are closed. If Yes, it’s time to get next useful tool for free. It’s my episode #07 in non-frequently series: Sunday Market. This time we will look for Dell. Inspired by online session of my good friend from England: Richard Douglas {TWITTER|BLOG|LINKEDIN} I want look for Spotlight Essentials Plugin by Dell. Richard delivered session: Wait Watchers – No pain, all gain exercises to increase database performance at PASS Database Administration Virtual Chapter and it was very interesting. Interesting enough to look for this plugin. It is free, so we can get in on Sunday, just like today. This post will be divided in two parts: one for basic installation – till the moment “"’I got it!’ and second with more configuration options. So let’s install this plugin.

Phase One. Getting the plugin.

You should go to Spotlight Plugin site: https://www.spotlightessentials.com/sql-server/instances 


Pict. 01 | Intoructionary picture of plugin


Pict. 02 | Registration form for account using Spotlight in future


Pict. 03 | Downloading plugin (ca 70 MB)


Pict. 04 | Registration for Spotlight Essential Web Account (if you registered before new geristration is not necessary)


Phase Two. Installation of plugin.


Pict. 05 | Welcome screen (same type like Visual Studio 2013 isn’t?)


Pict. 06 | Transaction Product Agreement – in normal words license.


Pict. 07 | Installation of Diagnostic Server with specify Diagnostic Server Service Account


Pict. 08 | Using special dedicated account SQL Spotlight will bring errors with permissions, but it work and is best way in my opinion.


Pict. 09 | Configuration os specific Payback Database which will store recent and current data (we can choose special instance or the same instance as production server)


Pict. 10 | If desired instance are not connected or not running, we will get error.


Pict. 11 | In the case of clicking NEXT at previous screen we will get error that Specific Database not exist


Pict. 12 | When we click CREATE on screen (at picture nine) we can creat Specific Database


Pict. 13 | Specific Database created succesfully


Pict. 14 | Installaton started…


Pict. 15 | …and finished. Aproximately time is less than five minutes.


Phase Three. First view in SQL Server Management Studio.


Pict. 16 | Specified Database appears in databases tree: SpotlightPluginPlayBackDatabase


Pict. 17 | We can docking Spotlight Plugin in a tab. But we can’t connect to Diagnostic Server if our Spotlight plug-in Diagnostic Server Service didn’t run.


Pict. 18 | We can check Services and even we see that service is running, we can STOP and START again it.


Pict. 19 | First view for our plugin in General Tab


Pict. 20 | Next step is connecting instance to Spotlight Essential Plug-In Web Account (I will write more about in in next post)


Pict. 21 | Time for connect first instance. In my case is the instance with Specified Database


Pict. 22 | And here we see status of connections: to Diagnostic Server and to Instance


Pict. 23 | First detailed tab: Overview Tab with Waits, Top Databases Files IO, Batch Request, Summary, Connections, CPU Usage and more.


Pict. 24 | Second detailed tab: Sessions with sessions status, activity last command and over thirty other details about sessions


Pict. 25 | Third detailed tab: Resources Waits with Wait Time Trend ans Waits Details


Pict. 26 | Fourth detailed tab: Data File I/OS with general data file infor for each data file and system databases


Pict. 27 | Fifth  detailed tab: TOP SQL  with detailed information about last TOP 25 SQL Queries


Pict. 28 | After few minutes of work we can see more events on our General Tab where alarms are sorted by Severity


Pict. 29 | And more and more alerst after next few minutes.

Screenshot 2015-01-04 18.17.01

Pict. 30 | We can add more instances for Diagnostic Server


Pict. 31 | But we can’t connect with SQL Azure Instance if we just click without changing authentication type


Pict. 32 | When we use SQL Authentication, connection is succesful


Pict. 33 | And we can see all instances in General Tab


Pict. 34 | In General Tab we see status of our instances in colors which based on health status

Screenshot 2015-01-04 18.47.05

Pict. 35 | When we move mouse in General Tab over one of many instances we will see pop-up windows with short information of errors

Screenshot 2015-01-04 18.48.51

Pict. 36 | And those errors will be different, they change in time

Screenshot 2015-01-04 19.01.23

Pict. 37 | Same pop-up windows we will see in Overview tab on each instance


That’s All Folks. Get the plugin, istall the plugin and configure the plugin. From this point You can test this plugin and start to play with this nice tool. More details about configuration, errors, connections and how useful this plugin could be in next post.