Create a SQL Server Performance Monitor Dashboard in the Azure Portal

By:   |   Comments   |   Related: > 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
create performance monitor dashboard in azure portal 018
  1. Edit Query
create performance monitor dashboard in azure portal 019
  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:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Joe Gavin Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article

















get free sql tips
agree to terms