question

sakuraime avatar image
0 Votes"
sakuraime asked Yufeishao-msft commented

Resource Governor Workload Group : Request_MAX_CPU_TIME_SEC

what's the meaning of the workload group setting : Request_MAX_CPU_TIME_SEC??


May I understand if set Request_MAX_CPU_TIME_SEC=1 for that workload group , if the login in that workload group execute a query need 10s CPU time , the query can't run ????

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered Yufeishao-msft commented

Hi @sakuraime

Request_MAX_CPU_TIME_SEC: specifies the maximum amount of CPU time, in seconds, that a request can use
By default, Resource Governor will not prevent a request from continuing if the maximum time is exceeded, however, an event will be gengerated

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'll add that in Microsoft SQL Server 2014, 2016 and 2017, it will be like I said, value must be 0 or a positive integer. The default setting for value is 0, which means unlimited.
and from 2017, Trace Flag(TF) 2422 is enabled, and if a request exceeds the maximum time set by REQUEST_MAX_CPU_TIME_SEC, then the request is aborted. Additionally, you may receive an error message 10961
the syntax:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-workload-group-transact-sql?view=sql-server-ver15

1 Vote 1 ·

so it's used with a traceflag 2422 .

0 Votes 0 ·

yes, you can, Request_MAX_CPU_TIME_SEC=value you can decide for yourself, and Trace Flag(TF) 2422 will help abort the request if the CPU usage exceeds the maximum configured limit for the workload group.

0 Votes 0 ·

thanks . and so what's the actual use case for this setting?

0 Votes 0 ·

about alter workload group:https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-workload-group-transact-sql?view=sql-server-ver15#examples

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·

If the reply above was helpful please mark as accepted answer so it can be helpful for other community members with same questions.

0 Votes 0 ·