none
SQL Server 2008 Memory Configuration

    Question

  • We have SQL Server 2008 10.0.1779 installed on a Windows Server 2008 Enterprise SP1 64-bit operating system with 4 Intel Xeon 3.40 GHz CPUs and 32 GB of memory. 

    We are upgrading from SQL server 2005 to take advantage of data compression.

    Currently this system is only used as a SQL server we will be hosting a web application on the server in the next few weeks.

    In SQL Server when the Maximum Server Memory is set to anything above 8 GB a process we are running stalls.  When we look at the activity Monitor there are a lot of suspended bulk inserts.

    With the amount of memory on the server shouldn't I be able to bump up the Maximum server memory to 24 GB and see some improvement?  

    name minimum maximum config_value run_value
    access check cache bucket count 0 65536 0 0
    access check cache quota 0 2.15E+09 0 0
    Ad Hoc Distributed Queries 0 1 0 0
    affinity I/O mask -2.1E+09 2.15E+09 1 127
    affinity mask -2.1E+09 2.15E+09 254 254
    affinity64 I/O mask -2.1E+09 2.15E+09 0 0
    affinity64 mask -2.1E+09 2.15E+09 0 0
    Agent XPs 0 1 1 1
    allow updates 0 1 0 0
    awe enabled 0 1 0 1
    backup compression default 0 1 0 0
    blocked process threshold (s) 0 86400 0 0
    c2 audit mode 0 1 0 0
    clr enabled 0 1 0 0
    common criteria compliance enabled 0 1 0 0
    cost threshold for parallelism 0 32767 5 5
    cross db ownership chaining 0 1 0 0
    cursor threshold -1 2.15E+09 -1 -1
    Database Mail XPs 0 1 1 1
    default full-text language 0 2.15E+09 1033 1033
    default language 0 9999 0 0
    default trace enabled 0 1 1 1
    disallow results from triggers 0 1 0 0
    EKM provider enabled 0 1 0 0
    filestream access level 0 2 0 0
    fill factor (%) 0 100 0 0
    ft crawl bandwidth (max) 0 32767 100 100
    ft crawl bandwidth (min) 0 32767 0 0
    ft notify bandwidth (max) 0 32767 100 100
    ft notify bandwidth (min) 0 32767 0 0
    index create memory (KB) 704 2.15E+09 0 0
    in-doubt xact resolution 0 2 0 0
    lightweight pooling 0 1 0 0
    locks 5000 2.15E+09 0 0
    max degree of parallelism 0 64 2 2
    max full-text crawl range 0 256 4 4
    max server memory (MB) 16 2.15E+09 8192 8192
    max text repl size (B) -1 2.15E+09 65536 65536
    max worker threads 128 32767 128 128
    media retention 0 365 0 0
    min memory per query (KB) 512 2.15E+09 1024 1024
    min server memory (MB) 0 2.15E+09 1024 1024
    nested triggers 0 1 1 1
    network packet size (B) 512 32767 4096 4096
    Ole Automation Procedures 0 1 0 0
    open objects 0 2.15E+09 0 0
    optimize for ad hoc workloads 0 1 0 0
    PH timeout (s) 1 3600 60 60
    precompute rank 0 1 0 0
    priority boost 0 1 1 1
    query governor cost limit 0 2.15E+09 0 0
    query wait (s) -1 2.15E+09 -1 -1
    recovery interval (min) 0 32767 0 0
    remote access 0 1 1 1
    remote admin connections 0 1 0 0
    remote login timeout (s) 0 2.15E+09 20 20
    remote proc trans 0 1 0 0
    remote query timeout (s) 0 2.15E+09 600 600
    Replication XPs 0 1 0 0
    scan for startup procs 0 1 0 0
    server trigger recursion 0 1 1 1
    set working set size 0 1 0 0
    show advanced options 0 1 1 1
    SMO and DMO XPs 0 1 1 1
    SQL Mail XPs 0 1 0 0
    transform noise words 0 1 0 0
    two digit year cutoff 1753 9999 2049 2049
    user connections 0 32767 0 0
    user options 0 32767 0 0
    xp_cmdshell 0 1 0 0




       

    Thursday, August 27, 2009 3:26 PM

