Please note that this blog has been updated to include a link to our 11.2 product release tour.
In December last year I moved from the Solution Engineering team, where I worked with Scott, Rich, and Lori to the Product Management team where I took responsibility for the SQL Sentry monitoring solution. So while this is another in a long line of SQL Sentry and SentryOne Platform releases, it is my first. As a result, I would like to run through some of the new features and product enhancements that we have put in place with this release.
We have several new features built into the SentryOne Platform. I’ll walk through of some of the major capabilities that we now have at our disposal.
Top SQL
Technically, this new capability could be called an enhancement. However, given the scale of the evolution that has taken place for this new Top SQL it is like getting a whole new toy box to play with. Before we dive in and look at what we have now, let us just remind ourselves of the Top SQL feature that has provided insight into our problem queries since 2008 when we introduced Performance Advisor.
Current Top SQL
We can see currently running queries, big hitting completed queries, procedure and query stats, as well as the query text. These, combined with the integrated Plan Explorer, gave us the ability to really get into why our queries were running slowly.
New Top SQL
So, given that we already had a good solution for troubleshooting query performance, what is the new Top SQL and what does it give me? Well, for a start we have revamped the UI in the client. You have more information at your fingertips, allowing you to get a deeper understanding of your problem queries with fewer clicks. It provides a superior solution to Diagnose and Optimize your queries.
In addition, we’ve made changes to the overall layout by switching running queries and filters to their own tabs. We have surfaced a lot of the data that was in the integrated Plan Explorer, meaning it’s now in the main interface. The two main components here are the Execution Plan preview and the flattened statement tree. Together with the new Query History chart that highlights the performance of the query or statement that we collected, it provides all of the information you need in one place to Diagnose and Optimize your queries.
The new Statements Grid is built up from the statement tree that is normally visible in Plan Explorer. Here we can see the statements that were collected with the associated completed query, procedure stats or query stats. It provides details on the key performance metrics that are associated with each component part of the larger query. This gives us the ability to see which resources were consumed by which part of the batch.
In addition to the default columns, there are a wealth of other columns such as CE Version, Missing Indexes, RID Lookups and many more. With these additional columns, you can surface information about where, and what issues you are seeing. It allows you to target specific problem queries and statements.
Identifying historical batch and statement executions via CPU, IO, or Duration can really help when it comes to performing Root Cause Analysis (RCA). Did that new index or stored procedure update have the desired impact? Comparing it to the history will assist in proving that.
Overall, this new Top SQL will help make identifying and resolving SQL Server workload issues easier.
PowerShell Condition Action
With PowerShell confirmed as the de facto standard for Windows and Microsoft automation, it made a lot of sense to add this as an explicit option for Condition Actions. What this means is that now it is possible to execute PowerShell directly from SentryOne in response to a health or performance condition that has been detected. This is applicable to both built-in Conditions and Advisory Conditions.
So, what possibilities does this open up? Having the ability to automatically generate the Cluster Log in response to an Availability Group or Failover Cluster Instance failover event being detected. All the way through to using the Invoke-RestMethod or Invoke-WebRequest PowerShell cmdlets to pass tokens from SentryOne messages to third party systems via web services. This new functionality allows for execution of the PowerShell script on the target, monitoring server, or any other registered target in the SentryOne solution.
Application Telemetry
At SentryOne we strive to build better software with every release. Up to and including this release we have relied on doing interviews with our users; past, present, and future. We seek to understand how they are using the SentryOne Platform; from navigating through the client to how long they spend in each area of the solution. While this presents us with great feedback, it only represents a small portion of our total customers. To build better software, we need to get more feedback, from a more diverse array of customers.
Obviously, I can’t interview everyone, so we made the decision to develop our first version of Application Telemetry. While I am only covering this at a very high level here, you can find a detailed post on the Telemetry that we gather and how we are using it in this blog post.
- Can I turn off Telemetry?
- Yes, you can disable sending Telemetry to SentryOne.
- What data is collected?
- Data collected is related to the SentryOne software, not your servers/databases/queries/etc.
- Where is the Telemetry sent from?
- Telemetry, if enabled, is sent from the client, not the monitoring service.
- How is data sent to SentryOne?
- Data is sent securely via HTTPS.
- Can the data be tied back to me?
- No, the data is anonymized before it is sent to us. We cannot reverse engineer the identifiers to link it to an installation.
- How is the data being used, and by whom?
- The data is being used to help us understand questions like how people are navigating through the client, how many monitoring services are present, and which features get used the most. The data is used by the SentryOne Product Management team to help with the decision-making process for updates to the SentryOne platform.
- Where is the data stored?
- The back end for the Telemetry system leverages a number of Microsoft Azure services, and is stored in North America.
As I mention above, the main driver for doing this is so that we can gain an understanding of which features are used, and how people get around in the application. These in turn will help us build better software. All of this can be enabled or disabled, and this will apply to all of the clients that connect to your SentryOne repository. So, if you want a say in how we build our software, please turn this on and send us the anonymized Telemetry so that you can tell us what is of most value to you as one of our customers.
In addition to the new features that I have listed above, we have also been working hard on bringing several enhancements to existing capabilities. These enhancements include providing deeper insight into virtualized SQL Servers, as well as being able to build your own Advisory Conditions for VMware hosts and Azure SQL Database targets. Here we will quickly cover some of these enhancements in a bit more detail.
FCI Dashboard Virtualization Metrics
When we introduced the capability to connect the SentryOne Platform to vCenter, it meant that for VM Guests we could gather VMware-level information. This gave those managing SQL Server systems visibility of Co-Stop and Ready Time CPU Counters, as well as Ballooned memory, and details about which host the guest was residing on at any given time. Now we have taken that a step further and added this ability to Failover Cluster Instance (FCI) targets. Now you can identify whether the performance symptoms you are seeing are host or guest related.
Advisory Condition Targets
We have expanded the range of targets for our Advisory Condition framework, adding the ability to create them for Azure SQL Database and VMware Hosts. These conditions leverage the counters that we capture for these target types.
VMware Advisory Condition Source
We’ve expanded on the ability to create a SentryOne Advisory Condition that allows creation of a Global Advisory Condition using the data that is collected from the VMware host when it is monitored by V Sentry. By adding VMware hosts as a first class Advisory Condition type, this allows the creation of target-level conditions. The target level facilitates a more granular approach to detecting and responding to performance or health conditions on VMware hosts.
The counter categories that are now available to pull data from in relation to the host are:
- CPU
- Datastore
- Guest CPU
- Memory
- Network
- vCenter Virtual Disk
- Virtual Disk
Within each of these categories, there are several different counters and counter instances that will allow you to build a comprehensive set of conditions for your monitored VMware hosts.
Azure SQL Database Advisory Condition Source
In the same way that we expanded the capability of V Sentry, we have done the same with DB Sentry. These new Advisory Conditions you can build will provide deeper insight into the performance and health of your Azure SQL Databases.
The new Azure SQL Database Advisory Conditions are combined with the Environment Health Overview. We can obtain insight on the health of all Azure SQL Databases that are registered in the SentryOne Platform. This becomes even more powerful when you consider the range of targets that SentryOne can monitor. It’s providing a global, single pane of glass view into your entire Microsoft Data Platform environment (whether you are running a mix of IaaS and PaaS in the Microsoft Azure cloud, or a Hybrid deployment that also encompasses on-premises).
With the Microsoft Data Platform evolving at pace, it is vital that we move with it. As a company, we have evolved the way that our Engineering teams build software. This allows us the ability to be more agile and responsive when Microsoft announces new initiatives for their Data Platform eco-system. The most obvious of these are new versions of SQL Server, and with SQL Server 2017 this also encompasses the move to Linux.
While these technologies are in a state of flux, we will include them in SentryOne. However, these will be considered experimental features and they may change as the technology that we are monitoring changes. Additionally, this is the case where we do a soft introduction of a new capability that will replace existing functionality. All of this means that some functionality will need to be off by default and need to be enabled by application config flags. Two of the major ‘experimental’ features in this release are “SQL Server on Linux” and “XE Tracing”.
SQL Server on Linux
While Microsoft is still working on the next version of SQL Server, releasing CTPs and RCs, it’s important that we can support the early adopters of these technologies as well as those on the currently supported versions of SQL Server. As such, while our existing release of SentryOne worked with SQL Server on Linux, we took a bit more time to refine it and bring in SQL Server on Linux as a dedicated target type. Because we are connecting directly to the SQL Server instance, this will use our limited access Dashboard which provides coverage of the SQL Server instance.
XE Tracing
One of the areas that makes SentryOne unique is our Engineering focus on minimizing the observer effect on monitored targets. This is one of the reasons that we have retained the use of SQL Trace over Extended Events (XE). While XE as a framework is highly efficient, the API for consuming them was not. The API has improved to the point that it is viable for using to pull the data we need. Due to this being a major change in architecture, we are taking an off by default approach. This is an example of a new feature that is currently behind an App Config Flag.
If you want to enable this feature and switch from Trace to XE, then this blog post will walk through how to enable it.
We are committed to monitoring Microsoft SQL Server wherever it resides, while maintaining the traditional on-premises monitoring that we have built up over the years. We will continue to add great new features that will help Data Platform professionals build and manage Microsoft Data Platform solutions. Fostering a Dev-Ops culture is important for any solution that is used to Monitor, Diagnose, and Optimize Data Platforms. It is becoming common for Operations teams that are using SentryOne to grant their Development Teams Read-Only access into SentryOne. This allows them to work closely and in collaboration to quickly resolve issues.
As we continue to move forward with our software, we made the decision to end support for the SentryOne Platform running on x86 architecture. We will be moving exclusively to an x64 client and monitoring service, albeit with the remaining support for the x86 SSMS add-in. Currently we are looking at implementing this decision at the end of Q1 2018, so there is still plenty of time to plan. If for whatever reason this is going to pose an issue for you, please let us know by getting in contact with our support team Support@SentryOne.com.
While we will be ending support for our solutions running on x86 we will still support monitoring of x86 SQL Server systems if you have them.
