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 29,266 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 29,266 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 29,266 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 29,266 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 29,266 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 29,266 Reputation points Microsoft Employee

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

Thank you, anonymous user

Accepted answer

Bhargava-MSFT 29,266 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 29,266 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

Share via

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

0 additional answers