Limit query time on Azure Synapse Analytics

On Azure Synapse Analytics, users can inadvertently run very resource-consuming queries over potentially large amounts of data.

On SQL Server, it's possible to configure a server-wide remote query timeout to limit this. Ideally, a database will provide an option to do this on an ad-hoc basis (i.e. query by query).

Is this possible on Azure Synapse Analytics (pool and/or serverless).

Solution

The simple answer is yes, it is possible to do it on Azure Syanapse Analytics. You can use a feature similar to Resource Governor on SQL Server called Work Load Management {1}. Using a workload group, you can set a parameter called QUERY_EXECUTION_TIMEOUT_SEC to define how long a query can run. Using the workload group you can define the parameter for a user, group of user, label, context, etc. {2}{3}. Please, take a look at the documentation below:

CREATE WORKLOAD GROUP group_name
 WITH
 (   MIN_PERCENTAGE_RESOURCE = value 
   , CAP_PERCENTAGE_RESOURCE = value 
   , REQUEST_MIN_RESOURCE_GRANT_PERCENT = value
  [ [ , ] REQUEST_MAX_RESOURCE_GRANT_PERCENT = value ]
  [ [ , ] IMPORTANCE = { LOW | BELOW_NORMAL | NORMAL | ABOVE_NORMAL | HIGH } ]
  [ [ , ] QUERY_EXECUTION_TIMEOUT_SEC = value ] )
  [ ; ]

{1} https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-workload-management

{2} https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-workload-classification

{3} https://learn.microsoft.com/en-us/sql/t-sql/statements/create-workload-group-transact-sql?toc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Ftoc.json&bc=%2Fazure%2Fsynapse-analytics%2Fsql-data-warehouse%2Fbreadcrumb%2Ftoc.json&view=azure-sqldw-latest&preserve-view=true

{4} https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-workload-isolation#execution-rules