SQL Server Performance

memory recommendations for 2 tera db

Discussion in 'SQL Server 2008 General DBA Questions' started by cbabino71, Apr 7, 2011.

  1. cbabino71 New Member

    I would like to reach out to other companies with a 2 terabyte sql server database. How much ram do you recommend having on the server?
    The database will be used heavily for querying and reporting.The server currently has 24 gig but I find it hard to believe that will be enough. The largest table is 120 gig growing aggresivly every day. Is my guess of having enough ram for the largest table to fit in a good guess?
    As we all know, the more data in the cache the better the queries will run, having to goto the hard disk for reads will be much slower.
    sql server 2008 enterprise 64bit.
    windows 2008 r2 64bit
    Thanks
    CB
    cbabino71, Apr 7, 2011
    #2

  2. satya Moderator

    SQL Server dynamically allocates AWE mapped memory when running with any of the Windows Server 2003 operating-system editions. In other words, the buffer pool can dynamically manage AWE mapped memory to balance SQL Server memory use with the overall system requirements. In your case this is not required as your platform is 64bit, it is not required AWE because access to memory is not limited to 4 GB.
    In terms of memory for Enterprise edition its always recommended: 4 GB or more as it can also support upto 2 TB (SQL Server Enterprise Edition supports a maximum of 2 TB of RAM or operating system maximum, whichever is lower).
    However do you see any memory related performance issues on this server?
    Run the Performance dashboard reports on Memory usage on the instance t get more information.
    satya, Apr 8, 2011
    #3

  3. cbabino71 New Member

    Thanks Satya for your reply but I doubt 4 gig is enough for a 2 terabyte db.
    cbabino71, Apr 8, 2011
    #4

  4. satya Moderator

    Don't just take this is a suggestion, but rather monitor the server usage to manage the resource efficiently.
    satya, May 3, 2011
    #5

  5. Jahanzaib Member

    Check this query
    SELECT * FROM SYS.DM_OS_PERFORMANCE_COUNTERS
    check Page life expectancy is higher then increase RAM
    Check SOS_SCHEDULE waitresourcetype with this query
    Select st.text,sp.* from sys.sysprocesses sp cross apply sys.dm_exec_sql_text(sp.sql_handle) st order by cpu desc
    if this waitresource exist then increase the RAM
    Check Performance monitor of OS if CPU continuous more than 50 to 60 then increase the RAM
    Jahanzaib, May 9, 2011
    #6

  6. Murat Yilmaz New Member

    Check Page Life Expectancy and Buffer Cache hit ratio as suggested. The more RAM, you will have, the longer your data will be in the buffer pool. And also measure your Disk ms/Reads, ms/Writes, Disk queue length. In most systems, slow disks make the large memory space no difference and actually the real source of performance bottlenecks.
    Murat Yilmaz, Jul 28, 2011
    #6

Share This Page