Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

About Resource Govrnor Expand / Collapse
Author
Message
Posted Thursday, February 27, 2014 1:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:58 PM
Points: 29, Visits: 128
Yes I did. Can you tell me your OS version, SQL version and CPU information? Thanks
Post #1546120
Posted Thursday, February 27, 2014 2:03 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:58 PM
Points: 29, Visits: 128
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?
Post #1546124
Posted Thursday, February 27, 2014 2:17 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:14 PM
Points: 1,194, Visits: 2,217
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
Post #1546131
Posted Thursday, February 27, 2014 3:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:59 PM
Points: 169, Visits: 433
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!
Post #1546157
Posted Friday, February 28, 2014 10:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 7:58 PM
Points: 29, Visits: 128
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
Post #1546470
Posted Wednesday, March 5, 2014 11:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:45 PM
Points: 29, Visits: 133
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
Post #1547961
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse