SQL SERVER – Service Broker and CAP_CPU_PERCENT – Limiting SQL Server Instances to CPU Usage

I have mentioned several times on this blog that the best part of blogging is the questions I receive from readers. They are often very interesting. The questions from readers give me a good idea what other readers might be thinking as well. After reading my earlier article Simple Example to Configure Resource Governor – Introduction to Resource Governor – I received an email from a reader and we exchanged a few emails. After exchanging emails we both figured out what is going on. It was indeed interesting and reader suggested to that I should blog about it.  I asked for permission to publish his name but he does not like the attention so we will just call him Jeff. I have converted our emails into chat for easy consumption.

Jeff: Your script does not work at all. I think either there is a bug in SQL Server.
Pinal: Would you please explain in detail?
Jeff: Your code does not limit the CPU usage?
Pinal: How did you measure it?
Jeff: Well, we have third party tools for it but let us say I have limited the resources for Reporting Services and used your script described in your blog. After that I ran only reporting service workload the CPU is still used more than 100% and it is not limited to 30% as described in your script. Clearly something is wrong somewhere.
Pinal: Did you say you ONLY ran reporting server load?
Jeff: Yeah, to validate I ran ONLY reporting server load and CPU did not throttle at 30% as per your script.
Pinal: Oh! I get it here is the answer – CAP_CPU_PERCENT = 30. Use it.
Jeff: What is that, I think your earlier script says it will throttle the Reporting Service workload and Application/OLTP workload and balance it.
Pinal: Exactly, that is correct.
Jeff: You need to write more in email buddy! Just like your blogs, your answers do not make sense! No Offense!
Pinal: Hmm…feedback well taken. Let me try again.

In SQL Server 2012 there are a few enhancements with regards to SQL Server Resource Governor. One of the enhancement is how the resources are allocated. Let me explain you with examples.

Configuration: [Read Earlier Post]

Reporting Workload: MIN_CPU_PERCENT=0, MAX_CPU_PERCENT=30
Application/OLTP Workload: MIN_CPU_PERCENT=50, MAX_CPU_PERCENT=100

Example 1: If there is only Reporting Workload on the server:
SQL Server will not limit usage of CPU to only 30% workload but SQL Server instance will use all available CPU (if needed). In another word in this scenario it will use more than 30% CPU.

Example 2: If there is Reproting Workload and heavy Application/OLTP workload:
SQL Server will allocate a maximum of 30% CPU resources to Reporting Workload and allocate remaining resources to heavy application/OLTP workload.

The reason for this enhancement is for better utilization of the resources. Let us think, if there is only single workload, which we have limited to max CPU usage to 30%. The other unused available CPU resources is now wasted. In this situation SQL Server allows the workload to use more than 30% resources leading to overall improved/optimized performance. However, in the case of multiple workload where lots of resources are needed the limits specified in MAX_CPU_PERCENT are acknowledged.

Example 3: If there is a situation where the max CPU workload has to be enforced:
This is a very interesting scenario, in the case when the max CPU workload has to be enforced irrespective of the workload and enhanced algorithm, the keyword CAP_CPU_PERCENT is essential. It specifies a hard cap on the CPU bandwidth that all requests in the resource pool will receive. It will never let CPU usage for reporting workload to go over 30% in our case. You can use the key word as follows:

-- Creating Resource Pool for Report Server

Notice that there is MAX_CPU_PERCENT=30 and CAP_CPU_PERCENT=40, what it means is that when SQL Server Instance is under heavy load under different workload it will use the maximum CPU at 30%. However, when the SQL Server instance is not under workload it will go over the 30% limit. However, as CAP_CPU_PERCENT is set to 40, it will not go over 40% in any case by limiting the usage of CPU. CAP_CPU_PERCENT puts a hard limit on the resources usage by workload.

Jeff: Nice Pinal, you should blog about it.

[A day passes by]

Pinal: Jeff, it is done! Click here to read it.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Service Broker, SQL Scripts
Previous Post
SQL SERVER – A Puzzle – Swap Value of Column Without Case Statement
Next Post
SQL SERVER – Finding Size of a Columnstore Index Using DMVs

Related Posts

6 Comments. Leave new

  • CAP_CPU_PERCENT don’t work on sqlserver 2008R2 ?

  • Nick Tornese
    July 29, 2014 2:00 am

    Please consider a server with multiple instances of SQL Server 2012 (for this example 2 instances). Now set CAP_CPU_PERCENT for the default pool on both instances to 45%. Will each instance independently use 45% with overall server utilization for SQL Server capped at 90%? Or will the 2 instances compete for the same 45% CPU? I’ll setup this in my lab this week to test, but in the meantime thought I’d ask someone like you who has probably already tested and reached the conclusion.

    • Nick Tornese
      July 29, 2014 8:30 pm

      I just tested in my lab and each instance will independently use 45% with overall server utilization capped at 90.

      I love it when I answer my own question!

  • I’m missing was the service broker has to do with this…

  • Hi ,

    instead of application wise, can i restrict the load database wise? i used the cpu cap and condition as below to control database wise, but still it is crossing the cpu_cap limit and not going to the cpu_pool, going to default pool. (monitored with perfmon counters) if db_name()=’tempdb’
    return ‘control_group’

  • drinking12many
    July 16, 2019 8:22 pm

    yogigollapudi seeing the same thing. I know this is an old post but I set up a database set a hard cap of 30% and used sp_xtp_bind_db_resource_pool to assign it to that pool and verified it. I then offlined/onlined the database and just as you said it still crosses the percentage. Id rather do it per database because I cant always say when another user/group gets added to the database for access.


Leave a Reply