Monitoring queries
At the beginning of this chapter, in the Introducing performance tuning section, we learned that performance tuning is a process and that one of the steps is to identify the root cause of performance bottlenecks. We also saw in Chapter 9, Monitoring and Troubleshooting Azure Data Explorer, that the ADX Insights page in the Azure Portal provides out-of-the-box dashboards for performance and other telemetry, which is are very useful.
We can use KQL management commands to view all the commands and queries that have been executed by our ADX cluster. These KQL management commands provide valuable insights into CPU consumption, query execution duration, the query/command being executed, and the state of the query's execution. The following query returns all the queries, sorted by execution duration, which have been executed on our ADX cluster:
.show queries | project Text, Database, StartedOn, Duration, State, FailureReason, TotalCpu, CacheStatistics.Disk.Misses...