Create alerts for your Synapse Dedicated SQL Pool
In this article I will discuss how to configure alerts for you Azure Synapse dedicated SQL pool and provide recommended alerts to get you started. Enabling alerts allows you to detect workload issues sooner allowing you to take action earlier to minimize end-user impact.Azure metrics Dashboard for Azure Synapse Analytics - Part 1
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 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. 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. Click on the pencil symbol and edit the name of the chart to your preference. 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. 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. 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' 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. 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. Once all the 4 charts are pinned to the dashboard, resize the charts so that they all fit on one screen like below. 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.Monitoring serverless SQL ended requests by using Log Analytics.
I was working in a serverless SQL case where the customer is trying to figure out how to monitor serverless SQL requests by using log analytics. The intention of this guide is explaining all required setup and considerations about how to monitor serverless SQL requests with Azure Monitor.
Missing Fields Added to Dedicated SQL pool Diagnostic Settings Logs
Nine fields have been added to the Dedicated SQL pool logs and are generally available. These values will provide a more effective monitoring experience as customers are now able to identify which session the query belongs to along with other fundamental insights.
Unleashing the capabilities of Azure Synapse Analytics for a healthcare customer
This blog post aims to provide you with insights into the best practices for optimizing performance in data ingestion and transformation. You will learn how to efficiently use available resources, compute capacity, and workload management in Azure Synapse Analytics workspace. While the solution discussed in this blog pertains to a healthcare industry customer, the optimization techniques presented here are applicable to other industries as well.
Power BI Visualization for Azure Synapse SQL Pool Dynamic Management Views
Azure Synapse Analytics SQL Pools have what is called the Dynamic Management Views(DMVs) that provide insights into the internals of Azure Synapse SQL Pools. This helps to better understand the performance bottlenecks, tune distribution and understand overall performance of the database. But it is not trivial to having a quick start even for experienced DBAs. It also adds complexity as things like request ID, session id etc needs to figured out manually. Also sometimes a more intuitive way of finding performance bottlenecks is needed in a high scale deployment. This article talks how Power BI Desktop can be used to create visual dashboards across these DMVs.
Updated Synapse Analyzer Report: Workload Management and ability to monitor large data warehouses
Now monitor and discover issues around Workload Management for Synapse Dedicated Pool with the new update to Synapse Analyzer Report (version 2.0). With this update, another template is also included, that will support running reports against extra-large data warehouses having >10K objectsRetrieve Azure Synapse role-based access control (RBAC) Information using PowerShell
One of the key use cases that most customers face difficulties while retrieving or assigning the role-based access control in Azure Synapse Analytics, that they cannot find the correct usernames, group names or the service principal names using the PowerShell cmdlet "Get-AzSynapseRoleAssignment". The PowerShell cmdlet only provides limited information and it's difficult to understand since that contains the object IDs. In this article, I have provided multiple examples to retrieve this information in a more meaningful way, that includes the usernames, group name or the service principal name.
