min memory per query Option

Use the min memory per query option to specify the minimum amount of memory (in kilobytes) that will be allocated for the execution of a query. For example, if min memory per query is set to 2048 kilobytes (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 2147483647 KB (2 gigabytes). The default is 1024 KB.

The Microsoft® SQL Server™ 2000 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 will receive. Queries will 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 could lead to increased contention for memory resources. min memory per query includes memory allocated for sorting and replaces the sort pages option in SQL Server version 7.0 or earlier.

min memory per query 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 stop and restart).

To set minimum query memory