設定 query governor cost limit 伺服器組態選項Configure the query governor cost limit Server Configuration Option

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

此主題描述如何使用 ,在 SQL Server 2017SQL Server 2017 中設定 SQL Server Management StudioSQL Server Management Studio query governor cost limit Transact-SQLTransact-SQL伺服器組態選項。This topic describes how to configure the query governor cost limit server configuration option in SQL Server 2017SQL Server 2017 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.

  • 若要根據每個連接變更 query governor cost limit 值,請使用 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.

安全性Security

權限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. 系統管理員 (sysadmin)serveradmin 固定伺服器角色會隱含 ALTER SETTINGS 權限。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

設定 query governor cost limit 選項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

設定 query governor cost limit 選項To configure the query governor cost limit option

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase 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)