Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


About Resource Govrnor


About Resource Govrnor

Author
Message
SillyDragon
SillyDragon
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 188
Yes I did. Can you tell me your OS version, SQL version and CPU information? Thanks
SillyDragon
SillyDragon
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 188
sqlbuddy123 (2/27/2014)

I think it's not like it works only on Win 2008 R2. May be they didn't test it on other OS.

It works for me on Win 7 and Win Server 2012 ..

--
SQLBuddy


Thanks for reply. i will try sql 2012.

Do you find the result of the custom report is different than the Performance Monitor?
sqlbuddy123
sqlbuddy123
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 2243
I feel it's a better way to do the monitoring and easy to interpret as it's an exclusive report for Resource Governor and less likely to misintrepret ..

--
SQLBuddy
Jacob Wilkins
Jacob Wilkins
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2422 Visits: 7596
Another important thing to note is that the max_cpu_percent very, very rarely actually limits anything to that percentage. In SQL Server 2012 they introduced the additional cap_cpu_percent, which is a hard cap that actually does what you expect.

I understand the desire to have limit that only applies when the server is under load, like max_cpu_percent, but I haven't found it to be all that reliable. The cap_cpu_percent in 2012, on the other hand, works like a charm.

Cheers!
SillyDragon
SillyDragon
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 188
Jacob Wilkins (2/27/2014)
Another important thing to note is that the max_cpu_percent very, very rarely actually limits anything to that percentage. In SQL Server 2012 they introduced the additional cap_cpu_percent, which is a hard cap that actually does what you expect.

I understand the desire to have limit that only applies when the server is under load, like max_cpu_percent, but I haven't found it to be all that reliable. The cap_cpu_percent in 2012, on the other hand, works like a charm.

Cheers!


Great suggestion! I will try. Thanks again
ajsql990
ajsql990
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 159
I think you setup the resource pool wrong. Follow the below steps might help your situation.

/*-----------------------------------------------
Step 0: (Optional and for Demo Purpose)
Create Two User Logins
1) ReportUser, 2) PrimaryUser
Use ReportUser login for Reports workload
Use PrimaryUser login for OLTP workload
-----------------------------------------------*/

Step 1: Creating Resource Pool

We are creating two resource pools. 1) Report Server and 2) Primary OLTP Server. We are giving only a few resources to the Report Server Pool as described in the scenario 1 the other server is mission critical and not the report server.
-----------------------------------------------
-- Step 1: Create Resource Pool
-----------------------------------------------
-- Creating Resource Pool for Report Server
CREATE RESOURCE POOL ReportServerPool
WITH
( MIN_CPU_PERCENT=0,
MAX_CPU_PERCENT=30,
MIN_MEMORY_PERCENT=0,
MAX_MEMORY_PERCENT=30)
GO
-- Creating Resource Pool for OLTP Primary Server
CREATE RESOURCE POOL PrimaryServerPool
WITH
( MIN_CPU_PERCENT=50,
MAX_CPU_PERCENT=100,
MIN_MEMORY_PERCENT=50,
MAX_MEMORY_PERCENT=100)
GO

Step 2: Creating Workload Group

We are creating two workloads each mapping to each of the resource pool which we have just created.
-----------------------------------------------
-- Step 2: Create Workload Group
-----------------------------------------------
-- Creating Workload Group for Report Server
CREATE WORKLOAD GROUP ReportServerGroup
USING ReportServerPool ;
GO
-- Creating Workload Group for OLTP Primary Server
CREATE WORKLOAD GROUP PrimaryServerGroup
USING PrimaryServerPool ;
GO

Step 3: Creating user defined function which routes the workload to the appropriate workload group.

In this example we are checking SUSER_NAME() and making the decision of Workgroup selection. We can use other functions such as HOST_NAME(), APP_NAME(), IS_MEMBER() etc.
-----------------------------------------------
-- Step 3: Create UDF to Route Workload Group
-----------------------------------------------
CREATE FUNCTION dbo.UDFClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @WorkloadGroup AS SYSNAME
IF(SUSER_NAME() = 'ReportUser')
SET @WorkloadGroup = 'ReportServerGroup'
ELSE IF (SUSER_NAME() = 'PrimaryUser')
SET @WorkloadGroup = 'PrimaryServerGroup'
ELSE
SET @WorkloadGroup = 'default'
RETURN @WorkloadGroup
END
GO

Step 4: In this final step we enable the resource governor with the classifier function created in earlier step 3.
-----------------------------------------------
-- Step 4: Enable Resource Governer
-- with UDFClassifier
-----------------------------------------------
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.UDFClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO
Step 5: If you are following this demo and want to clean up your example, you should run following script. Running them will disable your resource governor as well delete all the objects created so far.
-----------------------------------------------
-- Step 5: Clean Up
-- Run only if you want to clean up everything
-----------------------------------------------
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR DISABLE
GO
DROP FUNCTION dbo.UDFClassifier
GO
DROP WORKLOAD GROUP ReportServerGroup
GO
DROP WORKLOAD GROUP PrimaryServerGroup
GO
DROP RESOURCE POOL ReportServerPool
GO
DROP RESOURCE POOL PrimaryServerPool
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO


GOOD LUCK
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search