設定 min memory per query 伺服器組態選項Configure the min memory per query 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 ServerSQL Server 中設定 SQL Server Management StudioSQL Server Management Studio min memory per query Transact-SQLTransact-SQL伺服器組態選項。This topic describes how to configure the min memory per query server configuration option in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 每個查詢的最小記憶體 選項會指定為執行查詢所配置的最小記憶體數量 (以 KB 為單位)。The min memory per query option specifies the minimum amount of memory (in kilobytes) that will be allocated for the execution of a query. 這也稱為最小記憶體授與。This is also known as the minimum memory grant. 例如,如果將 min memory per query 設成 2,048 KB,就可以保證查詢至少有這些記憶體量可使用。For example, if min memory per query is set to 2,048 KB, the query is guaranteed to get at least that much total memory. 預設值為 1,024 KB。The default value is 1,024 KB. 最小值是 512 KB,最大值則是 2,147,483,647 KB (2 GB)。The minimum value 512 KB, and the maximum is 2,147,483,647 KB (2 GB).

本主題內容In This Topic

開始之前Before You Begin

限制事項Limitations and Restrictions

  • 每個查詢的最小記憶體數量之優先順序,高於 [索引建立記憶體] 選項。The amount of min memory per query has precedence over the index create memory option. 若您同時變更了兩個選項,且索引建立記憶體選項小於每個查詢的最小記憶體,您會看到警告訊息,但仍會設定該值。If you modify both options and the index create memory is less than min memory per query, you receive a warning message, but the value is set. 執行查詢時,您會看到另一個類似的警告。During query execution, you receive another similar warning.

建議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.

  • SQL ServerSQL Server 查詢處理器會嘗試判斷要配置給查詢的最佳記憶體數量。The SQL ServerSQL Server query processor tries to determine the optimal amount of memory to allocate to a query. min memory per query 選項可讓系統管理員指定任何單一查詢所接收的最小記憶體數量。The min memory per query option lets the administrator specify the minimum amount of memory any single query receives. 若查詢中含有大量資料的雜湊和排序作業,則這些查詢通常會接收比此值更多的記憶體。Queries generally receive more memory than this, if they have hash and sort operations on a large volume of data. 提高 min memory per query 的值也許可以改善一些小型至中型查詢的效能,但這樣做也可能導致競用記憶體資源的情形增加。Increasing the value of min memory per query may improve performance for some small to medium-sized queries, but doing so could lead to increased competition for memory resources. 每個查詢的最小記憶體選項包含為排序作業所配置的記憶體。The min memory per query option includes memory allocated for sort operations.

  • 因為查詢必須等到1其可取得要求的最小記憶體,或是等到超過查詢等候伺服器設定選項中指定的值為止,所以請勿將 min memory per query 伺服器設定選項設得太高,特別是在非常忙碌的系統上。Do not set the min memory per query server configuration option too high, especially on very busy systems, because the query has to wait1 until it can secure the minimum memory requested, or until the value specified in the query wait server configuration option is exceeded. 如果可用的記憶體多於執行所需的指定最小值,那麼只要記憶體可以有效地供查詢使用,查詢就會利用額外的記憶體。If more memory is available than the specified minimum value required to execute the query, the query is allowed to make use of the additional memory, provided that the memory can be used effectively by the query.

1 在此情況下,等候類型通常是 RESOURCE_SEMAPHORE。1 In this scenario, the wait type is typically RESOURCE_SEMAPHORE. 如需詳細資訊,請參閱 sys.dm_os_wait_stats (Transact-SQL)For more information, see sys.dm_os_wait_stats (Transact-SQL).

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

設定 min memory per query 選項To configure the min memory per query option

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

  2. 按一下 [記憶體] 節點。Click the Memory node.

  3. [每個查詢的最小記憶體] 方塊中,輸入將為執行查詢而配置的最小記憶體數量 (以 KB 為單位)。In the Minimum memory per query box, enter the minimum amount of memory (in kilobytes) that will be allocated for the execution of a query.

使用 Transact-SQLUsing Transact-SQL

設定 min memory per query 選項To configure the min memory per query 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_configuremin memory per query 選項的值設定為 3500 KB。This example shows how to use sp_configure to set the value of the min memory per query option to 3500 KB.

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'min memory per query', 3500 ;  
GO  
RECONFIGURE;  
GO    

後續操作:設定每個查詢的最小記憶體選項之後Follow Up: After you configure the min memory per query 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)
設定 index create memory 伺服器設定選項 Configure the index create memory Server Configuration Option
sys.dm_os_wait_stats (Transact-SQL) sys.dm_os_wait_stats (Transact-SQL)
sys.dm_exec_query_memory_grants (Transact-SQL)sys.dm_exec_query_memory_grants (Transact-SQL)