25 January,2013 by Jack Vamvas
Question: Will adjusting the Min Memory Per query option improve SQL query performance?
Answer: According to SQL Server Books Online the min memory per query Option will “specify the minimum amount of memory (in kilobytes (KB)) that are allocated for the execution of a query.”
If the min memory option is set at 1024 KB , the query receives as minimum that amount of memory. In reality a query with a large SORT operation may receive more than the min memory setting.
Use sp_configure to view the current Min Memory Per query
1)It is possible increasing the min memory option will help query performance – but if the system is busy\large and multiple transactions are occurring on large datasets, this could lead to memory constraint issue elsewhere.
Keep in mind – the query must wait for memory allocation until it can secure the min memory before query execution
2) If the query requires more memory , and the memory is available, SQL Server will grant the extra memory .As this feature exists – it is not advisable to adjust the Min Memory Per query option