none
Need suggestion on why MS SQL 2016 (sqlservr.exe) memory is not released and keeps on increasing RRS feed

  • Question

  • Hi,

    We are using SQL Server Enterprise Evaluation 2016 (13.0.1601.5) with min memory 0 MB and Max Memory as default i.e. 2,147,483,647 MB. The RAM size is 32 GB. After restart (Failover Active to Passive and vice versa) of SQL Cluster Service, sqlservr.exe memory increases slowly i.e.  on first day 5 GB then next it reaches to 10 GB then next day 15 GB. Since memory keeps on increasing after 3-4 days  few SQL quries also takes very long time to execute. But if SQL service are restarted then those sql quries are executed within few seconds. After executing DBCC DROPCLEANBUFFERS and Freeproccache the memory is not released by SQL server. 

    Also suppose If I set max memory to 15 GB then whether my daily running transactions (insert, update, select) will get fail or not after 3-4 days from restart of SQL services .

    Requesting you to please suggest on this. 

    Thanks in advance

    Regards,

    Nikhil P Desai


    Friday, October 11, 2019 9:41 AM

All replies

  • Hi,

    We are using SQL Server Enterprise Evaluation 2016 (13.0.1601.5) with min memory 0 MB and Max Memory as default i.e. 2,147,483,647 MB. The RAM size is 32 GB. After restart (Failover Active to Passive and vice versa) of SQL Cluster Service, sqlservr.exe memory increases slowly i.e.  on first day 5 GB then next it reaches to 10 GB then next day 15 GB. Since memory keeps on increasing after 3-4 days  few SQL quries also takes very long time to execute. But if SQL service are restarted then those sql quries are executed within few seconds. After executing DBCC DROPCLEANBUFFERS and Freeproccache the memory is not released by SQL server. 

    Also suppose If I set max memory to 15 GB then whether my daily running transactions (insert, update, select) will get fail or not after 3-4 days from restart of SQL services .

    Requesting you to please suggest on this. 

    Thanks in advance

    Regards,

    Nikhil P Desai


    In general SQL will try to use as much as memory and will not release unless OS request it.

    Are you using Lock pages in memory? I suggest to look the execution plan and go on it.

    It may help 

    https://www.sqlserverblogforum.com/dba/performance-tuning-series-main-part/

    https://www.sqlserverblogforum.com/category/performance/




    Muthukkumaran Kaliyamoorthy

    Helping SQL DBAs and Developers >>> SqlserverBlogForum

    Friday, October 11, 2019 10:23 AM
  • Don't worry you can setup max server memory to 15GB if you want to keep the rest for OS and some other apps, your queries for insert/update/select will work normally if there is enough space on disk for temp/transaction logs and data files.
    Friday, October 11, 2019 10:56 AM
  • It's a good practice to configure Min and Max memory for SQL Server, so that Windows will not starve for memory.

    It depends - what other apps running on your system - but I say at least 10% and usually not more than 20%.  You could leave about 4 Gigs of RAM for Windows, but that really depends on other apps running on the server, what components of SQL Server you are using etc. No server restart required to set Min/Max memory.

    And you need to identify the slow running queries and analyze the details (query i use found online, credit to the DBA who wrote it)

    SELECT  creation_time 
            ,last_execution_time
            ,total_physical_reads
            ,total_logical_reads 
            ,total_logical_writes
            , execution_count
            , total_worker_time
            , total_elapsed_time
            , total_elapsed_time / execution_count avg_elapsed_time
            ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
             ((CASE statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
              ELSE qs.statement_end_offset END
                - qs.statement_start_offset)/2) + 1) AS statement_text
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    ORDER BY total_elapsed_time / execution_count DESC;


    Friday, October 11, 2019 11:17 AM
  • sqlservr.exe memory increases slowly i.e.  on first day 5 GB then next it reaches to 10 GB then next day 15 GB.

    Yes, this is what you can expect.

    Since memory keeps on increasing after 3-4 days  few SQL quries also takes very long time to execute.

    Where did you get that "since" from? The first execution of a query with the same data with a cold cache will take longer time, since data is read from disk, but the more and more data that is in cache, the more of that factor will disappear.

    But if SQL service are restarted then those sql quries are executed within few seconds.

    Sounds like a parameter sniffing problem where you get different plans depending on the first execution of the query.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, October 11, 2019 9:06 PM