Azure metrics Dashboard for Azure Synapse Analytics - Part 1

Published Dec 26 2020 11:09 PM 3,063 Views
Microsoft

This article is part 1 of a 3 part monitoring series to meet the increasing needs of customers to proactively monitor the Synapse pool resource consumption, workload patterns and other key performance metrics.

 

In this post, we will cover the dashboards that are available today for us on the Azure portal. These don’t require heavy customization and are very easy to set up.

 

To create your dashboard, navigate to the Azure Portal Home page -> Synapse Pool resource blade -> Monitoring -> Metrics

 

prvemula_14-1609052505630.png

 

Although numerous metrics are available for building dashboards, this segment will cover the 4 most important ones for monitoring your DataWarehouse in this article. We will be using these metrics in the upcoming posts in the series as well.

prvemula_0-1609054865842.png

 

1. Resource Utilization - CPU, DWU, IO percentages

 

From the drop down shown above choose max CPU percentage, add max DWU percentage metric and max Data IO Percentage as shown below.

prvemula_1-1609054931515.png

Click on the pencil symbol and edit the name of the chart to your preference.

prvemula_17-1609052505611.png

 

Please note that DWU Percentage and CPU percentage overlap and you may see only one of them. The DWU percentage is usually either the CPU or IO percentage, whichever is higher.

prvemula_18-1609052505614.png

 

 

Now save the chart to a dashboard by clicking the 'pin to dashboard' option on the top righthand corner. You will be asked to choose between pinning it to an existing dashboard vs a new one as shown below.

prvemula_19-1609052505616.png

 

prvemula_20-1609052505617.png

 

Once the chart is saved/pinned to the dashboard, follow the same process to create the remaining charts as well, as shown below.  

 

2. Active and Queued queries - Concurrency details

 

Following the same process as above, create another chart on the same dashboard blade by adding active queries and queued queries aggregating on 'Sum'

prvemula_21-1609052505618.png

prvemula_22-1609052505620.png

 

3. Workload Group Allocation - Resource classes and their percentage allocation details

 

For this chart, select workload group allocation by system percent aggregating on 'Max' and split by 'Workload group'. Please note that there is a limit on the number of workload groups you can monitor.

 

prvemula_23-1609052505621.png

prvemula_24-1609052505623.png

 

4. Tempdb Utilization - tempdb usage across all the nodes

 

Add the below mentioned metric to your chart aggregating on 'Max'. It is important to note that the chart below is the minimum, average or maximum value over a 5 minute window of the average tempdb utilization across all the nodes. In general, tempdb is located on each of the nodes, however, Azure metrics do not show  the individual node level tempdb utilization as of yet. This has been brought to the attention of the development teams.

prvemula_25-1609052505625.png

prvemula_26-1609052505626.png

 

Once all the 4 charts are pinned to the dashboard, resize the charts so that they all fit on one screen like below.

prvemula_27-1609052505633.png

 

Now that you have the important dashboards setup, you can build additional custom dashboards to get into more granular details about what queries/workloads are affecting your resources. This is not done by graphical user interface entirely and the second part of this post will provide you the step by step process for setting up the same.