All replies

  • Hi,

    With 64 bits architecture and 32GB of amount memory, you can easily work with more memory than 8GB to improve general performance with your SQL Server.

    Configure correctly the "max server memory" parameter with the correct amount of memory.

    ++
    MCDBA | MCITP SQL Server 2005 | MCTS SQL Server 2008 | LPI Linux 1
    Sunday, September 13, 2009 6:13 PM
  • Well that's an odd symptom.  Have you compared the query plans, both before and after adjusting the max server memory?  Perhaps one plan is employing paralellism, while the other is not.  If this is the case, you can use a MAXDOP hint in your insert command - should take care of the problem.

    If this isn't the problem, do you see anything in the "wait type" field for those spids?  How about the "blocked by" field?
    Aaron Alton | thehobt.blogspot.com
    MCITP: Database Administrator
    Monday, September 14, 2009 1:51 AM
    Moderator
  • Some comments below:

    If you will be hosting an web application on the same box, then you have to be careful while setting the max server memory setting. Make sure you leave enough memory to OS, web application and then set the remaining available memory to SQL Server. Otherwise SQL Server will eatup all the memory, and the OS and web application may be competing for resources.

    I am not sure what you mean by stalls. Are you referring to blocking or just seeing suspended status for session_ids? What you need to look for, are the wait statistics on the server to findout the bottlenecks. To get accurate mesure of the top wait statistics, you should run the server for a while. Make sure the server has enough uptime. The below query will help identify the top wait statistics. This query is from an excellent blog psot by Glenn Berry. 

    Here is an excellent whitepaper on troubleshooting performance problems.
     
    Troubleshooting Performance Problems in SQL Server 2008

    Yes, SQL Server will enjoy and perform well when you can give more memory. But you should always look ways to improve the SQL when you can.
    -- Isolate top waits for server instance since last restart or statistics clear
    WITH Waits AS
    (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
        100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
     FROM sys.dm_os_wait_stats
     WHERE wait_type NOT IN( 'SLEEP_TASK', 'BROKER_TASK_STOP', 
      'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
      'LAZYWRITER_SLEEP')) -- filter out additional irrelevant waits
    SELECT W1.wait_type, 
      CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
      CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
      CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
    FROM Waits AS W1
    INNER JOIN Waits AS W2
    ON W2.rn <= W1.rn
    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
    HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold



    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Monday, September 14, 2009 2:03 AM
    Moderator

  • Currently this system is only used as a SQL server we will be hosting a web application on the server in the next few weeks.
       


    Just a note that this is not a recommended configuration.  Your web application should be completely separate from SQL Server for security and performance considerations, especially if you are currently having a performance problem related to SQL Server.

    As already recommended, check your wait_stats and post the results of the query provided by Sankar back here.  The Wait Statistics are generally one of the best pointers to what is causing a performance issue like you describe.
    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.twitter.com/SQLSarg
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Monday, September 14, 2009 12:37 PM
    Moderator
  • Hi Sankar,

    I have an SQL Server 2008 Std Edition installed on a server with Windows Server 2003. I had upgraded the RAM for the server to 12 GB and want to utilize the maximum performance of SQL Server. As per the query you have mentioned above, the results are as given below:

    wait_type                                               wait_time_s        pct      running_pct
    REQUEST_FOR_DEADLOCK_SEARCH         58188.33          15.07    15.07
    LOGMGR_QUEUE                                      58178.89          15.07    30.14
    XE_TIMER_EVENT                                     58172.98          15.07    45.20
    CHECKPOINT_QUEUE                                58152.77          15.06    60.27
    FT_IFTS_SCHEDULER_IDLE_WAIT              57660.57          14.93    75.20
    XE_DISPATCHER_WAIT                             54362.86          14.08    89.28
    BROKER_TO_FLUSH                                  29094.67           7.54     96.81

    The database server is accessed by our LAN based business application and through our web server by remote users.

    Kindly suggest what should be the ideal memory configurations for min & max memory for my server. Also, if any other options needs to be configured.

    Tuesday, February 15, 2011 5:47 AM
  • Hi Jonathan,

    I have an SQL Server 2008 Std Edition installed on a server with Windows Server 2003. I had upgraded the RAM for the server to 12 GB and want to utilize the maximum performance of SQL Server. As per the query you have mentioned above, the results are as given below:

    wait_type                                               wait_time_s        pct      running_pct
    REQUEST_FOR_DEADLOCK_SEARCH         58188.33          15.07    15.07
    LOGMGR_QUEUE                                      58178.89          15.07    30.14
    XE_TIMER_EVENT                                     58172.98          15.07    45.20
    CHECKPOINT_QUEUE                                58152.77          15.06    60.27
    FT_IFTS_SCHEDULER_IDLE_WAIT              57660.57          14.93    75.20
    XE_DISPATCHER_WAIT                             54362.86          14.08    89.28
    BROKER_TO_FLUSH                                  29094.67           7.54     96.81

    The database server is accessed by our LAN based business application and through our web server by remote users.

    Kindly suggest what should be the ideal memory configurations for min & max memory for my server. Also, if any other options needs to be configured.

    Tuesday, February 15, 2011 6:03 AM