October 30, 2012 at 10:29 am
Hi, How can I know what is the CAP limit for the CPU and how can i set it to the optimal value
October 30, 2012 at 10:41 am
What do you mean by cap limit?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2012 at 11:03 am
select name,max_cpu_percent,max_memory_percent from sys.dm_resource_governor_resource_pools
Max CPU Percentage settings in a SQL Server 2008 R2. How do I know what would be the optimal percentage to configure. Is there a way to find out how my CPU is been utilized on an average so that I can set the max_cpu_percent and max_memory_percent
October 30, 2012 at 11:24 am
That's part of resource governor, allows sessions to be classified and have some limits applied to them.
As for what's optimal or best, that depends on your apps, db usage, how the apps connect to the db and a whole bunch more. I suggest you have a read through BoL on resource governor, see how it's used.
Typically you'd limit queries that are not as critical and leave critical ones uncapped. Note that it's not a hard cap, it's relative to what else is running within SQL.
The max memory usage does not apply to the buffer pool, it's query workspace memory, again you have to test carefully and analyse what your app is doing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2012 at 11:45 am
Hi Master Gail. Thanks for quick reply.
I just found out that Resource Governor of all of out instances in the clusters are managed by WSRM. How can I know exactly to how much CPU % my application is set in that Node?
October 30, 2012 at 12:01 pm
Resource governor (SQL feature) is not managed by Windows Server Resource Manager. They are two completely different things. The former allows usage of certain resources by sessions within SQL to be limited, the latter manages and limits processes in Windows.
I suggest you do some reading up on what resource governor is and how it works.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2012 at 1:11 pm
Hi Master Gail... Like the way you educated on Log space, Can you give me a preferred Link to Study in depth on Resource Governor. There are a lot online not sure which one would be a good start..
Sorry for trouble....
October 30, 2012 at 1:25 pm
I did in my first post. Books Online (the SQL documentation)
p.s. Please stop calling me that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply