This is the third in a four blog post series focusing on how to monitor and visualize SQL servers in your environment using Microsoft technologies. These post topic include:

  • Part 1: Monitoring & Visualizing SQL Server using System Center Operations Manager 2012 R2
  • Part 2: Monitoring & Visualizing SQL Server using 3rd party dashboard solutions
  • Part 3: Monitoring & Visualizing SQL server using Operational Insights (OMS) [This post]
  • Part 4: Monitoring & Visualizing other variations of SQL Server

This blog post will focus on how we can use the Operational Insights functionality available in OMS to provide SQL Server monitoring and visualization benefits. The topics of this blog post include:

  • Introducing the “SQL Assessment” solution in Operational Insights
  • Addressing recommendations in the SQL Assessment
  • Using log search to track information gathered by this solution
  • Changing the collection interval for the SQL Assessment solution
  • Fixing the recommendation to increase the number of tempdb files
  • Custom dashboards in Operational Insights

     

Monitoring & Visualizing SQL server using Operational Insights (OMS)

As we have seen in previous blog posts there have been significant advancements made in the area of what we can monitor and especially how we can visualize that information. Microsoft Operations Management Suite (OMS) includes Operational Insights which provides some great additional functionality when monitoring SQL server. The “SQL Assessment” is a solution in OpInsights which is used to add functionality.

 

The “SQL Assessment” solution in Operational Insights

The SQL Assessment solution is part of Operational Insights which is a component of OMS. The SQL Assessment solution functions primarily as a best practices analyzer for SQL server which provides recommendations for how to make your SQL server environment better. It is important to note that what OMS is providing here is not available anywhere else – not from Operations Manager, not from any 3rd party dashboard solutions. So if you want this type of functionality you will definitely want to check out OMS. OMS is available from an easy to access URL at: www.microsoft.com/oms.

The solution gallery explains the SQL Assessment solution as follows:

What does the SQL Assessment solution do? The following is a subset of the information available at: https://azure.microsoft.com/en-us/documentation/articles/operational-insights-solutions/

The SQL Assessment solution appears as a top level tile once you have added the solution on the overview page (3rd from the right on the top row in the screenshot below).

The SQL Assessment tile shows how many servers are assessed and when they were last assessed. Additionally this tile indicates how many recommendations exist (both high priority and low priority) and how many checks have been passed. The examples below show two different environments with this solution place for comparison.

This tile can be drilled into to provide a dashboard specifically for the SQL Assessment solution. This solution provides recommendations in six areas:

  • Security and Compliance
  • Availability and Business Continuity
  • Performance and Scalability
  • Upgrade, Migration and Deployment
  • Operations and Monitoring
  • Change and Configuration Management

Low priority recommendations are shown in blue, high priority recommendations are shown in red. Passed checks are displayed in green.

Each of these areas can be drilled into in order to provide additional details. The example below shows additional details within the Availability and Business Continuity section. Note how the specific objects which are affected are displayed such as the five databases (including their server) which should have a full backup within the last seven days.

If you drill into the details one step forward this brings you to the search screen which shows the specific recommendations which are related to the screen that you drilled in from.

Note the options available at the bottom of the screen which allow you to change the view (list or table), export data to a CSV, save your search, assign this as a favorite search, or to see the history information.

The history information appears on the right side and a sample is shown below:

 

Addressing recommendations in the SQL Assessment:

The top level tile for our SQL Assessment currently shows 5 high priority recommendations. To see when the last SQL Assessment’s data was added we can look at the SQL Assessment tile shown below. This tile also indicates that two servers were last assessed on Tuesday September 8, 2015.

Many (or possibly all) of the recommendations which are provided by this solution provide suggested actions to address the recommendation. The item below provides a suggested action which discusses how to configure the tempdb database to reduce page allocation contention.

Let’s see what happens when we implement one of the suggested actions for a high priority recommendation. The recommendation below provides a suggested action to increase the number of tempdb database files on a specific server indicated in the affected objects section shown below.

After the issue is resolved, and the solution has provided it’s updated information to Operational Insights we can now see that the issue has been resolved (note how the high priority recommendations have decreased from 2 to 1 now).

This change is also reflected in the top level SQL Assessment tile which has moved down from 5 high priority recommendations to 4. We can also see that the SQL Assessment data has been updated as this now reflects a date of Thursday September 10, 2015.

 

Using log search to track information gathered by this solution:

Let’s see what information is collected by the SQL Assessment solution. To do so, we open the log search area.

Within the log search area we can use the following query looking for log entry’s related to failed recommendation results on this particular system for this particular recommendation. The syntax is shown below:

Type=SQLAssessmentRecommendation RecommendationResult=failed (Computer=”AllInOneOMTP3.CloudAzure.pvt”) (Recommendation=”Increase the number of tempdb database files.”)

To find non-failures we are looking at:

Type=SQLAssessmentRecommendation Computer=”AllInOneOMTP3.CloudAzure.pvt” (Recommendation=”Increase the number of tempdb database files.”)

Using this type of a query we can see that the last result which was returned was a resolved recommendation result (this was returned after making the change documented in the suggested actions section for this particular recommendation.

If you have queries that you like you can save them off and use them later both as queries and for custom dashboards. For details on the queries and how they work I recommend Stan’s blog post at: https://cloudadministrator.wordpress.com/2014/10/23/microsoft-azure-operational-insights-preview-series-sql-assessment-part-7/

 

Changing the collection interval for the SQL Assessment solution:

Based upon using this solution it appears that the assessment is run every 7 days by default (this is a good practice, you don’t want to be regularly running tasks which check the state of something which should not be changing that often). To test this idea we can temporarily override this and see if we can push the change up more quickly so that we do not need to wait a week to find out if the change that we made resolved the issue. To do this we first need to find the management pack in Operations Manager. The first step is to look for management packs which include “System Center Advisor”. The “Microsoft System Center Advisor SQL Assessment Intelligence Pack” appears to fit the bill as shown below.

The next step is to find what rules are included in this management pack by searching on the full name of the management pack: (filtered by “Microsoft system center advisor SQL assessment intelligence pack”). Based upon the search there are two relevant rules:

“Microsoft System Center Advisor SQL Assessment Run Assessment Rule” – this is disabled by default but it is enabled through an override. This is the primary rule that we are interested in for this blog post.

“Microsoft System Center Advisor SQL Assessment Upload Assessment Results Rule” – this rule is enabled by default and it appears to push up data to OMS.

The “Microsoft System Center Advisor SQL Assessment Run Assessment Rule” is enabled through an override for the “Microsoft System Center Advisor Monitoring Server Group” as shown below.

By looking at the properties of the “Microsoft System Center Advisor SQL Assessment Run Assessment Rule” we can see how often it is run. The screenshot below shows the IntervalMinutes is set to 10080 which is the same as running every 7 days.

We can create an override to change the default upload timeframe to every 60 minutes from once a week for rule.

This approach provides a way to cause the SQL Assessment solution to check for updates on an hourly basis instead of once a week. After completing a test like this you will want to be sure to remove this override to avoid additional overhead from this assessment running on an hourly basis.

 

Fixing the recommendation to increase the number of tempdb files:

So what does it look like to actually increase the number of tempdb files as is discussed in this recommendation? My system runs on between 2 and 8 cores depending on whether I’m running it as an A2 or an A4 (for details see https://www.catapultsystems.com/cfuller/archive/2015/09/01/kicking-the-tires-on-tp3-using-an-all-in-one-opsmgr-virtual-in-azure-iaas/). My concern is performance when it is running on 8 cores as that’s when I’m using this system for demonstrations so I am setting this to be configured as if it has 8 cores. To set this we open SQL manager for the database server indicated in the recommendation and then we open the tempdb system database properties and we change the files configuration. I added the various tempdev databases (tempdev2-7) until there were a total of 8 databases and one log as shown in the graphic below.

 

Custom dashboards:

You can create your own dashboards in Operational Insights to display information in a different way than existing solutions or to display information which is not displayed in existing solutions. To create a new dashboard, go to the My Dashboard icon.

From here you are prompted with how to add saved searches to dashboards and assemble your own dashboard.

To create a dashboard choose the query that you want from the right hand side. When you create a dashboard you can choose between two different tile visualizations:

An example for missing critical security types is shown below using both of the tile visualization methods available.

You can also set thresholds for your dashboard. For my systems which are missing critical security updates it is simple to turn the threshold on and say that it is passing the threshold when the value is over 5.

Once we have put together our dashboards we re-click on customize at the bottom to finish up editing the dashboard items. The example below shows a dashboard which indicates that there are more than 5 outstanding critical security updates.

We can also use the queries which we created when using the log search capabilities discussed earlier in this blog post. This dashboard shows the number of SQL Assessment which are failed grouped by their focus area.

This matches the total of the high priority and low priority recommendations (4+19=23, 13+5+5=23).

Summary: The SQL Assessment solution in OMS provides some really useful information and it is a breeze to integrate with an existing Operations Manager environment. If you have Operations Manager and you haven’t tried this out yet you definitely should. If you don’t have Operations Manager this can also be used without Operations Manager so either way – check this out! In the next part of this blog series we will wrap this series up with a look at monitoring other types of SQL.

Reference URL’s:

Category: infrastructure