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?