Limit CPU Usage - 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

Limit CPU Usage

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-08-29 : 11:25:39

is there any way to cap all sql processes?

for example, we remote desktop into our sql server to run stuff, however if there's something that maxes out the cpu to 100%, it's like watching paint dry.

steve_r18
Yak Posting Veteran

59 Posts

Posted - 2011-08-29 : 11:40:35

This can be done use the Resource Governor within the Management Studio. Under the Management Folder, choose Resource Governor then Pools, then you can either create a new one (suggested) or modify the default. Within the settings you choose your options (eg. max cpu), and also create a workgroup. You can then assign users to that workgroup so that running processes under that user will be limited based on the specifications in the pool you created.

Alternatively you can use a script.

BEGIN TRAN
USE master;
-- Create a resource pool that sets the MAX_CPU_PERCENT to 20%.
CREATE RESOURCE POOL pMAX_CPU_PERCENT_20
WITH
(MAX_CPU_PERCENT = 20);
GO
-- Create a workload group to use this pool.
CREATE WORKLOAD GROUP gMAX_CPU_PERCENT_20
USING pMAX_CPU_PERCENT_20;
GO
-- Create a classification function.
-- Note that any request that does not get classified goes into
-- the 'Default' group.
CREATE FUNCTION dbo.rgclassifier_MAX_CPU() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @workload_group_name AS sysname
IF (SUSER_NAME() = 'domain_name\MAX_CPU')
SET @workload_group_name = 'gMAX_CPU_PERCENT_20'
RETURN @workload_group_name
END;
GO

Steve

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-08-29 : 11:50:48

okay so question, lets say i assign user 1 to work_group1, max cpu = 80%
then i assign user 2 to work_group1 as well, and both users run code that would normally tack out the CPU. does it limit that whole group to 80%? or just the user? (combine total 160%, and i'm screwed) i'm assuming it'd limit it to the group. if so, now what if user1 and user2 are on different groups, both w/ 80% caps... same question.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-29 : 12:52:49

It's per group.

Cap 'em lower. I put everyone except for DBAs and the SQL Agent account into the same group and cap that.

Also, this only works for SQL 2008+

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-08-30 : 10:53:27

okay, so i got the gov setup, and the workload, i'm not seeing how i assign a user to the workload.

i see the the script:


      IF (SUSER_NAME() = 'domain_name\MAX_CPU')
          SET @workload_group_name = 'gMAX_CPU_PERCENT_20'

so would i replace 'domain_name\max_cpu' with my login?
or do i create a "group" i guess this is where i'm lost.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-30 : 11:05:29

You create a classifier function, and add define workload groups based on the login. You don't create new logins.

See here http://technet.microsoft.com/en-us/library/bb934084.aspx

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2011-08-30 : 14:23:57

okay, so question, until i have time to implement this, and read a bunch... how can i limit EVERYTHING...

can i just tweak the settings on the system resources pools > default workload? (when i'm in activity monitor, everything is under default)
if so, i already did that, and the server still peaks out @ 100% cpu... does it not always work?

if i can cap it for EVERYTHING for now, and than create work loads for what i need later (when i'm NOT watching paint dry) that would be GREAT! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources