Create a SQL Server Performance Monitor Dashboard in the Azure Portal

By:   |   Updated: 2018-10-16   |   Comments   |   Related: More > Azure

Problem

Collecting and analyzing performance metrics is a key task for database environments and Azure is no different.  In this tip we look at how to create a performance monitor dashboard in Azure.

Solution

While it's by no means a substitute for monitoring software, Azure Log Analytics with Azure Dashboards is a handy place to do some “roll your own” monitoring if you have the need.

Log Analytics lets you query gathered Performance Monitor and Event Log data and Dashboards are a handy way of viewing the visualized data.

Assumptions:

  • You already have Azure Log Analytics
  • The Microsoft Monitoring Agent is installed and configured on your servers of interest and collecting data
  • You have some basic familiarity with querying Log Analytics

We're going to walk through the following topics:

  • Creating a new Dashboard
  • Creating a couple of visualizations based on queries of some Perfmon counter results
  • Making the Dashboard public
  • Writing a query and pinning it to the Dashboard
  • Editing the visualization
  • Rearranging visualizations
  • Resizing visualizations
  • Exporting and importing our Dashboard

Create a New Microsoft Azure Dashboard

OK, let's dig in. We'll get started by creating a new Dashboard for our visualized query results.

  • Login to your Azure Portal at http://portal.azure.com/.  Your default dashboard will be displayed.
  • Choose 'New Dashboard'
microsoft azure dashboard
  • Give the dashboard a name. I kept it simple for our demo and just called it “Demo Dashboard” but you can call it what you like.
  • Click 'Done Customizing'
microsoft azure dashboard
  • Click 'Share'
  • Click 'Publish'
microsoft azure dashboard

The dashboard is now public, and queries can be pinned to it.

Build Visualizations with Log Analytics

We'll leave the fresh canvas for a while and build some visualizations.

  • Go to the left side of the portal and choose Log Analytics
  • Choose your Log Analytics Workspace
microsoft azure dashboard log analytics
  • Logs
  • Copy and paste this code into the query window:
Perf
| where ObjectName == "Processor Information" and CounterName == "% Processor Time" // show % Processor Time
| summarize AggregatedValue = avg(CounterValue) by bin(TimeGenerated, 10m), Computer // summarize average counter value
| sort by Computer asc // order by machine name
| render timechart // chart data
  • Press the Run button
microsoft azure dashboard log analytics
  • Pin
  • Choose dashboard
  • Update
update dashboard
  • Click on your dashboard name
pin dashboard
  • Click the edit icon in the visualization
  • Enter the counter name in the Title box
  • Optionally add the threshold you want in the Subtitle box for reference
  • Update
microsoft azure dashboard log analytics
  • Publish changes
demo dashboard

Add More Visualizations

Now we have an easily repeatable process and can rinse, lather and repeat.

  • Go back to the query window and paste this in:
Perf
| where ObjectName == "Memory" and CounterName == "Available MBytes" // show Available MBytes
| summarize AggregatedValue = avg(CounterValue) by bin(TimeGenerated, 10m), Computer // summarize average counter value
| sort by Computer asc // order by machine name
| render timechart // chart data
  • Run
  • Pin
  • Choose dashboard
  • Update
  • Click on your dashboard name
  • Click the edit icon in the visualization
  • Enter the counter name in the Title box
  • Optionally add the threshold you want in the Subtitle box for reference
  • Publish changes
microsoft azure dashboard log analytics

And we can keep adding tiles if we like. To add a tile displaying Buffer Cache Hit Ratio.

  • Go back to the query window and paste this in:
Perf
| where ObjectName == "SQLServer:Buffer Manager" and CounterName == "Buffer cache hit ratio" // show Buffer Cache Hit Ratio
| summarize AggregatedValue = avg(CounterValue) by bin(TimeGenerated, 10m), Computer // summarize average counter value
| sort by Computer asc // order by machine name
| render timechart // chart data
  • Run
  • Pin
  • Choose dashboard
  • Update
  • Click on your dashboard name
  • Click the edit icon in the visualization
  • Enter the counter name in the Title box
  • Optionally add the threshold you want in the Subtitle box for reference
  • Publish changes

And we have another tile. (Note: the arrow points to a dip that's slightly below what we want)

buffer cache hit ratio

We have a few visualizations, so let's go ahead and look at customizing the dashboard. We can easily resize and rearrange the tiles to suit our needs.

  • Either hover in the upper right corner of the tile and click the ellipse of click the Edit button on the top of the Dashboard.
procesor time

or

azure dashboard
  • Hover you mouse within tile and click to drag to a new location or click in the lower right corner to resize it
  • ''Done Customizing' when you're done

azure dashboard

  • Publish Changes

azure dashboard

And your back to your changed Dashboard.

azure dashboard

Customizing the Azure Dashboard

Now that we have a few counters on our Dashboard and customized it, let's see what else we can do.

  1. Refresh the query
azure dashboard
  1. Edit title
  1. Edit Query
  1. Open Chart in Analytics Blade - this is where can dig deeper into the data
azure dashboard

Create a Dashboard Template

Now we'll create a template that can be used to import a new Dashboard and / or be used as a backup.

  • Click the Download button and a .json file will be created in your default download directory.

download azure dashboard

One thing we can do with file is restore a deleted Dashboard.

  • Delete
  • Check confirmation box
  • OK
delete azure dashboard

Oops, it's gone.

To restore it:

  • Upload
  • Choose file
  • Open
upload azure dashboard
  • Share
  • Publish
share and publish dashboard

And we've restored back to where we were.

Summary

The easiest way to build dashboards is the way we've seen so far. It's outside the scope of this tip, but the exported .json file can also be used can also be used as a Dashboard creation template. There is further documentation in the Next Steps section that will be of help.

Next Steps

Hopefully this tip has given you the steps to create an Azure Dashboard and this is by no means a deep dive into Log Analytics as we've just touched upon the topic, but hopefully this will help you with some basic Dashboard building. These links point to some more resources that reference Azure Log Analytics and Dashboards:



Last Updated: 2018-10-16


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools