sql server enterprise edition or standard

  • Hello,

    I was looking over the Edition specifications, and Standard is capped at 128 GB RAM is that correct?Can someone recommend me to use sql server enterprise edition or standard, My company is mid size, i am trying to reduce cost like everyone,but want make do it right.

    I'm not sure how to get around the 128 GB  per instance limit given our current resource requirements.

    Unlimitted/OS Capacity for Enterprise

    I think this may be an issue, given that we use over 500 GB of RAM right now. so will probably need to have with Enterprise. what do you think?I mean, unless we're intending to split up the primary DB into multiple instances, is that a good idea split to instanses or go with enterprise? Thank you

  • If you're using 500GB now, it could be very hard to squeeze down to 128GB.

    It's likely, however, that you are not using data compression now.  From SQL 2016 on, that is available in Std Ed as well, and you should be using it if at all possible.  That will reduce memory requirements some, but may not be enough for you to be able to cut it from 500 to 128.

    Multiple instances may be able to help you too, depending on your specific circumstances.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • ScottPletcher wrote:

    If you're using 500GB now, it could be very hard to squeeze down to 128GB.

    It's likely, however, that you are not using data compression now.  From SQL 2016 on, that is available in Std Ed as well, and you should be using it if at all possible.  That will reduce memory requirements some, but may not be enough for you to be able to cut it from 500 to 128.

    Multiple instances may be able to help you too, depending on your specific circumstances.

    Actually, no.  You need to be really careful about using Row compression and that also means Page compression, which first uses row compression.  Turning on Row compression makes a huge number of your currently fixed width columns change to variable width.  If you never change a NULL to something else, the Row compression might be right for you but the chances of that being true are slim to none and slim just left.

    --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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • juliava wrote:

    Hello,

    I was looking over the Edition specifications, and Standard is capped at 128 GB RAM is that correct?Can someone recommend me to use sql server enterprise edition or standard, My company is mid size, i am trying to reduce cost like everyone,but want make do it right.

    I'm not sure how to get around the 128 GB  per instance limit given our current resource requirements.

    Unlimitted/OS Capacity for Enterprise

    I think this may be an issue, given that we use over 500 GB of RAM right now. so will probably need to have with Enterprise. what do you think?I mean, unless we're intending to split up the primary DB into multiple instances, is that a good idea split to instanses or go with enterprise? Thank you

    You say you're currently using 500GB of RAM.  I have no doubt that you have 500GB of ram but have you actually checked to see if SQL Server is actually consuming that much RAM using something like Task Manager to see how much it's actually using?

     

    --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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Also, the Standard Edition is "Limited to lesser of 4 sockets or 24 cores" and so you need to check your hardware configuration.

    You also need to do a feature comparison.  SQL Server Enterprise Edition still has numerous features that the Standard Edition does not, especially in the database engine part of it all.

    --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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    ScottPletcher wrote:

    If you're using 500GB now, it could be very hard to squeeze down to 128GB.

    It's likely, however, that you are not using data compression now.  From SQL 2016 on, that is available in Std Ed as well, and you should be using it if at all possible.  That will reduce memory requirements some, but may not be enough for you to be able to cut it from 500 to 128.

    Multiple instances may be able to help you too, depending on your specific circumstances.

    Actually, no.  You need to be really careful about using Row compression and that also means Page compression, which first uses row compression.  Turning on Row compression makes a huge number of your currently fixed width columns change to variable width.  If you never change a NULL to something else, the Row compression might be right for you but the chances of that being true are slim to none and slim just left.

    100% disagree.  Page compression is one of the great features of SQL Server, and the vast majority of large tables benefit greatly from it.  Esp. since developers do not encode most text values into numbers as they should; for example, status has values "Closed", "Completed", "In progress", etc., rather than 9, 8, 1, etc..

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • I agree that row and page compression is a wonderful tool and I've been very successful in using it properly.  I also agree that it's wonderful for the normally largest tables in most databases (audit/log/history tables, etc), especially since the usually fall into the W.O.R.M. category.

    In the past, you've stated that people should just automatically use at least row compression on every table.  You not said that in is the recent post but you have said that the OP should "be using it if at all possible" without stating what that means.

    You also not mentioned the much longer times (2x-4x has been my experience) that index maintenance will take if you do need to rebuild compressed indexes nor the massive increase in page splits that can occur if the table suffers Null-to-Populated updates on what used to be fixed width columns... and it only takes one such column to cause the problem.  Yep... you can help prevent that, in some cases, by lowering the Fill Factor but then you've just started "wasting" memory again.  Of course that's still better than the doubling the memory requirements if you don't fix the page splits.

    Again, yes... used properly on the proper tables with the proper insert patterns can save a huge wad of both disk space and memory.  Used on the wrong tables, the resulting page splits that occur will be much worse than too little memory.

     

    --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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • juliava wrote:

    Hello,

    I was looking over the Edition specifications, and Standard is capped at 128 GB RAM is that correct?Can someone recommend me to use sql server enterprise edition or standard, My company is mid size, i am trying to reduce cost like everyone,but want make do it right.

    I'm not sure how to get around the 128 GB  per instance limit given our current resource requirements.

    Unlimitted/OS Capacity for Enterprise

    I think this may be an issue, given that we use over 500 GB of RAM right now. so will probably need to have with Enterprise. what do you think?I mean, unless we're intending to split up the primary DB into multiple instances, is that a good idea split to instanses or go with enterprise? Thank you

    Here's a really inexpensive way to find out if your system can live on standard edition... Just drop max memory to 120GB and see what happens.  😉

    --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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    juliava wrote:

    Hello,

    I was looking over the Edition specifications, and Standard is capped at 128 GB RAM is that correct?Can someone recommend me to use sql server enterprise edition or standard, My company is mid size, i am trying to reduce cost like everyone,but want make do it right.

    I'm not sure how to get around the 128 GB  per instance limit given our current resource requirements.

    Unlimitted/OS Capacity for Enterprise

    I think this may be an issue, given that we use over 500 GB of RAM right now. so will probably need to have with Enterprise. what do you think?I mean, unless we're intending to split up the primary DB into multiple instances, is that a good idea split to instanses or go with enterprise? Thank you

    Here's a really inexpensive way to find out if your system can live on standard edition... Just drop max memory to 120GB and see what happens.  😉

    That's a far riskier approach than I'd ever be willing to take in prod.

    If you really want to try this, try reducing it gradually from 500 downward.  Say to 450.  See if that goes OK.  Then to 400.  Then to 360.  Etc..  If at any point before 128 you run into significant performance issues, you'll know it won't work.  I think going straight to 128 risks a super-bad performance issue in prod.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • It's probably a bad idea all the way around anyway.  🙁  How many people do you know that actually have a "Baseline" to compare against?

    For me, it would work just fine for going straight down to 128.  I'd be monitoring live, would make the change,  and in about 10 seconds, I'd know whether or not I had to hit the "GO" button on the script that would instantly set it back to what it was.

    --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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    It's probably a bad idea all the way around anyway.  🙁  How many people do you know that actually have a "Baseline" to compare against?

    For me, it would work just fine for going straight down to 128.  I'd be monitoring live, would make the change,  and in about 10 seconds, I'd know whether or not I had to hit the "GO" button on the script that would instantly set it back to what it was.

    No it wouldn't and no you wouldn't.  SQL can't release that much RAM that quickly.  In fact, you don't know how long it will take SQL to release that much RAM from SQL back to the OS.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • I get where you're going with that but I guess I'm going to have to put that to the test in the very near future.  I did it once out of necessity about 15 years ago and it worked like I said but a whole lot has changed since then.

    --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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 12 posts - 1 through 12 (of 12 total)

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