Allow Memory Grant Percent In SQL Server Standard Edition

Voice Of Users

I recently opened a User Voice issue for this, and I’d appreciate if you’d vote for it (note: this link is dead now, long live the first party solution)

From the issue:

In 2016 SP1 we got many features handed down to Standard Edition, but Resource Governor was not one of them.

In Enterprise Edition, Resource Governor is frequently used to reduce the maximum memory grant percent down from 25. That prevents queries that you don’t have control over from totally trashing your server. Since memory grant feedback is not available in Standard Edition, users aren’t left with many options. Query hints are nice, but only if you can change the queries.

Max grant percent should be either settable in Standard Edition, or decoupled from Resource Governor and settable at the database or server level.

Furthers Day

Adding in a bit more editorializing than I thought was appropriate in the User Voice issue, this setting is irresponsibly high for most workloads. If you have Max Server Memory set to 100GB, do you really want queries being able to ask for 25GB of it?

Because, like, they can.

If you have control over the queries and indexes on a server, there are all sorts of potential things you can do to “fix” memory grants. Sometimes they come from bad estimates, other times they come from inflated string column sizes — think about that column you have called “State” that’s an nvarchar max — because the optimizer guesses that every value will be about half the size of the definition.

There are lots of reasons! So many reasons, in fact, that Microsoft has a performance feature called Memory Grant Feedback. The problem is that this feature is only available in Enterprise Edition, and not readily available to Row Mode queries until SQL Server 2019. In SQL Server 2017, you needed some column store/batch mode operations in your query for it to apply.

While there are hints that can be used to control memory grants at the query level, query hints can be hard to apply in some circumstances. Vendor code, ORMs, ad hoc heavy workloads, and more make the broad application of query hints impossible.

Having this as a setting outside of Resource Governor (or, heck, making Resource Governor available in Standard Edition), would be a huge benefit to what seems like a forgotten group of SQL Server customers.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.