Missing "smallrc" DB role from Synapse Analytics dedicated SQL pools

Ashish Naik 20

Hi All,

I am a data engineer. Since yesterday we have observed that the "smallrc" DB role is missing from all synapse dedicated sql pools in all our environments (dev/stage/prod). All our synapse instances are provisioned in the AU East Azure Region.

Interestingly, when I try running an SQL query with default permissions, it appears as running with the "smallrc" resource class. I checked the resource class using the sys.dm_pdw_exec_requests DMV.

Just wanted to check if anyone else is experiencing the same?

PRADEEPCHEEKATLA-MSFT 90,146 Reputation points Microsoft Employee

2024-07-22T12:18:08.6733333+00:00

@Ashish Naik - Thanks for the question and using MS Q&A platform.

What do you mean by "smallrc" DB role is missing from all synapse dedicated sql pools in all our environments (dev/stage/prod)? How are you checking dynamic resources for your dedicated SQL pools.

By default, each user is a member of the dynamic resource class smallrc.

The resource class of the service administrator is fixed at smallrc and cannot be changed. The service administrator is the user created during the provisioning process. The service administrator in this context is the login specified for the "Server admin login" when creating a new Synapse SQL pool with a new server.

For more details, refer to Workload management with resource classes in Azure Synapse Analytics.

Hope this helps. Do let us know if you any further queries.
Ashish Naik 20 Reputation points

2024-07-22T12:36:21.5266667+00:00
Thanks for your comment @PRADEEPCHEEKATLA-MSFT

What I mean is, when I run the following query, which should give me the corresponding DB roles for each resource class, according to this MS learn page.

SELECT name FROM sys.database_principals WHERE name LIKE '%rc%' AND type_desc = 'DATABASE_ROLE';

The above query returns only the following DB Roles, as you can see "smallrc" DB role is missing, which used to be there earlier as a default DB Role in every synapse dedicated pool.
PRADEEPCHEEKATLA-MSFT 90,146 Reputation points Microsoft Employee

2024-07-22T12:46:48.6766667+00:00

@Ashish Naik - Thanks for the details.

Could you please change a user's resource class to smallrc and see if you are experiencing the issue?
Ashish Naik 20 Reputation points

2024-07-22T13:00:33.8033333+00:00

Hi @PRADEEPCHEEKATLA-MSFT ,

This does cause errors. Whats the best way to raise this bug with MS Support ?

I tried with admin privileges. As expected, it returns an error since it can't find "smallrc" db role anymore.
PRADEEPCHEEKATLA-MSFT 90,146 Reputation points Microsoft Employee

2024-07-23T03:26:10.5366667+00:00

@Ashish Naik - I agree that this issue looks strange. If you have a support plan, could you please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan please let us know here.
Ashish Naik (AU) 0 Reputation points

2024-07-23T04:33:38.12+00:00

Hi @PRADEEPCHEEKATLA-MSFT . Thanks for your help so far.

We are on Basic plan, so I am not able to raise a support request for the technical queries. It would be great, if you could help raise the SR with MS Support.
Ashish Naik 20 Reputation points

2024-07-25T04:28:09.13+00:00

Hi @PRADEEPCHEEKATLA-MSFT , hope you are well. just following up, has this been raised with MS support ?
PRADEEPCHEEKATLA-MSFT 90,146 Reputation points Microsoft Employee

2024-07-25T05:01:36.89+00:00

@Ashish Naik - We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.

Accepted answer

PRADEEPCHEEKATLA-MSFT 90,146 Reputation points Microsoft Employee

2024-07-25T09:59:13.8233333+00:00

@Ashish Naik - Thanks for the question and using MS Q&A platform.

Here is the update from internal PG team:

I don't think smallrc role ever exists.

Note: All query executions will be classified as smallrc unless configured for other resources classes. All users are smallrc by default, until they are assigned to another role.
Perhaps this is because smallrc is assigned to queries by default of no other resource class was assigned due to user’s role membership or WLM.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.

1 person found this answer helpful.
Ashish Naik 20 Reputation points

2024-07-26T08:59:53.2666667+00:00

Thanks @PRADEEPCHEEKATLA-MSFT . I have proposed an update to the MS Learn page to reflect the above more clearly. PR here: https://github.com/MicrosoftDocs/azure-docs/pull/123889

PRADEEPCHEEKATLA-MSFT 90,146 Reputation points Microsoft Employee

2024-08-01T09:57:15.0466667+00:00

@Ashish Naik - We had updated the document: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/resource-classes-for-workload-management

Please do continue to use MS Q&A platform for any question related to Azure!
Sign in to comment

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

Share via

Missing "smallrc" DB role from Synapse Analytics dedicated SQL pools

0 additional answers

Your answer