配置查询调控器开销限制服务器配置选项Configure the query governor cost limit Server Configuration Option

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题说明了如何使用 SQL ServerSQL Server 中配置 SQL Server Management StudioSQL Server Management Studio “查询调控器开销限制” Transact-SQLTransact-SQL服务器配置选项。This topic describes how to configure the query governor cost limit server configuration option in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 查询调控器开销限制选项指定查询可以运行的时间段上限。The query governor cost limit option specifies an upper limit on the time period in which a query can run. 查询开销是指在特定硬件配置中完成查询所需的估计占用时间(秒)。Query cost refers to the estimated elapsed time, in seconds, that is required to complete a query on a specific hardware configuration. 此选项的默认值为 0,将设置为关闭查询调控器。The default value for this option is 0, which sets the query governor to off. 这将允许所有查询在没有任何时间限制下运行。This allows all queries to run without any time limitation. 如果为该选项指定一个非零、非负的数值,则查询调控器将不允许执行估计开销超过该值的查询。If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost that exceeds that value.

本主题内容In This Topic

开始之前Before You Begin

建议Recommendations

  • 此选项是一个高级选项,仅应由有经验的数据库管理员或认证的 SQL ServerSQL Server 专业人员更改。This option is an advanced option and should be changed only by an experienced database administrator or certified SQL ServerSQL Server professional.

  • 若要在每个连接基础上更改查询调控器开销限制值,请使用 SET QUERY_GOVERNOR_COST_LIMIT 语句。To change the value query governor cost limit on a per-connection basis, use the SET QUERY_GOVERNOR_COST_LIMIT statement.

SecuritySecurity

权限Permissions

默认情况下,所有用户都具备不带参数或仅带第一个参数的 sp_configure 的执行权限。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 若要执行带两个参数的 sp_configure 以更改配置选项或运行 RECONFIGURE 语句,则用户必须具备 ALTER SETTINGS 服务器级别的权限。To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. ALTER SETTINGS 权限由 sysadminserveradmin 固定服务器角色隐式持有。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

配置查询调控器开销限制选项To configure the query governor cost limit option

  1. 在对象资源管理器中,右键单击服务器并选择 “属性”In Object Explorer, right-click a server and select Properties.

  2. 单击 “连接” 页面。Click the Connections page.

  3. 选中或清除“使用查询调控器防止查询长时间运行” 复选框。Select or clear the Use query governor to prevent long-running queries check box.

    如果选中此复选框,请在下面的框中输入一个正值,查询调控器将禁止执行运行长度超过该值的所有查询。If you select this check box, in the box below, enter a positive value, which the query governor uses to disallow execution of any query with a running length exceeding that value.

使用 Transact-SQLUsing Transact-SQL

配置查询调控器开销限制选项To configure the query governor cost limit option

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例说明如何使用 sp_configurequery governor cost limit 选项的值设置为 120 秒。This example shows how to use sp_configure to set the value of the query governor cost limit option to 120 seconds.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'query governor cost limit', 120 ;  
GO  
RECONFIGURE;  
GO  
  

有关详细信息,请参阅 服务器配置选项 (SQL Server)版本的组合自动配置的最大工作线程数。For more information, see Server Configuration Options (SQL Server).

跟进:在配置查询调控器开销限制选项之后Follow Up: After you configure the query governor cost limit option

该设置将立即生效,无需重新启动服务器。The setting takes effect immediately without restarting the server.

另请参阅See Also

RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL) SET QUERY_GOVERNOR_COST_LIMIT (Transact-SQL)
服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL)sp_configure (Transact-SQL)