It's a rare thing these days to see a product essentially reinvent itself. Most product improvements across the industry tend to be more evolutionary than revolutionary; and with Server & Application Monitor 6.0 beta 3 (sign-up here) we are definitely aiming our sights towards the latter. Since we very first sat down at the drawing board to begin design on SAM 6.0, our mantra has been "question everything". We wanted to provide a tremendous level of additional application monitoring depth, while simultaneously simplifying the entire experience of initial setup and configuration of application monitoring; we also wanted to reduce overhead associated with configuration changes made in the environment. As result of this endeavor AppInsight was born.

 

AppInsight is a new application monitoring technique we are introducing as part of the Server & Application Monitor 6.0 release. Beginning with Microsoft SQL Server, AppInsight provides a whole new level of application monitoring detail that was previously very difficult, if not impossible to achieve using Application Templates. AppInsight is not a direct replacement for Applications Templates but rather an entirely new monitoring concept within SAM. Application Templates remain the primary method for quickly monitoring virtually any commercial, open source, or home grown application imaginable. In contrast, AppInsight is more akin to an entirely new product deeply embedded within SAM; designed from the top down to solve common, yet complex problems for a specific application, rather than merely a new feature.

 

AppInsight for SQL

 

Microsoft's SQL Server is a ubiquitous beast at the very core of many organizations critical business applications, both large and small. Unfortunately, gaining the necessary visibility into various different aspects of the SQL Server instance, or worse yet, the databases themselves, that effect the performance characteristics of the applications that rely on the database server has proven challenging without the necessary tools to provide visibility.

 

With SAM 6.0 we knew we wanted to deliver comprehensive SQL database server monitoring in a simple, intuitive, easy to use manner that would aid anyone, from novice systems administrators to veteran DBAs alike, identify both common and complex SQL issues in their environment.

 

Discovery

 

The AppInsight experience, as with most things,starts at the beginning during discovery. When adding nodes individually all available SQL 2008, 2008R2, and SQL 2012 Server instances found on the host are listed. As you can see in the screenshot to the right, you can select each individual instance you'd like to monitor the same as you would for volumes or interfaces.

 

If adding nodes individually isn't your speed, AppInsight for SQL has also been tightly integrated into the Network Sonar Discovery wizard. This allows you to easily scan an entire subnet, group of subnets, IP address ranges, or a list of individual IP addresses, to quickly discover hosts on your network and any SQL Server instances installed on those hosts. Regardless of which method you choose, monitoring your SQL server instances is as easy as checking the box.

Add Node - AppInsight.png
Network+Sonar+-+AppInsight.png

Once you've enabled AppInsight for SQL monitoring for any SQL Server instance(s), you will see them listed within the same All Applications resource alongside the template based applications that you're monitoring with SAM today.

 

A new icon adorns AppInsight for SQL instances in the All Applications resource, making them easily distinguishable from traditional template based applications.

AppInsight Dashboard

 

When you click on any AppInsight for SQL listed in the All Applications tree, you will be taken to a new, radically overhauled version of the Application Details view that's been designed from the ground up to serve as the dashboard for that Microsoft SQL Server instance.

 

The very first thing you're likely to notice when accessing this new Application Details view are the the newly redesigned chart resources that combines information, such as status and current value normally found within the Components resource, with those metrics historical values charted over time. This allows you to see not only how the application is performing now, but how that performance compares to historical trends without needing to drill deeper into the Component Details view.

All Applications - AppInsight.png

There is such an enormous amount of valuable information on this view that it has been categorized and broken up logically across multiple resources; a theme carried over from the Asset Inventory subview I discussed in my previous SAM 6.0 Beta blog post. This prevents anyone from becoming too overwhelmed with all the information shown, while also making the diagnosis of root cause easier. For example, if you're troubleshooting a potential memory issue on your SQL Server, then all information related to memory is contained within the Memory resource. There are similar resources for information such as latches & locks, connections, cache, paging, buffers, and more.


Memory.png

Database Details

 

AppInsight for SQL doesn't merely gather information about the SQL Server instance itself but also individual database managed by that SQL Server as well. Contained within the Application Details view, you will find the All Databases resource that displays status and basic size information for the databases running on this SQL instance. As databases are created on the SQL server, AppInsight will automatically begin monitoring them. Conversely, when databases are deleted from the SQL server they will disappear from the All Databases resource. Click on any of the databases listed and you'll be taken to the Database Details view which contains all information specific to that database.

All databases.png

Database Index Fragmentation.png

 

The Database Details view helps to identify various database performance issues, such as those associated with poorly performing indexes through the top 10 clustered and non-clustered indexes by fragmentation resources.There's also a wealth of other valuable information contained in this view that will help you to determine which tables consume the most storage space, the number of rows in each table, white space remaining in the database, as well as resources to help isolate disk I/O contention issues for both database and transaction log files.

Database - Tables by Size.png
Expensive Queries


What do you do when "Sally" in accounting complains that the ERP system, that's critical to the business and relies on Microsoft SQL, is "slow", but you've exhausted all avenues trying to isolate the cause of the issue? SAM's AppInsight for SQL may be telling you that the issue is CPU related, but how do you determine the actual cause? This is where having insight into the most expensive queries executing on the SQL server becomes absolutely essential. Within the Application Details view you will find the Top 10 Expensive Queries resource that shows the the most expensive queries running across all databases for that SQL server instance. Additionally, you will find the same Top 10 Expensive Queries resource on the Database Details view that shows the list of most expensive queries running against that specific database. If those resources don't provide an adequate level of detail to help isolate the issue there's also a dedicated Queries subview where you can filter on a specific database, time frame, host, or user. From this view you can quickly identify that the cause of the high CPU utilization on the SQL server, and the ERP systems poor performance, are caused by "Jan" in the Finance department running her quarterly reports. That's actionable information everyone can use.

Most Expensive Queries.png

Download Details

 

There's still a ton more AppInsight for SQL goodness that I haven't yet shown. If you'd like to see it in action for yourself sign-up here to participate in the SAM 6.0 beta. Note: You must already own a license of Server & Application Monitor that's currently under active maintenance to participate in the Beta.