Metrics and logs located in the Monitoring section of most Azure products in the Azure portal are within the purview of Azure Monitor. Figure 9.2 shows an example of metrics, and Figure 9.1 shows an example of logs. The discussion of what those features are and how to use them surrounds those figures. The descriptions of those metrics and logs will enable you to take action when either generates the need to do so. That is a general statement; however, metrics values and log statements are relevant to the platform configuration and application directly. It is only possible to make general statements, but a baseline understanding provides you a starting point to build upon so that you can gain application‐specific metric thresholds and log interpretations. In this section you will learn about metrics and log details related to Azure Synapse Analytics workspaces, dedicated SQL pools, and Apache Spark pools. Table 9.5 details Azure Synapse Analytics workspace metrics.
TABLE 9.5 Azure Synapse Analytics workspace metrics
| Name | Aggregate | Description |
| Activity Runs Ended | Count, Sum | Total number of activity runs |
| Link Connection Events | Sum | Total number of connections to linked services |
| Link Latency in Seconds | Avg, Max, Min | Realized latency through linked services |
| Link Processed Data | Sum | Processed linked service data by volume in bytes |
| Link Processed Rows | Sum | Total rows processed through a linked service |
| Link Table Events | Sum | Generated table events from a linked service |
| Pipeline Runs Ended | Count, Sum | Number of ended pipeline runs |
| Trigger Runs Ended | Count, Sum | Number of ended trigger runs |
The workspace Metrics blade also includes metrics for the built‐in, serverless SQL pool: Data Processed (Bytes), Login Attempts, and Requests Ended. These metrics are helpful for determining how utilized the serverless SQL pool is. Table 9.6 provides the metrics available for dedicated SQL pools.
TABLE 9.6 Dedicated SQL pool metrics
| Name | Aggregate | Description |
| Active Queries | Sum | Total number of active queries across all SQL pool nodes |
| Adaptive Cache Hit % | Avg, Max, Min | Identifies cache utilization |
| Adaptive Cache Used % | Avg, Max, Min | Identifies cache utilization |
| Connections | Sum | Number of SQL pool logins |
| Connections Blocked by Firewall | Sum | Number of blocked SQL pool logins by firewall rule |
| CPU Used % | Avg, Max, Min | CPU utilization across all SQL pool nodes |
| DWU Limit | Avg, Max, Min | Configured size of SQL pool |
| DWU Used | Avg, Max, Min | DWU limit multiplied by the DWU percentage |
| DWU Used % | Avg, Max, Min | Combination of CPU and I/O usage across all SQL pool nodes |
| Local tempdb Used % | Avg, Max, Min | tempdb utilization across all SQL pool nodes |
| Memory Used % | Avg, Max, Min | Memory utilization across all SQL pool nodes |
| Queued Queries | Sum | Cumulative count of queued queries |
If the numbers returned from the adaptive cache metrics are low, it means that you should look to see if your dedicated SQL pool requires additional capacity. Another approach is to rerun your workloads so that the cache is loaded into memory on the node. What determines if the metrics value is low depends on the application and is drawn from your direct experience with it. Also, if you see numbers from the Queued Queries metric, it means that the concurrency limit was reached across your nodes. This is a clear indication that more capacity is needed for your pool or that queries are being blocked by some shared resource. Table 9.7 provides details about Apache Spark pools running in the Azure Synapse Analytics workspace.
TABLE 9.7 Apache Spark pool metrics
| Name | Aggregate | Description |
| Active Apache Spark Applications | Avg, Max, Min | Number of active Apache pools |
| Enabled Apache Spark Applications | Sum | Number of enabled Apache pools |
| Memory Allocated (GB) | Avg, Max, Min | Allocated memory to an Apache pool |
| vCores Allocated | Avg, Max, Min | Allocated vCores to an Apache pool |
From a logging perspective, you might have noticed that the selected categories from Diagnostic Settings is what instructs the platform to log that data into the selected locations. The examples in this chapter have been to target an Azure Monitor Log Analytics workspace, which is accessible directly or by selecting the Logs navigation menu item, which places you directly into the product scope. The tables into which logs can be stored for an Azure Synapse Analytics workspace are as follows:
- Synapse RBAC Operations
- Synapse Gateway Api Requests
- Built‐in Sql Pool Requests Ended
- Integration Pipeline Runs
- Integration Trigger Runs
- Synapse Link Event
Each log is placed into a dedicated table with many columns. The details found in the columns are helpful for determining the specific command, query, or API used. In addition, the log can show whether the command was successful, and if not, the actual status and an error message, which can help you determine what caused the unsuccessful operation. Additionally, columns such as duration, number of rows, dataset sizes, and file sizes are also often present and are helpful in gaining an understanding of the behaviors your application displays during given scenarios. For example, how does your application behave when the number of retrieved rows is 100,000? Does it fail? Is it slow? Or does it fly like a jet plane? Using logged numerical values and timeframes in combination with the performance metrics can give you a good overview of the application and platform state and help drive decisions to improve its behavior. The following logs are available for dedicated SQL pools:
- Sql Requests (sys.dm_pdw_sql_requests)
- Request Steps (sys.dm_pdw_request_steps)
- Exec Requests (sys.dm_pdw_exec_requests)
- Dms Workers (sys.dm_pdw_dms_workers)
- Waits (sys.dm_pdw_sql_requests)
If the names of the log tables look familiar, you might be thinking of their related PDWs, which are provided as well. The content in the log tables is what you should expect to find from the resulting PDW executions. Apache Spark pool logs have one table, Big Data Pool Applications Ended. Each product has its own set of metrics, logs, and use cases. For the DP‐203 exam, those concepts covered in this chapter are necessary for you to understand. However, consider expanding your scope of knowledge and find what each product offers, to see if you can link together all products that make up your data analytics solution.
