SQL Server Resource Governor

  • [アーティクル]

Here is a podcast on the SQL Server Resource Governor and some related sample code. Shows examples of classifier functions, resource pools and groups, etc..

https://channel9.msdn.com/posts/mwilmot/SQL-Server-Resource-Governor/

 

Here is an overview of the SQL Server Resource Governor in slides and some demos.
SQL Server 2008 provides Resource Governor, a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU and memory that incoming application requests can use.

Resource Governor is configurable in SQL Server Management Studio by using Transact-SQL statements or by using Object Explorer.

Resource Governor is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

use master
GO
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

sp_configure
GO

sp_configure 'affinity mask', 1;
RECONFIGURE;
GO

CREATE FUNCTION fv1()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
    DECLARE @val sysname
    if 'User1' = SUSER_SNAME()
        SET @val = 'gSlow';
    else if 'User2' = SUSER_SNAME()
        SET @val = 'gFast';
    return @val;
END
GO

-- Create pool for monitoring
CREATE RESOURCE POOL pSlow
WITH (MAX_CPU_PERCENT = 100)

CREATE RESOURCE POOL pFast
WITH (MAX_CPU_PERCENT = 100)

-- group definitions based on users
CREATE WORKLOAD GROUP gSlow
USING pSlow

CREATE WORKLOAD GROUP gFast
USING pFast

GO

CREATE LOGIN User1 WITH PASSWORD = 'u1', CHECK_POLICY = OFF
CREATE LOGIN User2 WITH PASSWORD = 'u2', CHECK_POLICY = OFF
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=dbo.fv1)
ALTER RESOURCE GOVERNOR RECONFIGURE

go

-- verify the classification
select
    sess.session_id, sess.group_id, grps.name
from
    sys.dm_exec_sessions as sess join
    sys.dm_resource_governor_workload_groups as grps on sess.group_id = grps.group_id
where
    session_id > 50

--
ALTER RESOURCE POOL pSlow
WITH (MAX_CPU_PERCENT = 20)

ALTER RESOURCE POOL pFast
WITH (MAX_CPU_PERCENT = 80)

ALTER RESOURCE GOVERNOR RECONFIGURE

Open perfmon
Counters:
    -sql server resource pool load
    -sql server workload
    -cpu usage for pfast & pslow
-open 2 query analyzers and run the LOAD below for each user to get a sense of how the RG allocates resources to each. You can view the RG resources in SQL Server Mgmt Studio under Mgmt->Resource Governor
-1 for u1 and 1 for u2
------------------------------

---CREATE THE LOAD
set nocount on
declare @i int=100000000;
declare @s varchar(100);
declare @x float(10);

while @i > 0
begin
    select @s = @@version;
    --if (@i % 5 = 0)
        select @x=VAR(s1.object_id) FROM sys.all_columns s1 INNER JOIN sys.all_columns s2 ON s1.object_id = s2.object_id WHERE s1.name LIKE '%a%'
    set @i = @i - 1;
end

----------------------

TEAR DOWN

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION=null)
ALTER RESOURCE GOVERNOR RECONFIGURE
ALTER RESOURCE GOVERNOR DISABLE
ALTER RESOURCE GOVERNOR RECONFIGURE
DROP FUNCTION fv1
DROP LOGIN User1
DROP LOGIN User2
DROP WORKLOAD GROUP gSlow
DROP WORKLOAD GROUP gFast
DROP RESOURCE POOL pSlow
DROP RESOURCE POOL pFast

Comments