how much memory that we need for SQL

  • Hi All,

    Our server has a Datawarehouse database is like 600 GB ( 500 GB for data file only ) and memory is ONLY 8 GB ( 5 GB for SQL ) . There are only 2 Databases and the other database is very small (58 MB)

    SQL edition is SQL 2014

    I feel The memory is too low.

    But how much memory that i should recommend ?

    Your feedback is appreciated

  • This was removed by the editor as SPAM

  • 5GB is less than the VM running on my laptop has for managing SQL Server.

    You have to think about how SQL Server works. Let's just talk about reads for a moment. A read requires access to a page on the disk. Even a one row read is going to go and get an 8k page. That 8k page has to, must, absolutely has to, go into memory before it can be sent to your results on your query. Let's imagine two people running an aggregate against your 500gb data warehouse. Each one is accessing, let's be nice and say, 3gb worth of pages to get the aggregation across a bunch of data. That's 6gb. Even if all 5gb was available for these two queries, and it isn't, you have a whole bunch of other memory caches & requirements that must be met, you're still 1gb shy of being able to move that data through memory for the query. So, it has to page all that out to disk. Yes, it writes out to disk when it doesn't have enough room in memory to gather up a result set. This causes slow downs, additional disk requirements, all sorts of crazy stuff. And we're only talking about two benign queries. What about lots more queries? What about bad queries that are scanning huge amounts of data? What about the need for memory for hash match joins or hash match aggregates? What about the plan cache? I can keep going.

    You don't have enough memory on the server.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • WhiteLotus wrote:

    Hi All,

    Our server has a Datawarehouse database is like 600 GB ( 500 GB for data file only ) and memory is ONLY 8 GB ( 5 GB for SQL ) . There are only 2 Databases and the other database is very small (58 MB)

    SQL edition is SQL 2014

    I feel The memory is too low.

    But how much memory that i should recommend ?

    Your feedback is appreciated

    Soooooo... let's ask the most important question of all.  Are you having performance issues with the workload you have against it?  And, how many CPUs (virtual or otherwise) does the database have access to?

    Considering that the database is more than half a Terabyte and not knowing any of the above, I'd recommend at least 64GB but I also don't recommend you go cheap.  I suspect you only have the Standard Edition (you really need to get better at providing enough facts in your questions) so go for 128GB.  Increasing memory does NOT increase licensing cost, either!

    --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 4 posts - 1 through 4 (of 4 total)

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