SQL Server 2014资源调控器

最新推荐文章于 2024-07-20 08:00:00 发布

culuo4781

最新推荐文章于 2024-07-20 08:00:00 发布

阅读量389

点赞数

文章标签：数据库 python linux java mysql

原文链接：https://www.sqlshack.com/sql-server-2014-resource-governor/

版权

SQL Server Resource Governor was introduced in SQL Server 2008. This feature is used to control the consumption of the available resources, by limiting the amount of the CPU, Memory and IOPS used by the incoming sessions, preventing performance issues that are caused by resources high consumption.

SQL Server 2008中引入了SQL Server资源调控器。此功能用于通过限制传入会话使用的CPU，内存和IOPS的数量来控制可用资源的消耗，从而防止由于资源过高而导致的性能问题。消费。

The Resource Governor simply differentiates the incoming workload and allocates the needed CPU, Memory and IOPS resources based on the predefined limits for each workload. In this way, the SQL Server resources will be divided among the current workloads reducing the possibility of consuming all resources by single workload type, while competing for the available resources. A minimum resources limit can be also specified in the Resource Governor, which allows you to set the proper resource level for each workload type.

资源调控器可以轻松区分传入的工作负载，并根据每个工作负载的预定义限制分配所需的CPU，内存和IOPS资源。这样，SQL Server资源将在当前工作负载之间分配，从而减少了在争用可用资源的同时按单个工作负载类型消耗所有资源的可能性。还可以在资源调控器中指定最小资源限制，该限制使您可以为每种工作负载类型设置适当的资源级别。

The Resource Governor feature is very useful when you have many databases hosted in your SQL Server and many applications connecting to these databases. These connections are competing for the available SQL Server resources, affecting each other’s performance. Using the Resource Governor feature will overcome this kind of performance issue.

当您有许多数据库托管在SQL Server中并且有许多应用程序连接到这些数据库时，资源调控器功能非常有用。这些连接正在争夺可用SQL Server资源，从而影响彼此的性能。使用资源调控器功能将克服这种性能问题。

There are three main components that form the Resource Governor; Resource Pools, Workload Groups and the Classifier. Small chunks of the CPU, Memory and IOPS resources are collectively called the Resource Pool. A set of defined connections is known as Workload Group. The component that is responsible for classifying incoming connections to workload groups, depending predefined criteria, is called the Classifier. We will review each these components, in detail, in this article.

构成资源调控器的主要部分包括三个部分： 资源池 ， 工作负荷组和分类器 。 CPU，内存和IOPS资源的一小部分统称为资源池。一组定义的连接称为工作负载组。根据预定义的标准，负责对工作负载组的传入连接进行分类的组件称为分类器。我们将在本文中详细审查每个组件。

Resource Pools represent virtual instances of all of the available SQL Server CPU, Memory and IOPS resources. There are two built-in resource pools created when you install the SQL Server; the Internal Pool, which is used for the SQL Server background tasks, and the Default Pool, which is used to serve all user connections that are not directed to any user-defined resource pool. The internal pool can’t be modified, as it is used for serving SQL Server background processes. If the internal pool needs all the SQL Server available resources for a specific internal task, it will be given priority over all other user-defined pools. SQL Server supports up to 64 user-defined resource pools.

资源池代表所有可用SQL Server CPU，内存和IOPS资源的虚拟实例。安装SQL Server时会创建两个内置资源池。 内部池 （用于SQL Server后台任务）和默认池 （用于服务未定向到任何用户定义的资源池的所有用户连接）。内部池不可修改，因为它用于服务SQL Server后台进程。如果内部池需要用于特定内部任务的所有SQL Server可用资源，则将给予它比所有其他用户定义的池更高的优先级。 SQL Server最多支持64个用户定义的资源池。

While creating resource pools, you need to specify the CPU, Memory and IOPS minimum and maximum limitations for each pool. The MIN_CPU_PERCENT parameter specifies the minimum CPU used by all requests in the created pool, which takes effect when CPU contentions occur, as the value will be available for other pools if there is no activity in the pool. This value can be between 0 and 100, with the sum of the MIN_CPU_PERCENT value for all pools not more than 100%. The MAX_CPU_PERCENT parameter specifies the maximum CPU used by all requests in the created pool. Same as the MIN_CPU_PERCENT parameter, the MAX_CPU_PERCENT takes effect when CPU contentions occurred, as the value will be available for other pools if there is no activity in the pool. The MAX_CPU_PERCENT value can’t be less than the MIN_CPU_PERCENT value. For the MIN_MEMORY_PERCENT setting, it will be reserved for the pool whether it is used or not, and will not be released for the other pools if there is no activity on that pool, which will affect the overall performance if you set a high value for less frequently used pool. Again the MAX_MEMORY_PERCENT parameter should be more than the MIN_MEMORY_PERCENT value.

创建资源池时，需要为每个池指定CPU，内存和IOPS的最小和最大限制。 MIN_CPU_PERCENT参数指定创建的池中所有请求使用的最小CPU，该值在发生CPU争用时生效，因为如果该池中没有活动，则该值可用于其他池。该值可以在0到100之间，所有池的MIN_CPU_PERCENT值之和不超过100％。 MAX_CPU_PERCENT参数指定创建的池中所有请求使用的最大CPU。与MIN_CPU_PERCENT参数相同，MAX_CPU_PERCENT在发生CPU争用时生效，因为如果该池中没有活动，则该值可用于其他池。 MAX_CPU_PERCENT的值不能小于MIN_CPU_PERCENT的值。对于MIN_MEMORY_PERCENT设置，无论是否使用该池，它将保留给该池使用；如果该池上没有任何活动，则不会为其他池释放该设置；如果为该池设置较高的值，它将影响整体性能。较少使用的池。同样， MAX_MEMORY_PERCENT参数应大于MIN_MEMORY_PERCENT值。

Workload Groups are containers for user and system sessions with a common classification type that will be mapped to one resource pool. There are two built-in workload groups created when the SQL Server is installed; internal SQL Server activities are grouped into the Internal Workload Group, which is mapped to the Internal Pool, and user activities that are not directed to any user-defined workload group, which will be grouped into the Default Workload Group that is mapped to the Default Pool.

工作负载组是具有通用分类类型的用户和系统会话的容器，它将被映射到一个资源池。安装SQL Server时，会创建两个内置的工作负荷组。内部SQL Server活动分为“ 内部工作负载组” （映射到“内部池”）和未定向到任何用户定义的工作负载组的用户活动，这些用户活动将分组到“ 默认工作负载”组，该组又映射到“默认”游泳池。

There are many parameters that you can tune during the workload group creation process, such as specifying the session’s importance within the group, otherwise referred to as IMPORTANCE. Importance is compared and affected to the same resource pool. Importance can be LOW, MEDIUM and HIGH, where MEDIUM is the default value.

您可以在工作负载组创建过程中调整许多参数，例如指定组中会话的重要性，否则称为IMPORTANCE 。比较重要性并将其影响到同一资源池。重要性可以是LOW，MEDIUM和HIGH，其中MEDIUM是默认值。

The REQUEST_MAX_CPU_TIME_SEC parameter specifies the maximum CPU time that can be used by the session. An alert will be raised if the session exceeds that value without interrupting the session. The value 0 indicates no limit for session’s CPU time.

REQUEST_MAX_CPU_TIME_SEC参数指定会话可以使用的最大CPU时间。如果会话超过该值而不会中断会话，则会发出警报。值0表示会话的CPU时间没有限制。
The MAX_DOP value specifies that maximum degree of parallelism used by the parallel sessions. This value will override the default server MAXDOP value for the parallel sessions. The MAX_DOP value can be between 0 and 64.

MAX_DOP值指定并行会话使用的最大并行度。该值将覆盖并行会话的默认服务器MAXDOP值。 MAX_DOP值可以在0到64之间。
The REQUEST_MEMORY_GRANT_TIMEOUT_SEC parameter specifies the maximum time that the query will wait to be granted memory once it is available.

REQUEST_MEMORY_GRANT_TIMEOUT_SEC参数指定查询在可用后将等待等待等待的最大时间。
The REQUEST_MAX_MEMORY_GRANT_PERCENT specifies the maximum memory that the session can use from the resource pool. The default value for the
REQUEST_MAX_MEMORY_GRANT_PERCENT is 25.
REQUEST_MAX_MEMORY_GRANT_PERCENT指定会话可以从资源池使用的最大内存。的默认值
REQUEST_MAX_MEMORY_GRANT_PERCENT是25。
The GROUP_MAX_REQUEST specifies the maximum number of concurrent sessions that can be executed in the workload group.

GROUP_MAX_REQUEST指定工作负载组中可以执行的最大并发会话数。

