設定 query wait 伺服器組態選項Configure the query wait Server Configuration Option

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

此主題描述如何使用 ,在 SQL Server 2019SQL Server 2019 中設定 SQL Server Management StudioSQL Server Management Studio query wait Transact-SQLTransact-SQL伺服器組態選項。This topic describes how to configure the query wait server configuration option in SQL Server 2019SQL Server 2019 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 如果因為記憶體不足,無法執行會使用大量記憶體的查詢 (例如涉及排序與雜湊的查詢),則這些查詢會排入佇列中。Memory-intensive queries (such as those involving sorting and hashing) are queued when there is not enough memory available to run the query. query wait 選項會指定逾時前,查詢等候資源的秒數 (從 0 到 2147483647)。這個選項的預設值是 -1。The query wait option specifies the time, in seconds (from 0 through 2147483647), that a query waits for resources before it times out. The default value for this option is -1. 這表示逾時時間就會是估計查詢成本的 25 倍。This means the time-out is calculated as 25 times the estimated query cost.

重要

當查詢在等候記憶體時,包含等候中查詢的交易可能會持有鎖定。A transaction that contains the waiting query might hold locks while the query waits for memory. 在極少數的情況下,可能會發生無法偵測的死結。In rare situations, it is possible for an undetectable deadlock to occur. 減少查詢等候時間會降低發生這類死結的可能性。Decreasing the query wait time lowers the probability of such deadlocks. 最後,等待的查詢會終止,並釋放其交易鎖定。Eventually, a waiting query will be terminated and the transaction locks released. 然而增加等候時間的上限,可能會增加終止前的查詢時間量。However, increasing the maximum wait time may increase the amount of time for the query to be terminated. 不建議您更改這個選項。Changes to this option are not recommended.

本主題內容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.

安全性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

若要設定查詢等候選項To configure the query wait option

  1. 在物件總管中,請以滑鼠右鍵按一下伺服器,然後選取 [屬性] 。In Object Explorer, right-click a server and select Properties.

  2. 按一下 [進階] 節點。Click the Advanced node.

  3. [平行處理原則] 下方,為 query wait 選項輸入想要的值。Under Parallelism, type the desired value for the query wait option.

使用 Transact-SQLUsing Transact-SQL

若要設定查詢等候選項To configure the query wait 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 wait 選項的值設定為 7500 秒。This example shows how to use sp_configure to set the value of the query wait option to 7500 seconds.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'query wait', 7500 ;  
GO  
RECONFIGURE;  
GO  
  

如需詳細資訊,請參閱 伺服器設定選項 (SQL Server)伺服器組態選項。For more information, see Server Configuration Options (SQL Server).

後續操作:設定查詢等候選項之後Follow Up: After you configure the query wait option

設定會立即生效,不需要重新啟動伺服器。The setting takes effect immediately without restarting the server.

另請參閱See Also

RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
伺服器組態選項 (SQL Server) Server Configuration Options (SQL Server)
sp_configure (Transact-SQL)sp_configure (Transact-SQL)