配置每次查询占用的最小内存服务器配置选项Configure the min memory per query 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 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 设置为 2048 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

  • min memory per query 的量优先于 index create memory 选项。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. min memory per query 选项包括为排序操作分配的内存。The min memory per query option includes memory allocated for sort operations.

  • 不要将 min memory per query 服务器配置选项设置得太高,尤其是在非常繁忙的系统上,因为查询将不得不等到1能确保占有所请求的最小内存、或直到时间超过 query wait 服务器配置选项内所指定的值。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).

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 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

配置每次查询占用的最小内存选项To configure the min memory per query 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_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)