Comprehensive workload management capabilities for your analytic workloads

by | Jul 10, 2020 | Uncategorized | 0 comments

This article is contributed. See the original author and article here.

Workload isolation in Azure Synapse Analytics is now generally available where you can use workload groups to reserve, contain, and manage resources across different workloads running on your SQL pool. Previously users had to provision, secure, and manage multiple pools with different endpoints orchestrating additional ETL processes to achieve workload isolation and avoid resource contention. This can become an expensive and a complex process to manage where you can end up with multiple production environments each with potentially hundreds of end users running queries at different times. There are also scenarios where adhoc end users can submit poorly written and runaway queries which consume unnecessary resources. Having comprehensive workload management capabilities is critical to achieving consistent performance and reducing time to insight to meet your business objectives.

 

With the general availability of workload isolation, you can use familiar T-SQL syntax to quickly ensure resources are appropriately allocated and managed for your most demanding workloads. Workload isolation can all be done within a single pool where you no longer need to manage multiple endpoints. You can also configure query timeouts to cancel any runaway queries without having to build alerting infrastructure or manually connect to your database to cancel these queries.

 

The simple example below demonstrates how to configure a single workload group where 100% of resources from the SQL pool will be allocated to a single load. This is an extreme example of workload isolation which can be used when you need to initially onboard and ingest a dataset as quickly as possible:

 

CREATE WORKLOAD GROUP adhocdataload
WITH ( 
        MIN_PERCENTAGE_RESOURCE = 100
      , CAP_PERCENTAGE_RESOURCE = 100
      , REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
      , QUERY_EXECUTION_TIMEOUT_SEC = 900 --Load cannot be longer than 15 minutes
);

CREATE WORKLOAD CLASSIFIER [adhocloader]
WITH (
        WORKLOAD_GROUP = 'adhocdataload'
      , WLM_LABEL = 'COPYStatement'
      , MEMBERNAME = 'myloaduser'
);

 

Requests coming from the ‘myloaduser’ user will automatically be classified to the ‘adhocdataload’ workload group which will be given 100% of the SQL pool resources.

 

For more information on workload isolation, see the following documentation:

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment