Minimum memory

  • WhiteLotus

    SSChampion

    Points: 11893

    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 !

     

    Attachments:
    You must be logged in to view attached files.
  • Jeff Moden

    SSC Guru

    Points: 995124

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • WhiteLotus

    SSChampion

    Points: 11893

    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.

  • Jeff Moden

    SSC Guru

    Points: 995124

    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...

    https://www.databasejournal.com/features/mssql/article.php/1436951/SQL-Server-Settings-Optimization-Tips.htm

    😉

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • DinoRS

    SSCrazy

    Points: 2564

    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.

  • Thomas Rushton

    SSC-Insane

    Points: 22624

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply