November 19, 2019 at 3:06 am
Hi All ,
Should we adjust the minimum memory per query (in KB) in SQL ?
The default is 1024 KB and I plan to change it into 2048 KB , Would it help boosting SQL performance ?
Also if change it into 2048 KB , should I change Minimum server memory (in MB) into the same number as well ?
Thanks very much for your feedback !
November 19, 2019 at 4:52 am
I have to say that it probably won't do much for performance (if anything at all) but it may cause some memory pressure because you're doubling the minimum of every query including the ones that don't come close to needing it.
Stop looking for a performance panacea. Only a decent database design, effective indexing and, most important of all, well written coe is going to help you with performance. Yeah... changing the threshold for parallelism can help some queries quite a bit but that's a rare exception.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2019 at 4:59 am
Thanks for your response . Much appreciated
I come across this below article ( I only copy the first point /secret )
"SQL Server Settings Optimization Tips
By Alexander Chigrik
Here are nine "secret" tips that you can use to ensure your SQL Server environment is performing in the most efficient manner possible.
1. If your SQL Server has a lot of memory available and there are many queries running concurrently on the server, you can increase the 'min memory per query' option to improve the performance of queries that use hashing or sorting operations.
The SQL Server will automatically allocate, at a minimum, the amount of memory set in this configuration setting. The default 'min memory per query' option is equal to 1024 Kb.
November 19, 2019 at 5:13 am
If you're going to cite an article, it would be polite to provide the link, as well,
Now... notice that the tip says IF your server has a lot of memory... DO YOU!!!??? And why would you want to waste it on possibly thousands of queries that don't need the help?
It also implies a big IF you have a lot of queries that have sorting or hashing going on (which should be fixed, BTW)... DO YOU!!!???
And do you realize that those tips were written for SQL Server 2000? DO YOU!!!???
Some of those recommendations in that article are fine but some of them have been proven to be a bit dangerous, as well. For those that want to see the article, here's the link...
😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2019 at 10:14 am
Sometimes I wonder if people deliberately search the interwebz in the darkest (and especially "oldest") places and try to apply things from 17(!) years ago rather than even giving it a try to fix the actual queries involved. I mean sometimes it might just be some CAST((FLOOR)) thing which you can replace by datepart to already achieve better permanent performance, but on the other hand that would actually require work outside browsing the interwebs for a magic stick.
No offense meant but Jeff is absolutely right, to what extent has SQL 2000 tips any relevance these days anymore? Cardinality Engine changed a lot not to mention all the other bits and pieces of SQL Server.
November 19, 2019 at 12:31 pm
The latest documentation about this setting is at https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-min-memory-per-query-server-configuration-option?view=sql-server-ver15
Includes the warning "could lead to increased competition for memory resources"
Is your system very busy? I assume it is, otherwise you wouldn't be looking for these sort of quick fixes...
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply