Synapse Analytics Dedicated SQL Pool - user not assigned to workload classifier

Anonymous

I created a new user to the dedicate sql pool: CREATE LOGIN [testwg] WITH PASSWORD=N'*****' GO CREATE USER testwg FROM LOGIN testwg GO EXEC sp_addrolemember 'db_datareader', 'testwg' I created a new workload group: CREATE WORKLOAD GROUP w_group1 WITH ( MIN_PERCENTAGE_RESOURCE = 0 , CAP_PERCENTAGE_RESOURCE = 50 , REQUEST_MIN_RESOURCE_GRANT_PERCENT = 25 ,REQUEST_MAX_RESOURCE_GRANT_PERCENT = 25 ,IMPORTANCE = NORMAL ) and I created a new classifier assigned to the previous group and user: CREATE WORKLOAD CLASSIFIER w_classifier1 WITH ( WORKLOAD_GROUP = 'w_group1' ,MEMBERNAME = 'testwg' ,WLM_LABEL = 'test_label' ,IMPORTANCE = NORMAL) but when I run a query with the testwg user, this user seems not assigned to the correct classifier: ![251055-chrome-c316qjdmxl.png][1] [1]: /api/attachments/251055-chrome-c316qjdmxl.png?platform=QnA

Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2022-10-18T18:45:10.103+00:00
Hello anonymous user,

Welcome to the MS Q&A platform.

It looks like you missed the below step. In order to assign a resource class to a user, we need to add a role member to that user.

EXEC sp_addrolemember 'xlargerc', 'testwg'

Detailed steps(with all steps):

on master
CREATE LOGIN [testwg] WITH PASSWORD=N'somepassword'
GO
on userdb
CREATE USER testwg FROM LOGIN testwg
GO
EXEC sp_addrolemember 'db_datareader', 'testwg'

execute as user ='testwg';
select current_user;

add rolemember to the user

EXEC sp_addrolemember 'xlargerc', 'testwg' --(open from a different window as testwg has read-only access)

select * from dbo.adf

go to synapse studio and validate this

![251732-image.png][1]

I hope this helps. Please let me know if you have any further questions.
Anonymous

2022-10-19T06:48:12.58+00:00

Hi @Bhargava-MSFT ,
I don't need to assign an available resource class (xlargerc) class to a user.
I need to create a new workload classifier "w_classifier1", this classifier have to use the previously created "w_group1" workload group and I want add the "testwg" user to the classifier.
Thanks for your help.
Filippo
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2022-10-19T19:41:40.153+00:00

Hello anonymous user,
Thank you so much for the details.
Yes, you are right. I am also not able to see the workload classifiers from my end. I will check with my internal team and get back to you with more details.
Anonymous

2022-10-21T10:49:59.817+00:00

Hi @Bhargava-MSFT , yes, if I remove "WLM_LABEL" the user is correctly assigned to the group. But if I change workload group properties it seems that resource consumptions doesn't change propertly. See examples below: Test 1: CREATE LOGIN [testwg] WITH PASSWORD=N'*****' GO CREATE USER testwg FROM LOGIN testwg GO EXEC sp_addrolemember 'db_datareader', 'testwg' CREATE WORKLOAD GROUP w_group1 WITH ( MIN_PERCENTAGE_RESOURCE = 25 , CAP_PERCENTAGE_RESOURCE = 25 , REQUEST_MIN_RESOURCE_GRANT_PERCENT = 25 ,REQUEST_MAX_RESOURCE_GRANT_PERCENT = 25 ,IMPORTANCE = NORMAL ) CREATE WORKLOAD CLASSIFIER w_classifier1 WITH ( WORKLOAD_GROUP = 'w_group1' ,MEMBERNAME = 'testwg' ,WLM_LABEL = 'test_label' ,IMPORTANCE = NORMAL) ![252927-chrome-ow05q7x7dj.png][1] ![252995-chrome-zl7ydy8lgu.png][2] Test 2 ALTER WORKLOAD GROUP w_group1 WITH ( MIN_PERCENTAGE_RESOURCE = 25 , CAP_PERCENTAGE_RESOURCE = 50 , REQUEST_MIN_RESOURCE_GRANT_PERCENT = 25 ,REQUEST_MAX_RESOURCE_GRANT_PERCENT = 50 ,IMPORTANCE = NORMAL ) ![252937-chrome-33inithe8w.png][3] ![253007-chrome-7srgacsqbd.png][4] [1]: /api/attachments/252927-chrome-ow05q7x7dj.png?platform=QnA [2]: /api/attachments/252995-chrome-zl7ydy8lgu.png?platform=QnA [3]: /api/attachments/252937-chrome-33inithe8w.png?platform=QnA [4]: /api/attachments/253007-chrome-7srgacsqbd.png?platform=QnA
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2022-10-21T18:58:53.56+00:00
Hello anonymous user,

You need to provide the "option (label ='test_label')" in your query when you use the label. Then it should point to the correct classifier. I was able to test this from my end. It's working as expected.

Sample query:

select * from adf option (label ='test_label')

I guess this is causing your resource consumption issue.
Anonymous

2022-10-24T07:44:38.5+00:00

Hi @Bhargava-MSFT , using option (label ='test_label') in the query nothing changes: ![253438-chrome-bxk97u3xyb.png][1] ![253400-chrome-0nvbagke6q.png][2] [1]: /api/attachments/253438-chrome-bxk97u3xyb.png?platform=QnA [2]: /api/attachments/253400-chrome-0nvbagke6q.png?platform=QnA
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2022-10-24T18:47:23.57+00:00

Hello anonymous user,

My understanding is that you are not seeing the correct resource allocation for CPU and Memory.

I have tested both cases(alter workload group with different values) and I see it is working as expected.
Can you please re-check the time stamp on the filter?

If you still seeing the resources are not being utilized properly with the workload classifiers, I would suggest you open a support case for further troubleshooting. In case if you don't have a support plan, please let me know so that I can enable one-time free support request for you to work closely with support team on this matter.

After alter:
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2022-10-26T23:18:16.12+00:00

Hello anonymous user,
I am checking to see if you get a chance to look into my above response. If you still have any further questions, please let me know.
Anonymous

2022-10-27T12:27:36.35+00:00

Hi @Bhargava-MSFT ,
I opened a support case.
Thank you
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2022-10-27T18:21:32.943+00:00

Thank you, anonymous user

Accepted answer

Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2022-10-19T20:04:57.997+00:00
Hello anonymous user,

The problem seems to be with the label in the work load classifier. Can you please remove the WLM_LABEL and try again(this is optional parameter)

,WLM_LABEL = 'test_label'

If you are using label, make sure to use 'option (label ='test_label') in your query.

Please use the below syntax:

CREATE WORKLOAD CLASSIFIER w_classifier1 WITH ( WORKLOAD_GROUP = 'w_group1' ,MEMBERNAME = 'testwg' ,IMPORTANCE = NORMAL)
Please sign in to rate this answer.
Bhargava-MSFT 31,116 Reputation points Microsoft Employee

2022-10-20T16:59:34.217+00:00

Hello anonymous user,
I am checking in to see if you get a chance to look into my above response. Please let me know if you have any further questions.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Synapse Analytics Dedicated SQL Pool - user not assigned to workload classifier

0 additional answers

Your answer