min memory per query Option
Use the min memory per query option to specify the minimum amount of memory (in kilobytes (KB)) that are allocated for the execution of a query. 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. You can set min memory per query to any value from 512 through 2,147,483,647 KB (2 gigabytes (GB)). The default is 1,024 KB.
The Microsoft SQL Server query processor attempts to determine the optimal amount of memory to allocate to a 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. 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 sorting and replaces the sort pages option in Microsoft SQL Server 7.0 or earlier.
Note
The amount of min memory per query has precedence over the index create memory Option. In Microsoft SQL Server 2000 these options were independent, while in Microsoft SQL Server 2005 they interact. If you alter 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.
The min memory per query option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change min memory per query only when show advanced options is set to 1. The setting takes effect immediately (without a server restart).
See Also
Concepts
Setting Server Configuration Options
index create memory Option
Other Resources
RECONFIGURE (Transact-SQL)
sp_configure (Transact-SQL)