This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
I was working with a FastTrack customer recently on setting up their Azure Synapse Analytics environment.
The customer asked a question about the best way to utilize resource classes to ensure that logins for specific tasks were given the appropriate level of resources to support the tasks they need to process on the system.
Instead of reviewing resource classes I recommended that we examine one of the new Azure Synapse features, Workload Isolation. Workload Isolation allows you to create your own resource limitations, caps, workload importance, minimums per connection, and bind them to individual logins. This approach offers much more flexibility for user configurations.
If you have ever set up resource governance in SQL Server then this concept will be very similar to you, essentially we were creating Workload Groups with a specific amount of dedicated resources to ensure they can maximize the power of Azure Synapse and then binding those Workload Groups to Workload Classifiers so a login can be bound to a specified Workload Group.
Let’s start by working through a scenario. Let us say that our Azure Synapse database will be utilized by the following login names:
- PowerBIUser – Users running Power BI reports use this login to query data against our Azure Synapse database.
- AASProcessing – This user runs our Azure Analysis Services cube processing.
- PBDataLoad – This user runs data loading from our Azure Data Lake to Azure Synapse using Polybase.
First, I want to validate those are the only users on the system and look for any other users to consider. For Workload Isolation to perform at its most optimum level all logins on an Azure Synapse SQL Pool should be part of the system. We will validate the users using the following script:
SELECT
dp2.name as 'User Name'
,dp1.name as 'Role Name'
FROM
sys.database_role_members drm
LEFT JOIN sys.database_principals dp1
ON drm.role_principal_id=dp1.principal_id
LEFT JOIN sys.database_principals dp2
ON drm.member_principal_id=dp2.principal_id
WHERE
dp2.type_desc<>'DATABASE_ROLE'
AND drm.role_principal_id < 16384
ORDER BY dp2.name desc
As you can see this gives us the users names for my SQL Pool as well as their current roles. Next, I want to understand what the resources are for the current roles. Let’s do that by expanding our query to add the DMV sys.dm_workload_management_workload_groups_stats:
SELECT
dp2.name as 'User Name'
,dp1.name as 'Role Name'
,wmwg.effective_min_percentage_resource as 'Min%'
,wmwg.effective_cap_percentage_resource as 'Cap'
,wmwg.effective_request_min_resource_grant_percent 'MinGrant%'
,wmwg. effective_request_max_resource_grant_percent as 'Max%'
FROM
sys.database_role_members drm
LEFT JOIN sys.database_principals dp1
ON drm.role_principal_id=dp1.principal_id
LEFT JOIN sys.database_principals dp2
ON drm.member_principal_id=dp2.principal_id
LEFT JOIN sys.dm_workload_management_workload_groups_stats wmwg
on dp1.name = wmwg.name
WHERE
dp2.type_desc<>'DATABASE_ROLE'
AND drm.role_principal_id < 16384
ORDER BY dp2.name desc
We can now see the effective minimum, minimum grant percent, max percent, and the cap for the resource.
I would like my percentages to total up to 100% so I can sustain a global Power BI user community, additional cube processing tasks, and more frequent incremental data loading. To do so I’m going to add the following resource caps; Power BI 30%, DataLoad 22%, and AASProcess 48% which totals up to 100%.
First, we will create the Workload Groups with the following script:
CREATE WORKLOAD GROUP PowerBI WITH
( MIN_PERCENTAGE_RESOURCE = 20 -- integer value
,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 2.5 -- factor of 20 (guaranteed a minimum of 8 concurrency)
,CAP_PERCENTAGE_RESOURCE = 30 )
CREATE WORKLOAD GROUP DataLoad WITH
( MIN_PERCENTAGE_RESOURCE = 11 -- integer value
,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 11 -- factor of 11 (guaranteed a minimum of 2 concurrency)
,CAP_PERCENTAGE_RESOURCE = 22 )
CREATE WORKLOAD GROUP AASProcess WITH
( MIN_PERCENTAGE_RESOURCE = 48 -- integer value
,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 24 -- factor of 48 (guaranteed a minimum of 2 concurrency)
,CAP_PERCENTAGE_RESOURCE = 48 )
After we create our Workload Groups, we need to bind these groups to our logins using a classifier. We will do this with the following script:
CREATE WORKLOAD CLASSIFIER PowerBIWC WITH
( WORKLOAD_GROUP = 'PowerBI'
,MEMBERNAME = 'PowerBIUser'
)
CREATE WORKLOAD CLASSIFIER DataLoadWC WITH
( WORKLOAD_GROUP = 'DataLoad'
,MEMBERNAME = 'PBDataLoad'
)
CREATE WORKLOAD CLASSIFIER AASProcessWC WITH
( WORKLOAD_GROUP = 'AASProcess'
,MEMBERNAME = 'AASProcessing'
)
Now let’s run a query against our DMV sys.dm_workload_management_workload_groups_stats to see our new Workload Groups and their allocations next to the standard Resource Classes.
select
wmwg.name
,wmwg.effective_min_percentage_resource as 'Min%'
,wmwg.effective_cap_percentage_resource as 'Cap'
,wmwg.effective_request_min_resource_grant_percent 'MinGrant%'
,wmwg. effective_request_max_resource_grant_percent as 'Max%'
from
sys.dm_workload_management_workload_groups_stats wmwg
Our new process is listed and ready to go. Now let’s test this out. I’m going to kick off my different processes and then we will run the following query to validate the resource usage.
SELECT
resource_class
,importance
,group_name
,classifier_name
,resource_allocation_percentage
,*
FROM
SYS.DM_PDW_EXEC_REQUESTS
WHERE
STATUS='RUNNING'
Perfect! We can see as these processes are executing that the percentages, we defined are the same as those that are running on our system.
One question you may ask is ‘What about the previous Resource Class definitions? Shouldn’t we remove them from the logins?’.
How very perceptive of you Dear Reader. The reason I left them in place is, should I need to change my Workload Groups I would need to first drop the Classifier, then the Workload Group, then re-create the Workload Group.
The Workload Group cannot be dropped while active transactions utilizing that Workload Group have completed. If I want to alter the group and a process is running during that time, if I dropped the previous Resource Classes, then each new process would inherit the default Resource Class of smallrc. The smallrc only grants 5% of resources to a login. That may be ok for my Power BI users, but not for my PBDataLoad or AASCubeProcessing users.
Thanks very much for reading, now go forth and begin working with Workload Isolation!