Workload insights into SQL Data Warehouse delivered through Microsoft Azure Monitor diagnostic logs
SQL Data Warehouse (SQL DW) now enables enhanced insights into analytical workloads by integrating directly with Microsoft Azure Monitor diagnostic logs. This new capability enables developers to analyze workload behavior over an extended time period and make informed decisions on query optimization or capacity management. SQL DW is a flexible, secure, and fully managed analytics platform for the enterprise optimized for running complex queries quickly across petabytes of data.
Today, customers leverage Dynamic Management Views (DMVs) to get insights into their data warehouse workload. These DMVs have a limit of 10,000 rows that can easily be exceeded for intensive enterprise data warehouse workloads with heavy query activity. Relying solely on DMVs hinders or blocks many query troubleshooting scenarios for active workloads. To work around this DMV limitation, custom logging solutions were required which consumed internal system resources, increased the total cost of the data warehouse solution, and introduced additional development complexities and maintenance effort.
We have now introduced an external logging process through Azure Monitor diagnostic logs, which provides additional insights into your data warehouse workload. With a single click of a button, you are now able to configure diagnostic logs for historical query performance troubleshooting capabilities using Log Analytics. Azure Monitor diagnostic logs support customizable retention periods by saving the logs to a storage account for auditing purposes, the capability to stream logs to event hubs near real-time telemetry insights, and the ability to analyze logs using Log Analytics with log queries. Diagnostic logs consist of telemetry views of your data warehouse equivalent to the most commonly used performance troubleshooting DMVs for SQL Data Warehouse. For this initial release, we have enabled views for the following:
Configure diagnostic logs to emit to Log Analytics: