Query Labels in Azure Synapse Analytics Dedicated SQL Pool

Synapse Analytics Dedicated SQL Pools support a new concept called query labels. Query labels are tags that can be applied to SQL queries using the OPTION keyword. e.g.

FROM dbo.ADEtable
OPTION (LABEL = 'ADE Query Label')

As we know, Synapse Analytics has an MPP architecture where data is stored across multiple nodes. This means that table rows can be distributed across multiple nodes and what seems to be a simple SELECT statement, might actually pull data from multiple underlying nodes.

If you were to look at the query execution plan for a query that performs a DML operation on a distributed table, you will see multiple underlying steps, which may include moving data between nodes.

These intermediate database operations are logged separately as individual log entries in the DMVs. (DMVs or Dynamic Management Views are system views that are used by all versions of SQL Server to track Database metadata and performance)

By using a query label, the query can be uniquely identified and traced within the DMVs.

A very simple example for querying a DMV using a query Label is provided below:

FROM    sys.dm_pdw_exec_requests r
WHERE   r.[label] = 'ADE Query Label'

Using context specific query labels also helps in tracking and optimizing query performance for a particular application or use case, such as ETL table loads.

As a bonus, following a proper naming convention for query labels also makes it much easier to find related queries among all the code stored in source control.

Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-develop-label

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: