SQL Server – Min Memory Per query and performance

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

 Some notes:

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

Read More

 SQL Server Performance ,memory pressure and memory usage

SQL Server – SORT IN TEMPDB and Sort Warnings - SQL Server DBA

Performance Top Queries by Total CPU - SQL Server DBA

SQL Server – Measuring SQL memory usage - SQL Server DBA

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on SQL Server – Min Memory Per query and performance

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer