January 23, 2018 at 2:02 am
Hello, i have a single question.
Is the column size_in_bytes from sys.dm_exec_cached_plans a correct value for to determine the best "mínimum memory per query"?
Thanks for all.
January 23, 2018 at 2:10 am
I don't think so, no - that's the amount of space the plan takes up in the cache.
John
January 23, 2018 at 3:23 am
Unless you have a really good reason to be fiddling with that config setting, rather leave it alone.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2018 at 8:32 am
Thanks for the answers.
Some instances can execute 15000-20000 procedures in one minute.
Cached plans more or less 12500.
Maybe, to put 768KB in Minimum memory per query helps in memory pressure with a busy OLTP.
January 23, 2018 at 10:28 am
Min memory has nothing to do with cached plans or plan size.
I strongly recommend that you leave that setting alone.
In a busy OLTP system, most queries should not need memory grants, and hence will be unaffected by that setting. If you've got lots of queries needing memory grants, then identifying those queries and tuning them to reduce or eliminate the memory grant will probably get you better results.
And keep in mind that if a query needs memory and gets less than it needs, it spills to TempDB (potentially multiple times) which is horribly slow and is something you want to avoid on a busy OLTP system.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply