none
Sql server -Max RAM utilization

    Frage

  • Hello,

    I have been having some problem with sql server memory utilization.

    The server is deployed on a box with 32 GB ram and 31 - 31.9 GB is being utilized.  We have several jobs deployed on the server. I disabled all the jobs , but still the usage did not go down. I get random errors :

    • Dead lock errors
    • Not able to establish connection error
    • Unable to find table error ( package runs successfully at times, no changes, hence table is always available)

    Could these errors be because of the ram usage?

    I had a few questions regarding this:

    • How can i find out which activity on SQl Server is using this much RAM.
    • Do you think indexing a huge table ( i dropped that index now) was a problem?
    • Once I had left the trace on, but did not switch it off, as my comp crashed after that. However, the server was restarted after this? Could the trace be a problem?
    • Will this config :10 % of 32 GB must be saved for OS, 25 - 29 GB for SQL server slow down any of the SQL jobs? and solve the deadlock and connection problems? How do I set max memory for sql server?

    Thanks!

    Montag, 23. April 2012 17:16

Antworten

  • Hello,

    When you speak of jobs, are you talking SSIS packages (which use executables and memory outside of SQL Server) or strictly Agents tasks that do not utilize linked servers, openquery, xp_cmdshell, etc?

    1. Deadlocks are not caused by being low or out of memory, they are caused by lock contention or incompatible locks of resources.

    2. Not being able to connect could most certainly be a symptom of out of memory or high memory pressure on the server.

    3. Unable to find table (I'm assume it errors and says the object doesn't exist?) from a package (SSIS?) could be a symptom if the package runs out of memory on the server (I'm assuming the Database Engine and SSIS are installed on the same server), however this is normally a logic error on the part of the programmer or a race condition.

    Observation: From the looks of it (maxed out memory), Max server memory is not set correctly. Is this a 32-bit or a 64-bit SQL Server install?

    Observation: If SSIS and the database engine are installed on the same server and there is memory pressure when large SSIS packages are run, look at moving the SSIS services to another server and dedicating that to just SSIS.

    Q: How can i find out which activity on SQl Server is using this much RAM

    A: While I can't speak for the ram in its entirety, you can find out which databases and then objects inside said database are using the most memory.

    SELECT
    CASE database_id WHEN 32767 THEN 'ResourceDB' ELSE DB_NAME(database_id) END AS [Database_Name],
    page_type,
    COUNT(*) * 8 / 1024 AS [MB_Used]
    FROM sys.dm_os_buffer_descriptors
    WHERE page_type IN ('DATA_PAGE', 'INDEX_PAGE', 'TEXT_MIX_PAGE')
    GROUP BY database_id, page_type
    ORDER BY [Database_Name] ASC, [MB_Used] DESC
    

    Q: Do you think indexing a huge table ( i dropped that index now) was a problem?

    The index may not have been used, but that would really affect performance in terms of query (insert/update/delete) overhead to maintain the index. If the index was being used well, by dropping it you could be putting your server under even more load from both the disk IO and from memory.

    Q: Once I had left the trace on, but did not switch it off, as my comp crashed after that. However, the server was restarted after this? Could the trace be a problem?

    If the server has been restarted since, the trace shouldn't be running. You can check to see running traces (remember the default trace is running [by default]) so don't be alarmed about that.

    /* either of these will return trace information */
    select * from sys.traces
    
    select * from sys.fn_trace_getinfo(null)

    Q: Will this config :10 % of 32 GB must be saved for OS, 25 - 29 GB for SQL server slow down any of the SQL jobs? and solve the deadlock and connection problems? How do I set max memory for sql server?

    Max Server Memory most certainly needs to be set correctly. If you have SSIS packages or are using many linked servers/oledb/com objects, clr, etc you may need to leave more memory. I would probably start around 20 GB for SQL Server and slowly bump it up assuming no performance issues happen after running your SSIS packages and agent jobs. Eventually you'll find the sweet spot for memory on that server. I would look at moving SSIS off of the server and on to its' own dedicated one. Deadlocks aren't caused by out of memory but connection problems can be a symptom.

    -Sean

    • Als Antwort markiert CK1286 Montag, 23. April 2012 18:50
    Montag, 23. April 2012 17:54
    Beantworter

Alle Antworten

  • The memory usage is completely normal and expected if you do not set the "max memory" setting.  The other issues may not be related to memory usage.   However if the server is massively page swapping it can cause strange issues.

    Please see:

    http://support.microsoft.com/kb/321363

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

    Montag, 23. April 2012 17:33
    Moderator
  • Hello,

    When you speak of jobs, are you talking SSIS packages (which use executables and memory outside of SQL Server) or strictly Agents tasks that do not utilize linked servers, openquery, xp_cmdshell, etc?

    1. Deadlocks are not caused by being low or out of memory, they are caused by lock contention or incompatible locks of resources.

    2. Not being able to connect could most certainly be a symptom of out of memory or high memory pressure on the server.

    3. Unable to find table (I'm assume it errors and says the object doesn't exist?) from a package (SSIS?) could be a symptom if the package runs out of memory on the server (I'm assuming the Database Engine and SSIS are installed on the same server), however this is normally a logic error on the part of the programmer or a race condition.

    Observation: From the looks of it (maxed out memory), Max server memory is not set correctly. Is this a 32-bit or a 64-bit SQL Server install?

    Observation: If SSIS and the database engine are installed on the same server and there is memory pressure when large SSIS packages are run, look at moving the SSIS services to another server and dedicating that to just SSIS.

    Q: How can i find out which activity on SQl Server is using this much RAM

    A: While I can't speak for the ram in its entirety, you can find out which databases and then objects inside said database are using the most memory.

    SELECT
    CASE database_id WHEN 32767 THEN 'ResourceDB' ELSE DB_NAME(database_id) END AS [Database_Name],
    page_type,
    COUNT(*) * 8 / 1024 AS [MB_Used]
    FROM sys.dm_os_buffer_descriptors
    WHERE page_type IN ('DATA_PAGE', 'INDEX_PAGE', 'TEXT_MIX_PAGE')
    GROUP BY database_id, page_type
    ORDER BY [Database_Name] ASC, [MB_Used] DESC
    

    Q: Do you think indexing a huge table ( i dropped that index now) was a problem?

    The index may not have been used, but that would really affect performance in terms of query (insert/update/delete) overhead to maintain the index. If the index was being used well, by dropping it you could be putting your server under even more load from both the disk IO and from memory.

    Q: Once I had left the trace on, but did not switch it off, as my comp crashed after that. However, the server was restarted after this? Could the trace be a problem?

    If the server has been restarted since, the trace shouldn't be running. You can check to see running traces (remember the default trace is running [by default]) so don't be alarmed about that.

    /* either of these will return trace information */
    select * from sys.traces
    
    select * from sys.fn_trace_getinfo(null)

    Q: Will this config :10 % of 32 GB must be saved for OS, 25 - 29 GB for SQL server slow down any of the SQL jobs? and solve the deadlock and connection problems? How do I set max memory for sql server?

    Max Server Memory most certainly needs to be set correctly. If you have SSIS packages or are using many linked servers/oledb/com objects, clr, etc you may need to leave more memory. I would probably start around 20 GB for SQL Server and slowly bump it up assuming no performance issues happen after running your SSIS packages and agent jobs. Eventually you'll find the sweet spot for memory on that server. I would look at moving SSIS off of the server and on to its' own dedicated one. Deadlocks aren't caused by out of memory but connection problems can be a symptom.

    -Sean

    • Als Antwort markiert CK1286 Montag, 23. April 2012 18:50
    Montag, 23. April 2012 17:54
    Beantworter