The Classifier is a function that is used to categorize incoming sessions into the appropriate workload group. Many system functions can be used to classify the incoming sessions such as: HOST_NAME (), APP_NAME (), SUSER_NAME (), SUSER_SNAME (), IS_SRVROLEMEMBER (), and IS_MEMBER (). Only one classifier function can be used to direct the sessions to the related workload group. It is better to keep the classifier function as simple as possible, as it will be used to evaluate each incoming session, and with complex function it will slow down the incoming queries and affect overall performance.

分类器是一项功能，用于将传入的会话分类到适当的工作负荷组中。许多系统功能可用于对传入的会话进行分类，例如：HOST_NAME（），APP_NAME（），SUSER_NAME（），SUSER_SNAME（），IS_SRVROLEMEMBER（）和IS_MEMBER（）。只能使用一个分类器功能将会话定向到相关的工作负荷组。最好使分类器函数尽可能简单，因为它将用于评估每个传入的会话，而使用复杂的函数，它将减慢传入的查询并影响整体性能。

We described each component individually, now we need to combine it all together to know how the Resource Governor works. Simply, once the session connected to the SQL Server, it will be classified using the classifier function. The session will be routed to the appropriate workload group. This workload group will use the resources available in the associated resource pool. The resource pool will provide the connected session with limited resources.

我们分别描述了每个组件，现在需要将所有组件组合在一起以了解资源调控器的工作方式。只需将会话连接到SQL Server，即可使用分类器功能对其进行分类。该会话将被路由到适当的工作负荷组。该工作负载组将使用关联资源池中的可用资源。资源池将为连接的会话提供有限的资源。

The process of configuring the SQL Server Resource Governor is simple; first you need to create the resource pools, then create a workload group and map it to a resource pool, after that the classification function should be created to classify the incoming requests and finally the resource governor will be enabled with the created classification function.

配置SQL Server资源调控器的过程很简单。首先，您需要创建资源池，然后创建工作负载组并将其映射到资源池，之后应创建分类功能以对传入的请求进行分类，最后将使用创建的分类功能启用资源调控器。

Let’s start with creating two resource pools, the ServicePool that will be used for the service accounts and the UserPool that will be used for the user accounts as below:

让我们从创建两个资源池开始，如下所示：将用于服务帐户的ServicePool和将用于用户帐户的UserPool：

 
CREATE RESOURCE POOL [ServicePool] WITH(
min_cpu_percent=50, 
		max_cpu_percent=100, 
		min_memory_percent=50, 
		max_memory_percent=100, 
		AFFINITY SCHEDULER = AUTO
)
 
GO
 
CREATE RESOURCE POOL [UserPool] WITH(
min_cpu_percent=0, 
		max_cpu_percent=30, 
		min_memory_percent=0, 
		max_memory_percent=30, 
		AFFINITY SCHEDULER = AUTO
)
 
GO

As you can see, the ServicePool assigned resources more than the UserPool, in order to give priority for the requests coming from the application side over the users’ ad-hoc queries.

如您所见，ServicePool比UserPool分配了更多资源，以使来自应用程序端的请求优先于用户的即席查询。

Now the two resource pools are ready, we will start creating two workload groups; ServiceGroup that will be mapped to the ServicePool resource pool and the UserGroup that will be mapped to UserPool resource pool, keeping all other parameters with its default values as follows:

现在两个资源池已经准备好了，我们将开始创建两个工作负载组。将映射到ServicePool资源池的ServiceGroup和将映射到UserPool资源池的UserGroup，将所有其他参数保留为其默认值，如下所示：

 
CREATE WORKLOAD GROUP [ServiceGroup] 
USING [ServicePool]
GO
CREATE WORKLOAD GROUP [UserGroup] 
USING [UserPool]
GO

To make sure that the changes will take effect, the RECONFIGURE statement should be run as below:

为了确保更改将生效，RECONFIGURE语句应如下运行：

 
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Now that the resource pools and workload groups have been created and configured successfully, ou can now create the resource pools and workload groups simply using SQL Server Management Studio. Expand the Management node from the Object Explorer, then right-click on the Resource Governor and choose New Resource Pools as follows:

现在已经成功创建和配置了资源池和工作负载组，现在您可以使用SQL Server Management Studio创建资源池和工作负载组。从“ 对象资源管理器”中展开“ 管理”节点，然后右键单击“ 资源 调控器” ，然后选择“ 新资源池” ，如下所示：

The Resource Governor Properties window will be displayed, where you will find two default resource pools in the top grid, the Default and Internal resource pools. You can create the two user-defined resource pools created previously by specifying the pool name and the CPU and Memory min and max limitations. Then by clicking on each created pool, you can create workload groups that will be mapped to the selected resource pool. And finally enable the resource governor by checking the Enable resource Governor checkbox as below:

将显示“ 资源调控器属性”窗口，您将在顶部网格中找到两个默认资源池，即“默认”和“内部”资源池。您可以通过指定池名称以及CPU和内存的最小和最大限制来创建先前创建的两个用户定义的资源池。然后，通过单击每个创建的池，您可以创建将映射到所选资源池的工作负载组。最后，通过选中“启用资源调控器”复选框来启用资源调控器，如下所示：

You will not be able to choose the classifier function that will be used in the previous window as it is not created yet. Refresh the Resource Governor node to check that the resource pools and the workload groups created successfully:

您将无法选择将在前一个窗口中使用的分类器功能，因为它尚未创建。刷新资源调控器节点，以检查资源池和工作负载组是否已成功创建：

You can also use the sys.resource_governor_resource_pools and sys.resource_governor_workload_groups DMVs to list the created resource pools and workload groups with all settings as in the following simple SELECT statements with the results:

您还可以使用sys.resource_governor_resource_pools和sys.resource_governor_workload_groups DMV列出具有所有设置的已创建资源池和工作负载组，如以下简单的SELECT语句中所示，并显示结果：

 
SELECT * FROM sys.resource_governor_resource_pools

 
SELECT * FROM sys.resource_governor_workload_groups

Now, we will create the classification function that will be used to classify and route the incoming requests to the appropriate workload group. In this example, we will classify the incoming requests depending on the user name as service or normal users, using the SUSER_SNAME() system function as follows:

现在，我们将创建分类功能，该功能将用于分类传入的请求并将其路由到适当的工作负载组。在此示例中，我们将使用SUSER_SNAME（）系统函数，根据用户名将传入请求分类为服务用户还是普通用户：

 
USE master;
GO
 
CREATE FUNCTION Class_funct() RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
  DECLARE @workload_group sysname;
 
  IF ( SUSER_SNAME() = 'ramzy')
      SET @workload_group = 'UserGroup';
  IF ( SUSER_SNAME() = 'SQLShackDemoUser')
      SET @workload_group = 'ServiceGroup';
     
  RETURN @workload_group;
END;

To enable the Resource Governor using the created classification function, use the ALTER RESOURCE GOVERNOR query below:

要使用创建的分类功能启用资源调控器，请使用以下ALTER RESOURCE GOVERNOR查询：

 
USE master
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Class_funct);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;

The sys.resource_governor_configuration DMV can be used to make sure that the Resource Governor is enabled as follows:

可以使用sys.resource_governor_configuration DMV来确保已启用资源调控器，如下所示：

 
SELECT * FROM sys.resource_governor_configuration

Congratulations, the Resource Governor is configured successfully and will start catching each connected session, routing the connections from SQLSHACKDEMO user to the ServiceGroup, the connections from Ramzy user to UserGroup, the SQL Server background processes to the Internal workload group and any other connections, to the Default workload group.

恭喜，资源调控器已成功配置，并将开始捕获每个已连接的会话，将SQLSHACKDEMO用户与ServiceGroup的连接，Ramzy用户与UserGroup的连接，SQL Server后台进程与内部工作负载组的连接以及所有其他连接路由到默认工作负载组。

The below query can be used monitor the incoming sessions with the workload group for each session except the internal sessions (remove the WHERE clause to monitor all sessions):

以下查询可用于监视除内部会话以外的每个会话以及工作负载组的传入会话（删除WHERE子句以监视所有会话）：

 
USE master
GO
SELECT ConSess.session_id, ConSess.login_name,  WorLoGroName.name
  FROM sys.dm_exec_sessions AS ConSess
  JOIN sys.dm_resource_governor_workload_groups AS WorLoGroName
      ON ConSess.group_id = WorLoGroName.group_id
  WHERE session_id > 60;

The results will be like:

结果将类似于：

We can also use the performance monitor counters to monitor the Resource Governor. The CPU usage % counter from the SQLServer: Workload Group Stats counters set is used to monitor the CPU usage by all requests in the selected workload group. And the Used memory (KB) performance counter from the SQLServer: Resource Pool Stats counters set retrieves the amount of memory used by each resource pool.

我们还可以使用性能监视器计数器来监视资源调控器。 “ SQLServer：工作负载组统计”计数器集中的CPU使用率百分比计数器用于监视所选工作负载组中所有请求的CPU使用率。 SQLServer：“资源池状态”计数器集中的“已用内存（KB）”性能计数器将检索每个资源池使用的内存量。

Assume that we will run the below DBCC CHECKDB queries concurrently by the SQLSHACKDEMO and Ramzy users in order to monitor both the CPU and Memory usage per each workload group they belong to:

假设我们将由SQLSHACKDEMO和Ramzy用户同时运行以下DBCC CHECKDB查询，以便监视他们所属的每个工作负载组的CPU和内存使用情况：

 
USE master
 GO
 DBCC checkdb (AdventureWorks2012 )
 GO
 DBCC checkdb (AdventureWorksDW2012 )
 GO
 DBCC checkdb (ApexSQLCrd )
 GO
 DBCC checkdb (ApexSQLMonitor )
 GO
 DBCC checkdb (SQLShackDemo )
 GO

Open the Windows Performance Monitor window from the Administrative tools or by writing Perfomon on the Run window, then click on Plus (+) icon to add the needed performance counters. First we will choose the CPU Usage% counter from the SQLServer: Workload Group Stats counters set for the two user-defined resource pools created previously as follows:

通过管理工具打开Windows Performance Monitor窗口，或在Run窗口中编写Perfomon，然后单击加号（+）图标添加所需的性能计数器。首先，我们将从SQLServer中选择CPU使用率计数器：工作负载组统计信息 为先前创建的两个用户定义的资源池设置计数器，如下所示：

As you can see in the middle part of the result below, the ServicePool represented by the red line is assigned more CPU than the UserPool represented by the green line, as the CPU for the UserPool is limited to 30 percent only. Once the query that is running on the ServicePool finished, the UserPool limitation is overridden and assigned all CPU resources requested by that session to complete the query as appeared in the last part of the below graph. Once both queries are finished, all CPU resources released back to the other pools showing zero CPU percent for both pools below:

正如您在下面结果的中间部分所看到的，红线代表的ServicePool比绿线代表的UserPool分配了更多的CPU，因为UserPool的CPU仅限制为30％。一旦在ServicePool上运行的查询完成，将覆盖UserPool限制并分配该会话请求的所有CPU资源以完成查询，如下图的最后一部分所示。两个查询完成后，所有释放回其他池的CPU资源将显示以下两个池的CPU百分比为零：

To discuss the memory usage on each pool, choose the Used memory (KB) counter from the SQLServer: Resource Pool Stats counters set for the two user-defined resource pools created previously as follows:

若要讨论每个池上的内存使用情况，请从SQLServer：资源池统计信息中选择“已用内存（KB）”计数器。为先前创建的两个用户定义的资源池设置计数器，如下所示：

It is clear from the middle part of the graph below, that the ServicePool represented by the blue line assigned more memory than the UserPool represented by the purple line during the concurrent run of both sessions. Once the session running in the ServicePool completed, the session running on the UserPool will not override the defined limit and will complete running in the same memory limit. The other thing that we should mention is that, once the sessions are finished, the memory will not be completely released, reserving the minimum memory required for the sessions, although there is no incoming requests on that pools as follows:

从下图的中间部分可以明显看出，在两个会话的并发运行期间，由蓝线表示的ServicePool比由紫线表示的UserPool分配了更多的内存。一旦在ServicePool中运行的会话完成，在UserPool中运行的会话将不会覆盖已定义的限制，并且将在相同的内存限制下完成运行。我们应该提到的另一件事是，一旦会话完成，内存将不会被完全释放，保留会话所需的最小内存，尽管该池上没有传入请求，如下所示：

The SQL Server 2014 Resource Governor allows you to limit another server resource in addition to the CPU and Memory to manage the server performance, which is the IOPS (Input Output Operations per Second) per volume. If one of your applications performs an intensive IO load, it will affect the overall SQL Server IO performance, slowing down the other applications connecting to that SQL Server. With this new addition in SQL Server 2014, you can control the IO consumption by specifying the MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME parameters while creating the resource pool.

SQL Server 2014资源调控器允许您限制CPU和内存以外的其他服务器资源来管理服务器性能，即每卷IOPS （每秒输入输出操作）。如果您的一个应用程序执行大量的IO负载，则将影响SQL Server IO的整体性能，从而减慢其他应用程序与该SQL Server的连接速度。使用SQL Server 2014中的新增功能，您可以在创建资源池时通过指定MIN_IOPS_PER_VOLUME和MAX_IOPS_PER_VOLUME参数来控制IO消耗。

Let’s modify the ServicePool and the UserPool that we created in our demo to set the MAX_IOPS_PER_VOLUME value using ALTER RESOURCE POOL query below:

让我们修改我们在演示中创建的ServicePool和UserPool，以使用下面的ALTER RESOURCE POOL查询来设置MAX_IOPS_PER_VOLUME值：

 
ALTER RESOURCE POOL ServicePool WITH (Max_IOPS_PER_VOLUME=1500);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
ALTER RESOURCE POOL UserPool WITH (Max_IOPS_PER_VOLUME=500);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

The sys.dm_resource_governor_resource_pools DMV can be used again to check our resource pools settings including the CPU, Memory and IOPS limits as follows:

可以再次使用sys.dm_resource_governor_resource_pools DMV来检查我们的资源池设置，包括CPU，内存和IOPS限制，如下所示：

 
SELECT pool_id , name, min_cpu_percent ,max_cpu_percent ,min_memory_percent ,max_memory_percent ,min_iops_per_volume ,max_iops_per_volume   from sys.dm_resource_governor_resource_pools

The result will be like:

结果将如下所示：

The Disk Read IO/Sec performance counter from the SQLServer: Resource Pool Stats counters set can be used to monitor the IOPS load for the two user-defined resource pools created previously as follows:

SQLServer：资源池状态计数器设置的磁盘读取IO /秒性能计数器可用于监视先前创建的两个用户定义的资源池的IOPS负载，如下所示：

You can see from the below graph how the Resource Governor limits the IOPS used by the UserPool represented by the blue line not to exceed the 500 IOPS, where it allow the ServicePool to exceed that value but limited to 1500 IOPS as per our configuration. Once the sessions finished, both will release the IOPS to the other pools on that SQL Server:

从下图可以看到，资源调控器如何限制用蓝色线表示的UserPool使用的IOPS不超过500 IOPS，在此情况下，它允许ServicePool超过该值，但根据我们的配置限制为1500 IOPS。会话结束后，两者都将IOPS释放到该SQL Server上的其他池中：

If you arrange to modify the classification function or disable the Resource Governor, you should disconnect the Resource Governor from the classification function by assigning it to NULL as follows:

如果您打算修改分类功能或禁用资源调控器，则应通过将其分配给NULL来断开资源调控器与分类功能的连接，如下所示：

 
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = null);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;

Null means that all incoming requests will be routed to the Default pool. To complete disabling the Resource Governor, run the simple ALTER RESOURCE GOVERNOR query below:

Null表示所有传入请求都将路由到默认池。要完全禁用资源调控器，请运行以下简单的ALTER RESOURCE GOVERNOR查询：

 
ALTER RESOURCE GOVERNOR DISABLE;
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;

You need to make sure that all sessions connected to that SQL server are stopped, in order to completely disable the Resource Governor, and the best way to guarantee that is restarting the SQL Server Service using the SQL Server Configuration Manager.

您需要确保连接到该SQL Server的所有会话都已停止，以便完全禁用资源调控器，并且最好的方法是使用SQL Server配置管理器重新启动SQL Server Service。

结论： (Conclusion:)

The SQL Server Resource Governor enables you to limit the CPU, Memory and recently the IOPS resources requested by the incoming sessions. In this way, you will prevent high resource consuming queries from affecting the performance of other queries and slowing down the applications connecting to the SQL Server.

SQL Server资源调控器使您可以限制传入会话请求的CPU，内存和最近的IOPS资源。这样，您可以防止消耗大量资源的查询影响其他查询的性能，并减慢连接到SQL Server的应用程序的速度。

On the down side, the Resource Governor has few limitations, such as it can be used to limit the resources only for the SQL Server Database Engine, and can’t be configured to limit the other SQL Server components such as Reporting Services, Integration services or Analysis Services. Also, the Resource Governor has no control over system activities that can consume all server resources, and only limit user activities.

不利的一面是，资源调控器没有什么限制，例如可以用于限制SQL Server数据库引擎的资源，不能配置为限制其他SQL Server组件（例如Reporting Services，集成服务）或分析服务。而且，资源调控器无法控制可以消耗所有服务器资源的系统活动，而只能限制用户活动。

翻译自: https://www.sqlshack.com/sql-server-2014-resource-governor/

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL Server 2014资源调控器

SQL Server Resource Governor was introduced in SQL Server 2008. This feature is used to control the consumption of the available resources, by limiting the amount of the CPU, Memory and IOPS used ...
复制链接

扫一扫