Location>code7788 >text

SQL Server high memory usage analysis and solutions (super detailed)

Popularity：199 ℃/2025-01-17 11:23:19

SQL Server high memory usage analysis and solutions (super detailed)

1. Problem

1.1. SQL Server memory usage is high and memory is not released.

1.2. SQL Server memory usage strategy

SQL Server’s usage strategy for server memory isAs many as there are(Approximately until the remaining memory is about 4M) Only used when the server memory is insufficient.

Only then will a little occupied memory be released, so many times, we will find that the system memory running SQL Server often remains high. These memories are generally

It is used as a cache when SQL Server is running.

Data cache：

For example: if you run a select statement, SQL Server will store the relevant data pages (The data operated by SQL Server is based on pages.，exist

The size of a page in SQL Server is always 8kb. There are different types of pages: data pages, index pages, system pages, etc.) is loaded into memory.

Cache, so that when the data of this page is requested again, it can be returned directly from the memory without reading the disk, which greatly improves the speed.

Execute command cache：

For example, when executing stored procedures and custom functions, SQL Server needs to be binary compiled before running. The compiled results will also be cached, so there is no need to compile again when called again.

2. Solution

When we know the memory usage of SQL Server, there are two solutions:

  • clear cache

-- Check the memory usage. This will return multiple result set data, which can help us troubleshoot memory problems.
 DBCC MemoryStatus

Explanation of some important indicators in the result set (please note,DBCC MEMORYSTATUSThe output format and content may vary depending on the SQL Server version):If you are not interested in indicators and want to solve the problem in one go, you can skip the following indicators and go directly to the cache clearing command below.

1、Total Server Memory (KB)

  • meaning: The actual amount of memory used by SQL Server in kilobytes.
  • importance: This is the actual physical memory size currently occupied by SQL Server, which directly reflects the impact of SQL Server on system resources. If this value is close to or exceeds the configured maximum server memory limit, it may indicate memory pressure.

2、Target Server Memory (KB)

  • meaning: The amount of memory (in kilobytes) that SQL Server would ideally like to reserve, a calculated target value based on current load and configuration parameters.
  • importance:andTotal Server MemoryComparison can help identify whether there is insufficient memory. ifTotal Server Memorymuch lower thanTarget Server Memory, it may be due to other processes taking up too much memory.

3、Memory Grants Outstanding

  • meaning: The number of memory requests waiting to be allocated to the query.
  • importance: When this number is large, it means that there are a large number of concurrent queries competing for limited memory resources, which may cause increased query latency. Long-term high levels may require adjusting the maximum memory setting or optimizing the query.

4、Page Life Expectancy (PLE)

  • meaning: The average time (in seconds) that a page stays in the buffer pool without being removed. Although PLE is notDBCC MEMORYSTATUSFields that are directly output, but can be passedsys.dm_os_performance_countersView acquisition.
  • importance:PLE is an important memory health indicator. A high PLE value usually indicates sufficient memory, while a low PLE value (e.g., less than 300 seconds) may indicate memory pressure because pages are frequently removed from cache.

5、Available Physical Memory (KB)

  • meaning: The amount of physical memory currently available in kilobytes, that is, memory that is not occupied or reserved.
  • importance: Understand the overall memory situation of the system and help determine whether there is enough free memory for SQL Server and other applications. Low available memory may cause the operating system to start swapping memory to disk, thus degrading performance.

6、Available Paging File (KB)

  • meaning: The amount of page file space currently available in kilobytes.
  • importance: Although SQL Server tries to avoid using the page file, it will still rely on the page file if there is insufficient physical memory. Therefore, it is also important to ensure that there is enough page file space, but reliance on it should be minimized.

7、Percent of Committed Memory in WS

  • meaning: The percentage of committed memory that is within the working set.
  • importance: This ratio helps understand how much of the memory allocated to SQL Server is being actively used. A high ratio indicates that most of the memory is in active use, while a low ratio may suggest underutilized memory or excessive memory allocations.

8、System Physical Memory Low

  • meaning: A status flag indicating that the system's physical memory is at a "low" level.
  • importance: When this flag is true, it means that the system's physical memory is close to being exhausted, and action may need to be taken to relieve memory pressure, such as increasing physical memory, optimizing queries, or adjusting the maximum memory setting of SQL Server.

9、Page Faults

  • meaning: The number of page faults, which refers to the number of times the operating system loads a page triggered by an attempt to access a page that is not in physical memory.
  • importance: Frequent page faults (especially hard page faults) may indicate insufficient memory, because each page fault results in a disk I/O operation, which will significantly affect performance.

10、Memory Grants Pending

  • meaning: The queue length of memory requests that have been submitted but have not yet been processed.
  • importance: If this value is non-zero, it means there are memory requests waiting to be processed, which may delay query execution. Long-standing non-zero values ​​may point to memory contention issues.

11、Lock Pages in Memory Usage (KB)

  • meaning: If the Page Lock option is enabled, this value represents the number of pages (in kilobytes) used to lock into physical memory.
  • importance: Enabling "page lock" can prevent SQL Server's working set from being swapped out to disk and improve performance. However, this also reduces the physical memory available to other processes by the operating system.

12、Large Pages Allocated (KB)

  • meaning: If hugepage support is enabled, displays the amount of allocated hugepage memory in kilobytes.
  • importance: Large pages can reduce TLB (Translation Lookaside Buffer) loss and improve performance. For large data warehouse or OLAP environments, enabling large page support may result in performance improvements.

These key metrics provide a comprehensive view of SQL Server memory usage and are extremely valuable for diagnosing performance issues. By regularly monitoring these indicators, potential problems can be discovered in time and appropriate measures can be taken to optimize SQL Server performance. Additionally, combined with Dynamic Management Views (DMVs) such assys.dm_os_memory_clerks、sys.dm_exec_query_memory_grantsetc., as well as performance counters, to gain more detailed insights to better manage and tune your SQL Server instance.

Cache clearing command

-- Temporarily clear cache command
 DBCC FREEPROCCACHE --Clear cache related to stored procedures
 DBCC REESESSIONCACHE --Clear session cache
 DBCC FREESYSTEMCACHE('All') --Clear system cache
 DBCC DROPCLEANBUFFERS --Clear all caches

Although the above command will clear the existing cache to make room for the new cache, Sql server will not release the occupied memory. Sql

Server does not provide any command to allow us to release unused memory. Therefore, we can only dynamically adjust the physical memory settings available to Sql Server.

settings to force it to release memory.

If you want Sql Server to actively release the occupied and free memory space, you can set the upper limit of the memory occupied by Sql Server, which will allow Sql Server to

Within the upper limit of memory, dirty data is actively cleared and replaced with hot data. So you have to do the following

3. Set the maximum memory usage value

Setting method:

Connect from the studio that comes with it, click [right-click] on the database server name, select [Properties], then find the [Memory] option, and click the right button on the left of [Use AWE to allocate memory] on the right (sqlServer64 should not be checked) Check it. Fill in the appropriate size in the maximum server memory (MB) (the specific size must not exceed the physical memory of the computer, it is recommended to control it at 60%-75%), and restart after the setting is successful.

image

4. Others

Result set picture: Execute DBCC MemoryStatus

If there is anything wrong with the final article, you are welcome to tell me in the comment area! ! !
If you think it would be helpful to youLike and recommendorfocus onGive it a try! ! !
img

<Previous Next>