Create a SQL Server Performance Monitor Dashboard in the Azure Portal

By: Joe Gavin | Updated: 2018-10-16 | 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'

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'

Click 'Share'
Click 'Publish'

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

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

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
Update

Publish changes

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

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)

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.

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.

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.

Refresh the query

Edit title

create performance monitor dashboard in azure portal 018

Edit Query

create performance monitor dashboard in azure portal 019

Open Chart in Analytics Blade - this is where can dig deeper into the data

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

Oops, it's gone.

To restore it:

Upload
Choose file
Open

Share
Publish

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:

About the author

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

Article Last Updated: 2018-10-16