%3CLINGO-SUB%20id%3D%22lingo-sub-2016393%22%20slang%3D%22en-US%22%3EAzure%20metrics%20Dashboard%20for%20Azure%20Synapse%20Analytics%20-%20Part%201%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2016393%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20article%20is%20part%201%20of%20a%203%20part%20monitoring%20series%20to%20meet%20the%20increasing%20needs%20of%20customers%20to%20proactively%20monitor%20the%20Synapse%20pool%20resource%20consumption%2C%20workload%20patterns%20and%20other%20key%20performance%20metrics.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20post%2C%20we%20will%20cover%20the%20dashboards%20that%20are%20available%20today%20for%20us%20on%20the%20Azure%20portal.%20These%20don%E2%80%99t%20require%20heavy%20customization%20and%20are%20very%20easy%20to%20set%20up.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20create%20your%20dashboard%2C%20navigate%20to%20the%20Azure%20Portal%20Home%20page%20-%26gt%3B%20Synapse%20Pool%20resource%20blade%20-%26gt%3B%20Monitoring%20-%26gt%3B%20Metrics%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_14-1609052505630.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242936iAF49641DCAE994CE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_14-1609052505630.png%22%20alt%3D%22prvemula_14-1609052505630.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlthough%20numerous%20metrics%20are%20available%20for%20building%20dashboards%2C%20this%20segment%20will%20cover%20the%204%20most%20important%20ones%20for%20monitoring%20your%20DataWarehouse%20in%20this%20article.%20We%20will%20be%20using%20these%20metrics%20in%20the%20upcoming%20posts%20in%20the%20series%20as%20well.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_0-1609054865842.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242973i2C43D21391787EF3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_0-1609054865842.png%22%20alt%3D%22prvemula_0-1609054865842.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E1.%20Resource%20Utilization%20-%20CPU%2C%20DWU%2C%20IO%20percentages%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFrom%20the%20drop%20down%20shown%20above%20choose%20max%20CPU%20percentage%2C%20add%20max%20DWU%20percentage%20metric%20and%20max%20Data%20IO%20Percentage%20as%20shown%20below.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_1-1609054931515.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242975i3BBC0D93B599BAE0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_1-1609054931515.png%22%20alt%3D%22prvemula_1-1609054931515.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EClick%20on%20the%20pencil%20symbol%20and%20edit%20the%20name%20of%20the%20chart%20to%20your%20preference.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_17-1609052505611.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242941i2D47BEF58C6A7BEC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_17-1609052505611.png%22%20alt%3D%22prvemula_17-1609052505611.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20note%20that%20DWU%20Percentage%20and%20CPU%20percentage%20overlap%20and%20you%20may%20see%20only%20one%20of%20them.%20The%20DWU%20percentage%20is%20usually%20either%20the%20CPU%20or%20IO%20percentage%2C%20whichever%20is%20higher.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_18-1609052505614.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242942i8DC3840FD662ADB8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_18-1609052505614.png%22%20alt%3D%22prvemula_18-1609052505614.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20save%20the%20chart%20to%20a%20dashboard%20by%20clicking%20the%20'pin%20to%20dashboard'%20option%20on%20the%20top%20righthand%20corner.%20You%20will%20be%20asked%20to%20choose%20between%20pinning%20it%20to%20an%20existing%20dashboard%20vs%20a%20new%20one%20as%20shown%20below.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_19-1609052505616.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242940iF5BD79EB1E07A37F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_19-1609052505616.png%22%20alt%3D%22prvemula_19-1609052505616.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_20-1609052505617.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242946i923542527483D1B6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_20-1609052505617.png%22%20alt%3D%22prvemula_20-1609052505617.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20the%20chart%20is%20saved%2Fpinned%20to%20the%20dashboard%2C%20follow%20the%20same%20process%20to%20create%20the%20remaining%20charts%20as%20well%2C%20as%20shown%20below.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E2.%20Active%20and%20Queued%20queries%20-%20Concurrency%20details%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFollowing%20the%20same%20process%20as%20above%2C%20create%20another%20chart%20on%20the%20same%20dashboard%20blade%20by%20adding%20active%20queries%20and%20queued%20queries%20aggregating%20on%20'Sum'%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_21-1609052505618.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242947i822AD8FA5850BBE6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_21-1609052505618.png%22%20alt%3D%22prvemula_21-1609052505618.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_22-1609052505620.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242948i257D5D46D6B81EDC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_22-1609052505620.png%22%20alt%3D%22prvemula_22-1609052505620.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E3.%20Workload%20Group%20Allocation%20-%20Resource%20classes%20and%20their%20percentage%20allocation%20details%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20this%20chart%2C%20select%20workload%20group%20allocation%20by%20system%20percent%20aggregating%20on%20'Max'%20and%20split%20by%20'Workload%20group'.%20Please%20note%20that%20there%20is%20a%20limit%20on%20the%20number%20of%20workload%20groups%20you%20can%20monitor.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_23-1609052505621.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242951i9676CF87E61D5F7F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_23-1609052505621.png%22%20alt%3D%22prvemula_23-1609052505621.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_24-1609052505623.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242953i9B720B75FE410A6E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_24-1609052505623.png%22%20alt%3D%22prvemula_24-1609052505623.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E4.%20Tempdb%20Utilization%20-%20tempdb%20usage%20across%20all%20the%20nodes%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CLI-WRAPPER%3E%3C%2FLI-WRAPPER%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdd%20the%20below%20mentioned%20metric%20to%20your%20chart%20aggregating%20on%20'Max'.%20%3CSTRONG%3EIt%20is%20important%20to%20note%20that%20the%20chart%20below%20is%20the%20minimum%2C%20average%20or%20maximum%20value%20over%20a%205%20minute%20window%20of%20the%20average%20tempdb%20utilization%20across%20all%20the%20nodes%3C%2FSTRONG%3E.%20In%20general%2C%20tempdb%20is%20located%20on%20each%20of%20the%20nodes%2C%20however%2C%20Azure%20metrics%20do%20not%20show%26nbsp%3B%20the%20individual%20node%20level%20tempdb%20utilization%20as%20of%20yet.%20This%20has%20been%20brought%20to%20the%20attention%20of%20the%20development%20teams.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_25-1609052505625.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242952i9CA0097923CEAC54%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_25-1609052505625.png%22%20alt%3D%22prvemula_25-1609052505625.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_26-1609052505626.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242954i0D160E6EC5C8917F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_26-1609052505626.png%22%20alt%3D%22prvemula_26-1609052505626.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20all%20the%204%20charts%20are%20pinned%20to%20the%20dashboard%2C%20resize%20the%20charts%20so%20that%20they%20all%20fit%20on%20one%20screen%20like%20below.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22prvemula_27-1609052505633.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F242955i47420E1E09B26550%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22prvemula_27-1609052505633.png%22%20alt%3D%22prvemula_27-1609052505633.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20that%20you%20have%20the%20important%20dashboards%20setup%2C%20you%20can%20build%20additional%20custom%20dashboards%20to%20get%20into%20more%20granular%20details%20about%20what%20queries%2Fworkloads%20are%20affecting%20your%20resources.%20This%20is%20not%20done%20by%20graphical%20user%20interface%20entirely%20and%20the%20second%20part%20of%20this%20post%20will%20provide%20you%20the%20step%20by%20step%20process%20for%20setting%20up%20the%20same.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2016393%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EInternals%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Administration%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Monitoring%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Support%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Dec 26 2020 11:58 PM
Updated by: