SQL SERVER – Service Broker and CAP_CPU_PERCENT – Limiting SQL Server Instances to CPU Usage
- by pinaldave
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
CREATE RESOURCE POOL ReportServerPool
WITH
( MIN_CPU_PERCENT=0,
MAX_CPU_PERCENT=30,
CAP_CPU_PERCENT=40,
MIN_MEMORY_PERCENT=0,
MAX_MEMORY_PERCENT=30)
GO
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 (http://blog.sqlauthority.com)
Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology Tagged: Service Broker
