SQLTeam.com | Weblogs | Forums

Minimum memory per query

We are experiencing lots of memory pressure on one of our servers with corresponding resource_semiphore waits.

We have lots of simple queries that run 30,000+ times per hour. When I look in query store at these queries I see this...

Average Memory Consumption (KB) = 1024
Minimum Memory Consumption (KB) = 1024
Maximum Memory Consumption (KB) = 1024

I noticed that the default setting for "Minimum memory per query" for the database server is 1024 KB which explains the numbers above. I've read lots of articles that warn against increasing that value unless you have lots of excess memory but in this scenario it seems to make sense to reduce it. The vast majority of queries on this system don't need that much memory but are being forced to use it which is creating memory pressure. Is there any reason I shouldn't reduce it to 512?

Not if you're sure your queries (almost) never need more than that.

Why not try 768 first?

What does the historical performance show? and review once in a while. if you set it low and historically minimum is bigger then you might have some blocking issues? I would base it on historical trends?