Resource GovernorResource Governor

适用对象: 是SQL Server 是Azure SQL 数据库(仅限托管实例)否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

SQL ServerSQL Server 资源调控器是一项可用于管理 SQL ServerSQL Server 工作负荷和系统资源使用情况的功能。Resource Governor is a feature than you can use to manage SQL ServerSQL Server workload and system resource consumption. 您可使用资源调控器指定传入应用程序请求可以使用的 CPU、物理 IO 和内存的数量限制。Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.

资源调控器的优势Benefits of Resource Governor

利用资源调控器,您可以通过指定传入请求的资源消耗限制来管理 SQL ServerSQL Server 工作负荷和资源。Resource Governor enables you to manage SQL ServerSQL Server workloads and resources by specifying limits on resource consumption by incoming requests. 在资源调控器上下文中,工作负荷是一组大小相似的查询或请求,可以且应该视为单个实体。In the Resource Governor context, workload is a set of similarly sized queries or requests that can, and should be, treated as a single entity. 这并不是必需的,但是工作负荷的资源使用模式越统一,通过资源调控器可能获得的益处越多。This is not a requirement, but the more uniform the resource usage pattern of a workload is, the more benefit you are likely to derive from Resource Governor. 资源限制可以实时重新配置,对正在执行的工作负荷影响非常小。Resource limits can be reconfigured in real time with minimal impact on workloads that are executing.

在相同服务器上存在多个不同工作负荷的环境中,使用资源调控器可以区分这些工作负荷并能根据指定的限制在请求时分配共享资源。In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. 这些资源是 CPU、物理 IO 和内存。These resources are CPU, physical IO, and memory.

通过使用资源调控器,您可以:By using Resource Governor, you can:

  • 在服务于多个客户端工作负荷的单个 SQL Server 实例上提供多租户和资源隔离。Provide multitenancy and resource isolation on single instances of SQL Server that serve multiple client workloads. 也就是说,您可以将某个服务器上的可用资源划分到多个工作负荷上,并且尽量减少在工作负荷争用资源时可能发生的问题。That is, you can divide the available resources on a server among the workloads and minimize the problems that can occur when workloads compete for resources.

  • 提供可预测性能并且在多工作负荷和多用户环境中支持针对工作负荷租户的 SLA。Provide predictable performance and support SLAs for workload tenants in a multi-workload and multi-user environment.

  • 隔离和限制失控查询,或者中止可能使 IO 子系统饱和以及对其他工作负荷有负面影响的操作(例如 DBCC CHECKDB)的 IO 资源。Isolate and limit runaway queries or throttle IO resources for operations such as DBCC CHECKDB that can saturate the IO subsystem and negatively impact other workloads.

  • 为资源使用退款添加细粒度资源跟踪,并且对服务器资源的使用者提供可预测的计费。Add fine-grained resource tracking for resource usage chargebacks and provide predictable billing to the consumers of the server resources.

资源调控器约束Resource Governor Constraints

此资源调控器版本存在以下约束:This release of Resource Governor has the following constraints:

  • 资源管理仅限于 SQL Server 数据库引擎SQL Server Database EngineResource management is limited to the SQL Server 数据库引擎SQL Server Database Engine. 资源调控器不能用于 Analysis ServicesAnalysis ServicesIntegration ServicesIntegration ServicesReporting ServicesReporting ServicesResource Governor can not be used for Analysis ServicesAnalysis Services, Integration ServicesIntegration Services, and Reporting ServicesReporting Services.

  • SQL Server 实例之间没有工作负荷监视或工作负荷管理。There is no workload monitoring or workload management between SQL Server instances.

  • 资源调控器可以管理 OLTP 工作负荷,但通常持续时间非常短的查询类型总是在 CPU 上停留很短时间而不足以应用带宽控制。Resource Governor can manage OLTP workloads but these types of queries, which are typically very short in duration, are not always on the CPU long enough to apply bandwidth controls. 这样会使返回的 CPU 使用率的统计信息有偏差。This may skew in the statistics returned for CPU usage %.

  • 控制物理 IO 的功能仅适用于用户操作,而不适用于系统任务。The ability to govern physical IO only applies to user operations and not system tasks. 系统任务包括向事务日志的写入操作和 Lazy Writer IO 操作。System tasks include write operations to the transaction log and Lazy Writer IO operations. 资源调控器主要应用于用户读取操作,因为大多数写操作通常由系统任务执行。The Resource Govenor applies primarily to user read operations because most write operations are typically performed by system tasks.

  • 您不能对内部资源池设置 IO 阈值。You cannot set IO thresholds on the internal resource pool.

资源概念Resource Concepts

下面的三个概念是了解和使用资源调控器的基础:The following three concepts are fundamental to understanding and using Resource Governor:

  • 资源池。Resource pools. 资源池表示服务器的物理资源。A resource pool, represents the physical resources of the server. 您可以将池看作 SQL ServerSQL Server 实例内部的一个虚拟 SQL ServerSQL Server 实例。You can think of a pool as a virtual SQL ServerSQL Server instance inside of a SQL ServerSQL Server instance. 当安装 SQL ServerSQL Server 时,会创建两个资源池(内部资源池和默认资源池)。Two resource pools (internal and default) are created when SQL ServerSQL Server is installed. 资源调控器还支持用户定义的资源池。Resource Governor also supports user-defined resource pools. 有关详细信息,请参阅 Resource Governor Resource PoolFor more information, see Resource Governor Resource Pool.

  • 工作负荷组。Workload groups. 工作负荷组充当具有相似分类标准的会话请求的容器。A workload group serves as a container for session requests that have similar classification criteria. 工作负荷允许对会话进行聚合监视,并定义会话的策略。A workload allows for aggregate monitoring of the sessions, and defines policies for the sessions. 每个工作负荷组都处于一个资源池中。Each workload group is in a resource pool. 当安装 SQL ServerSQL Server 时,会创建两个工作负荷组(内部工作负荷组和默认工作负荷组)并将其映射到对应的资源池。Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL ServerSQL Server is installed. 资源调控器还支持用户定义的工作负荷组。Resource Governor also supports user-defined workload groups. 有关详细信息,请参阅 Resource Governor Workload GroupFor more information see, Resource Governor Workload Group.

  • 分类。Classification. 分类过程会根据传入会话的特征将其分配给工作负荷组。The Classification process assigns incoming sessions to a workload group based on the characteristics of the session. 您可以通过编写用户定义函数(称为分类器函数)来定制分类逻辑。You can tailor the classification logic by writing a user-defined function, called a classifier function. 资源调控器还支持用于实现分类规则的分类器用户定义的函数。Resource Governor also supports a classifier user-defined function for implementing classification rules. 有关详细信息,请参阅 Resource Governor Classifier FunctionFor more information, see Resource Governor Classifier Function.

备注

资源调控器不向专用管理员连接 (DAC) 施加任何控制。Resource Governor does not impose any controls on a dedicated administrator connection (DAC). 无需对在内部工作负荷组和资源池中运行的 DAC 查询进行分类。There is no need to classify DAC queries, which run in the internal workload group and resource pool.

在资源调控器的上下文中,您可以将前面的概念视为组件。In the context of Resource Governor, you can treat the preceding concepts as components. 下图显示了这些组件及其在数据库引擎环境中相互之间的关系。The following illustration shows these components and their relationship with each other as they exist in the database engine environment. 从处理的角度,简化的流程如下所示:From a processing perspective, the simplified flow is as follows:

  • 会话有传入的连接(Session 1,共 n个)。There is an incoming connection for a session (Session 1 of n).

  • 会话已分类(分类)。The session is classified (Classification).

  • 会话工作负荷将路由到工作负荷组,例如,Group 4。The session workload is routed to a workload group, for example, Group 4.

  • 工作负荷组使用与其关联的资源池,例如,Pool 2。The workload group uses the resource pool it is associated with, for example, Pool 2.

  • 资源池提供并限制应用程序所需的资源,例如,Application 3。The resource pool provides and limits the resources required by the application, for example, Application 3.

资源调控器功能组件Resource Governor Functional Components

资源调控器任务Resource Governor Tasks

任务说明Task Description 主题Topic
说明如何启用资源调控器。Describes how to enable Resource Governor. 启用资源调控器Enable Resource Governor
说明如何禁用资源调控器。Describes how to disable Resource Governor. 禁用资源调控器Disable Resource Governor
说明如何创建、更改和删除资源池。Describes how to create, alter, and drop a resource pool. 资源调控器资源池Resource Governor Resource Pool
说明如何创建、更改、移动和删除工作负荷组。Describes how to create, alter, move, and drop a workload group. 资源调控器工作负荷组Resource Governor Workload Group
说明如何创建和测试分类器用户定义函数。Describes how to create and test a classifier user-defined function. 资源调控器分类器函数Resource Governor Classifier Function
说明如何使用模板配置资源调控器。Describes how to configure Resource Governor using a template. 使用模板配置资源调控器Configure Resource Governor Using a Template
说明如何查看资源调控器属性。Describes how to view Resource Governor properties. 查看资源调控器属性View Resource Governor Properties

另请参阅See Also

数据库引擎实例 (SQL Server)Database Engine Instances (SQL Server)