Resource Governor

最新推荐文章于 2024-08-30 16:12:34 发布

OnlyQi

最新推荐文章于 2024-08-30 16:12:34 发布

阅读量1.4k

点赞数

分类专栏： SQL Server数据库

SQL Server数据库专栏收录该内容

128 篇文章

订阅专栏

Resource Governor 可以将数据库负载根据用户要求，分散到自己可使用的资源中。从而限制不同的数据库访问程序可以使用的资源。

例如有两个程序app1和app2分别使用数据库。使用Resource Governor可以让app1只能使用1颗cpu和1G内存，app2可以使用3颗cpu和2G内存。

从而在程序层面上对其使用的资源进行监管。

主要的概念有：

Resource pools. Two resource pools (internal and default) are created when SQL Server 2012 is installed. Resource Governor also supports user-defined resource pools.
Workload groups. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server 2012 is installed. Resource Governor also supports user-defined workload groups.
Classification. There are internal rules that classify incoming requests and route them to a workload group. Resource Governor also supports a classifier user-defined function for implementing classification rules.

http://msdn.microsoft.com/en-us/library/bb933866.aspx

另外一篇参考文章：

http://msdn.microsoft.com/en-us/library/bb895389%28v=sql.105%29.aspx

在使用了Resource Governor后我们还可以使用以下SQL来查看各个group的负载情况。

-- use this to verify which sessions have the correct
-- session classification.
USE master;
SELECT s.session_id, s.login_name, s.login_time, s.group_id, g.name as group_name,
CAST(s.host_name as nvarchar(20)) as host_name
FROM sys.dm_exec_sessions AS s 
JOIN sys.dm_resource_governor_workload_groups AS g 
ON s.group_id = g.group_id
WHERE session_id > 50
ORDER BY g.name
GO



-- Finds SQL Running in various groups --
SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, t.text 
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_resource_governor_workload_groups g ON g.group_id = r.group_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY g.name
GO



--Get specifics about the resource pools and workload groups
USE master
SELECT * FROM sys.resource_governor_resource_pools
SELECT * FROM sys.resource_governor_workload_groups
GO


--- Get the classifier function Id and state (enabled).
SELECT object_name(classifier_function_id) AS [schema_name], is_enabled
FROM sys.resource_governor_configuration
GO

另外一个人写的浅显易懂的blog：

http://blog.sqlauthority.com/2012/06/04/sql-server-simple-example-to-configure-resource-governor-introduction-to-resource-governor/

和同一个人写的另外一个补充：

http://blog.sqlauthority.com/2012/06/09/sql-server-service-broker-and-cap_cpu_percent-limiting-sql-server-instances-to-cpu-usage/

最后是和resource governor 相关的动态视图。

http://technet.microsoft.com/en-us/library/bb895339(v=sql.105).aspx