Author: @NickSalch is a Sr. Program Manager in the Azure Synapse Customer Success Engineering (CSE) team.
There are many options available for monitoring your dedicated SQL pool and this blog is part of a series that will walk you through implementing a monitoring solution for Azure Synapse Analytics. This article will walk you through the steps to enable historical monitoring using Azure Monitor Workbook templates on top of Azure Metrics and Azure Log Analytics. Once the workbook is deployed, you will have a rich graphical, interactive set of reports that help you identify and investigate issues with your workload.
NOTE: The first time this is enabled it can take a few hours before data starts flowing to the Log Analytics workspace. The workbook will not allow you to select the dedicated SQL Pool until data exist in the Log Analytics workspace, so that can be used as a check fi the data exists yet.
If you enable the SQL Audit log to go to the same log analytics workspace a new Audit tab will appear in the workbook to help you investigate the Audit log.
If you want to update your version of the workbook to the latest, simply follow the same steps again to deploy, except instead of creating a new workbook, click edit on the current workbook then go to advanced editor to paste in the new template.
This workbook is frequently updated with new features, so it’s recommended you periodically look for the newest version on GitHub, especially if you encounter an issue with the workbook. The tabs and charts in this workbook can change over time as feedback is incorporated.
The Overview page is meant to give you a big picture of your workload for the time range selected. The first 4 charts are perhaps the quickest way to determine if your workload is running within normal range from day to day. The Overview page is meant to identify issues to be investigated on other tabs, not necessarily to deep dive into why something happened.
Things you may identify from these charts:
If you discover any of these issues you may want to use other tabs to investigate the root-cause of the issue.
The Workload Management page is meant to help investigate the effectiveness of your workload management configuration. You can see what percentage of your overall concurrency resources are used by each workload group and how many queries run under each group.
Tempdb utilization is caused by 2 main factors:
Replicated Tables page focuses on the history of internal jobs that keep your replicated tables up to date. This can be very useful in determining if a table is actually a good candidate for a replicated table.
As a quick review, replicated tables are initially created by the system as round robin, then the first time the table is queried a background process called BuildReplicatedTableCache is triggered that will cache a full copy of this table to the first distribution assigned to each compute node. Future queries can use the cached copy and benefit from the cached replicated version until there is a change made to the table, in which case the cached copies are invalidated and the process starts over.
Query Investigation is very useful for investigating a particular run for a query or comparing historical query runs to each other. If you know the RequestID for the query (found from sys.dm_pdw_exec_requests), then you search for that or you can search for a snippet of the query text. The query text search can search the first 4 thousand characters of the query text.
Query Comparison allows you to input 2 request IDs and see the plans side by side. This is useful in determining the difference between plans in 2 runs of the same query as well as determine where execution time was spent from one run to another.
Autostats was created to help in the scenario where you see an autostats job executing, but do not know what query caused it to execute. This allows you to put in the request ID for that autostats name, or the name of the table the autostats are created on and it will show you the query that triggered that autostats job.
Query Audit allows you to filter to a particular user or application and see all queries executed against the database. After selecting a user, you can see all queries executed by that user within the selected timeframe.
Query Search simplifies investigation into who executed a particular query. For instance, if there was a DROP TABLE ran by a user that dropped data that should not have been dropped, you can search for the table name or just DROP TABLE to find the query that ran the drop as well as the user that executed it.
Our team publishes blog(s) regularly and you can find all these blogs at https://aka.ms/synapsecseblog. For deeper level of understanding of Synapse implementation best practices, please refer to our Success by Design (SBD) site at https://aka.ms/Synapse-Success-By-Design
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.