Deep analysis of Microsoft® SQL queries and VMs

by Alain Geenrits on May 8, 2018

Microsoft SQL analysis is getting deeper

  • Onwards and upwards

We at Blue Medora have just launched a major new version (v2.2.0) of our management pack for Microsoft SQL (MS SQL) Server. There is also a minor follow up release v2.3.0 that is planned around mid May which contains efficiency improvements.

  • Detect MS SQL server and VM relationships

The new version v2.2.0 enhances VMware vRealize® Operations Manager with more than 250 metrics collected from your MS SQL server instance, provides more than 30 alerts and capacity definitions for connection limit, CPU demand, memory usage, and disk capacity. 7 dashboards help you monitor your environment out of the box. Most importantly, we detect relationships between MS SQL server and the VM layer, giving you access to a comprehensive full stack view.

  • Creme-de-la-creme – Query plan support 

The single most exciting new feature is the support for query plans!

  • Performance and efficiency improvements

You need to get v2.3.0 to get these. By default, the plugin excludes index event based queries and metrics collection as it is resource intensive. Also provides the option of disabling specific alerts. The out of the box plugins provide better performance due to these improvements.

Installation and requirements
Installation is straightforward as you need to upload one file in vROps and configure an instance of the management pack per database instance. You only need a host or instance name and a least-privileged user to access the environment. Did I say NO agents? Really! We use a JDBC connection to get data out. All the characteristics can be tweaked if necessary in the advanced settings (figure 1).

Figure1. Managing the solution

A lot of customers told us they need a more automatic install of the adapter instances, since they have hundreds of MS SQL server instances. Well, we have been listening and I am very excited that the new version supports a vRealize Orchestrator workflow to install automagically! This workflow can read a CSV file to install instances without intervention. You can download the workflow for free and get all documentation in this KB article.

The new version adds support for MS SQL 2017, while still supporting old versions from 2008R2 onwards. Availability groups are supported of course as are cluster installs. On the vROps side you need at least version 6.3.

Query plans – Improves Sr. DBA’s & DBA’s productivity
The single most exciting new feature must be query plans! Previous versions of the management pack already supported queries. You could see queries with all metrics such as execution time and you could even see the actual query text. (see figure 2).

Figure 2. Microsoft SQL queries analysis

Microsoft has the possibility in MS SQL studio to visualize query execution plans. Microsoft SQL plan definition: “The execution plan describes the sequence of operations, physical and logical, that MS SQL Server will perform in order to fulfill the query and produce the desired result set”. (see Technet).

A use case that customers presented us is that of a senior DBA or VMware admin seeing a slow query. They want to quickly advice application owners or DBAs on what is causing the performance loss, all from their central SDDC monitoring dashboards in vROps. Well now we can! In the new “MS SQL Server query plan” dashboard the user can select a query and launch the “retrieve query plan” related task. This will retrieve the query plan and displays it in the dashboard. See figure 3.

Figure 3. SQL Server query plan dashboard

Since this is a resource intensive operation the query plan is only generated when the task is run, but not continuously. See Figure 4.

Figure 4. Retrieve query plan

Now you can analyze the cost of any query w.r.t CPU, memory, use of indexes, and more!

New dashboards – Database->VM->storage->networking relationships
A number of dashboards have been reworked and others deleted to provide even more value out of the box to you. Another use case we encountered is a DBA who needs a quick overview dashboard to tell him what is going on with his MS SQL databases and some parameters from the VM layer. The “Microsoft SQL Server DBA Overview” provides just that. See figure 5.

Figure 5. Microsoft SQL Server DBA overview dashboard

Another use case which will ring a bell with a lot of you is the DBA noticing nothing special, but the end users complaining that the app “is slow today”. Then, the finger pointing can begin to find the reason of the problem. Not so with our “SQL Server VM Relationship” dashboard! In figure 6 you see that on the left side I can chose a SQL instance, see the whole topology with relationship to all objects from other management packs like storage and networking. In the middle column you see what I call the “SQL server view”. The right column shows you the same from the virtual machine layer. I think this is one of the most useful views to troubleshoot issues across the full IT stack!


Figure 6. Microsoft SQL Server VM relationship dashboard

Conclusion
I have shown you few features of our new edition of the management pack for Microsoft SQL and few use cases. For the full documentation see our support site. To get a presentation, data sheet or to try it yourself, ask for a demo and trial – visit the MS SQL server integrations page. If you are looking to monitor Azure SQL we can do that too! See the Azure management pack and visualize all your databases on and off premise in a single dashboard!

All our management packs are also immediately compatible with the new release of vROps 6.7. Manage the whole SDDC and hybrid cloud with vRealize Operations and Blue Medora management packs!

Get started

Try BindPlane for free. No credit card required.

Sign up
True Visibility
BindPlane for VMware vRealize Operations

True Visibility allows cloud management teams to use VMware vRealize’s powerful machine learning and capacity planning engine across their entire hybrid cloud environment.

Azure Monitor...everything
BindPlane for Microsoft Azure Monitor

Make Azure Monitor your first-pane-of-glass across your entire multi-cloud, multi-database or hybrid platform environment.

Thank you for contacting us. Your information was received. We'll be in touch shortly.