Increase Memory for Queries in SQL Server

The default memory for query execution 'min memory per query' allocated by SQL Server is equal to 1024 KB.

When should I increase the default memory allocated to queries?
1024 KB is sufficient to run queries, however you may need to increase the memory if you have an extremely busy server that runs many concurrent queries simultaneously or your query is quiet resource intensive. Also before increasing the memory, set a few performance benchmarks like the 'Expected Query Time' to determine if you really require an increase.

Note: Do not increase the memory unnecessarily as SQL Server may require it for other operations.

How can I increase memory for query execution?

Just use this query to increase the memory to 1536KB

EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'min memory per query', 1536
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook