MAX CPU Governor not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

MAX CPU Governor not working

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-09-13 : 13:09:29

for some reason, my resource governor is not limiting the CPU to 80%, any ideas?

if i look under activity monitor, everything that is a user process is under the dbas/admins workload group, yet we're tacking out the cpu constantly.


/****** Object:  ResourcePool [DBAs/Admins]    Script Date: 09/13/2011 10:23:47 ******/
CREATE RESOURCE POOL [DBAs/Admins] WITH(min_cpu_percent=0, 
		max_cpu_percent=80, 
		min_memory_percent=0, 
		max_memory_percent=80)

GO

/****** Object:  WorkloadGroup [DBAs/Admins]    Script Date: 09/13/2011 10:23:42 ******/
CREATE WORKLOAD GROUP [DBAs/Admins] WITH(group_max_requests=0, 
		importance=Medium, 
		request_max_cpu_time_sec=0, 
		request_max_memory_grant_percent=25, 
		request_memory_grant_timeout_sec=0, 
		max_dop=0) USING [DBAs/Admins]
GO

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-13 : 16:23:56

Index rebuilds happening? Those are allowed to exceed the specified values.

Sure your classifier function is working properly?

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-09-13 : 22:54:01

no index rebuilds going on not. anything i can do to test to see that things are running under it properly? right now i just look in the activity monitor, and all the user processes are running under this group.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-13 : 23:40:22

I don't have the answer to your question here, but I'd like to figure out why your CPU utilization is so high. The most likely cause of your CPU problem is missing indexes. Have you identified the queries with high CPU and verified their execution plans? Have you run the missing indexes report? What is your maxdop set to?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-09-14 : 00:16:26

tara, your right. 90% of the time it's because tables dont have indexes on them, thing is, 90% of the files we import, we have to go though and run counts on all of the columns to get a feel for the data before we create the real ddl for the table. (i.e. field widths and frequency reports). running stuff like this kills the server, so that's where a govenor would Really help.

as far as the maxdop, i have no clue what that is :) any clarification?

basically, i'm really just trying to reserver some of the CPU for normal windows ops, so that when the server does tack out, i'm not sitting watching paint dry.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-14 : 13:01:50

Maxdop - sp_configure 'max degree of parallelism'

Yours is probably set to 0 if you aren't aware of this option. How many CPUs do you have?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-09-14 : 15:20:25

right now, the machine only has 1 CPU, 4 CORES. i have another CPU i'm gunna put into it, but need to sche. some down time before that happens.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources