Monitoring Synapse SQL Pools with Log Analytics

Synapse has powerful, simple to implement integration with Log Analytics.

It is critical to understand the environment and how users interact with it. This helps administrators optimise queries and costs, track activity over time and users can understand their queries better.

Contents

• Why is this useful
• What can be logged
• How can this information be displayed
  • Workbooks (portal)
  • Logs viewer (portal) - list of queries
  • PowerBI
• How is data captured to Log Analytics
• Cost Analysis

Why is this useful

Data Management Views (DMVs) in Synapse are very useful in understanding workload, however many of the underlying datasets only retain the most recent 10k rows (details), which in busy systems means that current data will expire within hours or days. Log Analytics allows long term retention of this data and trend analysis.

What can be logged

There are two kinds of logs: audit and diagnostic logs.

Audit logs include simplified views of workload on the platform and includes user names, source IPs and executed commands. Typically this will manifest as a single row per query.

Diagnostic logs include details of all the DMVs as well as metrics about the database. These closely mimic the DMVs. Queries stored in Log Analytics have some obfuscation applied, and are not currently linkable to a database principal. More detail here.

• logs
  • DmsWorkers
  • ExecRequests
  • RequestSteps
  • SqlRequests
  • Waits
• metric
  • Basic
  • InstanceAndAppAdvanced
  • WorkloadManagement

How can this information be displayed

Users can use various methods to consume this information

• Workbooks (portal)
• Logs viewer (portal) - list of queries
• PowerBI

Known limitations

Logging

Masking occurs on data sent to Log Analytics

Joining Exec Request and Audit

there is no common key between what is stored for Exec Requests and Audit logs, so it is not possible to directly obtain the Resource Class or detailed query plan for a specific user

Duplication

Duplication can occur in the data. This is unresolved in the current iteration

How is data captured to Log Analytics

For Audit logs (including username and summary query information)

For Diagnostic logs (including metric and detailed query information)