As you now know, there are many built‐in monitoring logs you can configure to feed into Azure Monitor using Log Analytics. All the categories within each Azure product’s diagnostic settings and many of the metric values feed automatically into Azure Monitor once configured to do so. Many other logs are helpful when it comes to supporting and troubleshooting IT solutions, for example, IIS logs, Event Tracing for Windows (ETW), and Windows and Linux performance logs. In addition to those known monitor log types, your application may create custom logs that are unique. To ingest known monitor logs like IIS and ETW, you need to install an Azure Monitor extension onto the machine that generates the logs. This is like an agent, service, or listener that binds to the monitor event pipeline, captures the logs, and copies them in Azure Monitor for analysis through Log Analytics queries.
Custom logs require some configuration in the Azure portal. From the Log Analytics workspace where you want the custom log to be uploaded, you navigate to the Custom Logs blade. There is a wizard that walks you through the configuration of your sample log file. Part of the configuration requires that you identify where this file is hosted on the machine running the Azure Monitor extension so that the agent can access it and send it to Azure Monitor.
Measure Query Performance
Most of the performance‐related content will be covered in Chapter 10, but because you must have logs and metrics to analyze, here is a good place to make a short introduction. The following is a very simple example of a query that would not be performant:
SELECT *
FROM theInternet
WHERE description LIKE ‘%the%’
If the table the data is retrieved from has few rows and columns, then it is possible a result would return rather quickly. However, as the number of rows increases, so will the latency and so will the compute requirements. At some point, the query would never return, because the amount of matching data and the amount of compute required to parse all the data would be too great. Projecting the query to return only the necessary columns and not using wildcard searches are fundamental characteristics that pertain to writing reliant queries. As you know, queries can become very complex, and the reason why they perform slowly may not be obvious by just looking at the queries themselves. Instead, you will need to perform some analysis of which queries are running, what they are doing, and how long they are taking. There are two areas to review when you need to determine how queries running on a dedicated SQL pool are performing: Azure Monitor metrics and DMVs.
Figure 9.17 showed dedicated SQL pool metrics that can have some impact on the performance of query execution: Adaptive Cache Used Percentage and Adaptive Cache Hit Percentage, which is also of value in this context. Each metric determines how optimally the workloads are using adaptive cache. Adaptive cache on dedicated SQL pools is stored on local SSDs, and the used percentage value represents the amount used across all nodes. The Adaptive Cache Hit Percentage pertains to the sum of all hits on clustered column store indexes across all nodes. As previously explained, pulling queried data from memory and/or SSDs is much more performant than from hard disk. That means if you can write queries in a way that uses data cached in memory, the queries will execute faster. Also, depending on where you execute the queries from, there may be a product that helps you customize caching behaviors of your data analytics solution. The other option for measuring query performance is from a feature already discussed: DMVs. For example, you can use the sys.dm_pdw_exec_requests DMV to identify the queries that might need some investigation. Once you find that list, you can use the sys.dm_pdw_request_steps DMV to identify the execution plan or steps that the query performs to complete the operation. Looking into each step will help you find the specific step or group of steps causing the most latency. Then you can take a deeper look into them and recommend some optimization activities. Finally, deadlocks or hangs might occur when you have multiple queries that share a resource or excessive queuing. The sys.dm_pdw_waits DMV enables you to identify which queries are impacted by either of those scenarios.
