sqlserver.exe is consuming high memory

Dinesh Negi 66 Reputation points
2021-07-09T14:49:24.437+00:00

Team,

Sqlserver.exe is consuming high memory. Total memory is assigned 128GB out of that 120GB is consumed by sqlserver.exe. I know its by design sql will grab all the memory from the OS. Server is hosted on shared Data center.

my question: How we confirm how much RAM is needed ? so that it will not impact the server performance. Do we have any tool to analysis the how much memory /cpu needed? on what basis I can confirm to customer SQL need more memory?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,714 questions
0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101K Reputation points MVP
    2021-07-09T15:56:18.557+00:00

    When it comes to the impact on the rest of the machine, you need to consider if there are other processes running, including things like Integration Services. If the machine has 128 GB in total, maybe you should set max server memory to 100 GB.

    The standard answer to "how much memory does SQL Server need?" is "more". :-) More seriously, there is a quite a bit of "it depends" here. SQL Server can easily consume 120 GB of RAM if you are running reindex operations on large tables. In fact, if you have 1 TB database, and you reindex every table, guess what: SQL Server will fill up 1 TB of RAM if that would be available. If you only have 120 GB or RAM? Yes, some data will fall out of the cache. But that is not really a problem. On the other hand, it is a problem if normal day use does not really fit into 120 GB of RAM so that data all through the day is being read from disk and not from cache.

    Would that be the case, most likely you will have users complaining about poor performance.

    0 comments
  2. Tom Phillips 17,716 Reputation points
    2021-07-09T20:03:48.203+00:00

    SQL Server never gives up RAM unless the OS signals it is low on memory. This is completely normal and expected behavior. It does not indicate a problem.

    https://learn.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide?view=sql-server-ver15#dynamic-memory-management

    How much ram is "needed" is really up to your usage and installation. If your performance is ok, then you have enough. Adding RAM (to a point) will generally increase the buffer size and decrease disk reads. You will always have a bottleneck, RAM, Disk or CPU. It is up to you to determine if the performance is good enough for your situation.

    0 comments
  3. Dinesh Negi 66 Reputation points
    2021-07-10T06:36:14.227+00:00

    at OS point of view everything looks good to me.

    What shall I check? and proof their is no issue from OS side. Any specific performance counter I can set? and show them.

  4. Dinesh Negi 66 Reputation points
    2021-07-10T14:08:46.71+00:00

    Yes, server has poor performance.

  5. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-07-12T06:38:22.54+00:00

    Hi dineshnegi-9437,
    In addition, to monitor memory, we can use the following counters:
    Memory: Pages/sec
    This counter indicates the number of pages that either were retrieved from disk due to hard page faults or written to disk to free space in the working set due to page faults. A high rate for the Pages/sec counter could indicate excessive paging.The average Pages/sec value should be below 50.
    Memory: Page Faults/sec
    This counter indicates the rate of Page Faults for all processes including system processes. A low but non-zero rate of paging to disk (and hence page faults) is typical, even if the computer has plenty of available memory. The normal values are 10 to 15, but even 1,000 page faults per second can be normal in specific environments.
    Memory: Available bytes
    This counter indicates how many bytes of memory are currently available for use by processes. Low values for the Available Bytes counter can indicate an overall shortage of operating system memory. If the memory Available bytes value is constantly lower than 100 MB, it indicates that there is insufficient memory on the server, or that there is an application that is not releasing memory.
    SQL Server: Memory Manager: Total Server Memory (KB)
    This counter indicates the amount of the operating system's memory the SQL Server memory manager currently has committed to SQL Server. If the Total Server Memory (KB) value is consistently high, it means that SQL Server is constantly using a lot of memory and that the server is under memory pressure.
    SQL Server: Memory Manager: Target Server Memory (KB)
    This counter indicates an ideal amount of memory SQL Server could consume, based on recent workload. When the Total Server Memory and Target Server Memory values are close, there’s no memory pressure on the server.
    SQL Server: Buffer Manager: Buffer Cache Hit Ratio
    This counter is specific to SQL Server. A ratio of 90 or higher is desirable. A value greater than 90 indicates that more than 90 percent of all requests for data were satisfied from the data cache in memory without having to read from disk.
    SQL Server: Buffer Manager: Page life expectancy
    This counter measures amount of time in seconds that the oldest page stays in the buffer pool. For systems that use a NUMA architecture, this is the average across the all NUMA nodes. A higher, growing value is best. A sudden dip indicates a significant churn of data in and out of the buffer pool, indicating the workload could not fully benefit from data already in memory.

    Please refer to SQL Server memory performance metrics and Monitor memory usage which might help.

    Best Regards,
    Amelia

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments