You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Copy file name to clipboardExpand all lines: docs/relational-databases/performance-monitor/monitor-memory-usage.md
+37-86Lines changed: 37 additions & 86 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -2,7 +2,7 @@
2
2
title: "Monitor Memory Usage | Microsoft Docs"
3
3
description: "Monitor a SQL Server instance to confirm that memory usage is within typical ranges. Use the Memory: Available Bytes and Memory: Pages/sec counters."
Monitor an instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] periodically to confirm that memory usage is within typical ranges.
31
-
32
-
## Configuring [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] max memory
33
-
34
-
A [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance may over time consume all the Windows operating system memory allowed by [the **max server memory** option](../../database-engine/configure-windows/server-memory-server-configuration-options.md).
35
-
36
-
In SQL Server on Linux, [set the memory limit](../../linux/sql-server-linux-performance-best-practices.md#advanced-configuration) with the mssql-conf tool and the [memory.memorylimitmb setting](../../linux/sql-server-linux-configure-mssql-conf.md#memorylimit).
37
-
38
-
## Monitor operating system memory
39
-
To monitor for a low-memory condition, use the following Windows server counters. Many operating system memory counters can be queried via the dynamic management views [sys.dm_os_process_memory](../system-dynamic-management-views/sys-dm-os-process-memory-transact-sql.md) and [sys.dm_os_sys_memory](../system-dynamic-management-views/sys-dm-os-sys-memory-transact-sql.md).
40
-
30
+
Monitor an instance of [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] periodically to confirm that memory usage is within typical ranges.
31
+
32
+
To monitor for a low-memory condition, use the following object counters:
33
+
41
34
-**Memory: Available Bytes**
42
-
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. This value can be queried via T-SQL using [sys.dm_os_sys_memory](../system-dynamic-management-views/sys-dm-os-sys-memory-transact-sql.md).available_physical_memory_kb.
43
-
35
+
44
36
-**Memory: Pages/sec**
45
-
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.
46
-
47
-
-**Memory: Page Faults/sec**
48
-
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 Microsoft Windows Virtual Memory Manager (VMM) takes pages from [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] and other processes as it trims the working-set sizes of those processes. This VMM activity tends to cause page faults.
49
-
50
-
-**Process: Page Faults/sec**
51
-
This counter indicates the rate of Page Faults for a given user process. Monitor **Process: Page Faults/sec** to determine if disk activity is caused by paging by [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. To determine whether [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] or another process is the cause of excessive paging, monitor the **Process: Page Faults/sec** counter for the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] process instance.
52
-
53
-
For more information about resolving excessive paging, see the operating system documentation.
54
-
55
-
## Isolating memory used by [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]
56
-
57
-
To monitor [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] memory usage, use the following [SQL Server object counters](use-sql-server-objects.md). Many SQL Server object counters can be queried via the dynamic management views [sys.dm_os_performance_counters](../system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql.md) or [sys.dm_os_process_memory](../system-dynamic-management-views/sys-dm-os-process-memory-transact-sql.md).
58
-
59
-
By default, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] manages its memory requirements dynamically, on the basis of available system resources. If [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] needs more memory, it queries the operating system to determine whether free physical memory is available and uses the available memory. If there is low free memory for the OS, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] will release memory back to the operating system until the low memory condition is alleviated, or until [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] reaches the **minservermemory** limit. However, you can override the option to dynamically use memory by using the **minservermemory**, and **maxservermemory** server configuration options. For more information, see [Server Memory Options](../../database-engine/configure-windows/server-memory-server-configuration-options.md).
37
+
38
+
The **Available Bytes** counter indicates how many bytes of memory are currently available for use by processes. The **Pages/sec** 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.
39
+
40
+
Low values for the **Available Bytes** counter can indicate that there is an overall shortage of memory on the computer or that an application is not releasing memory. A high rate for the **Pages/sec** counter could indicate excessive paging. Monitor the **Memory: Page Faults/sec** counter to make sure that the disk activity is not caused by paging.
41
+
42
+
A low rate of paging (and hence page faults) is typical, even if the computer has plenty of available memory. The Microsoft Windows Virtual Memory Manager (VMM) takes pages from [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] and other processes as it trims the working-set sizes of those processes. This VMM activity tends to cause page faults. To determine whether [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] or another process is the cause of excessive paging, monitor the **Process: Page Faults/sec** counter for the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] process instance.
43
+
44
+
For more information about resolving excessive paging, see the Windows operating system documentation.
45
+
46
+
## Isolating Memory Used by SQL Server
47
+
By default, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] changes its memory requirements dynamically, on the basis of available system resources. If [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] needs more memory, it queries the operating system to determine whether free physical memory is available and uses the available memory. If there is low free memory for the OS, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] will release memory back to the operating system until the low memory condition is alleviated, or until [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] reaches the minservermemory limit. However, you can override the option to dynamically use memory by using the **minservermemory**, and **maxservermemory** server configuration options. For more information, see [Server Memory Options](../../database-engine/configure-windows/server-memory-server-configuration-options.md).
60
48
61
49
To monitor the amount of memory that [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] uses, examine the following performance counters:
62
50
63
-
-**SQL Server: Memory Manager: Total Server Memory (KB)**
64
-
This counter indicates the amount of the operating system's memory the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] memory manager currently has committed to [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. This number is expected to grow as required by actual activity, and will grow following SQL Server startup. Query this counter using the [sys.dm_os_sys_info](../system-dynamic-management-views/sys-dm-os-sys-info-transact-sql.md) dynamic management view, observing the **committed_kb** column.
65
-
66
-
-**SQL Server: Memory Manager: Target Server Memory (KB)**
67
-
This counter indicates an ideal amount of memory [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] could consume, based on recent workload. Compare to **Total Server Memory** after a period of typical operation to determine whether [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] has a desired amount of memory allocated. After typical operation, **Total Server Memory** and **Target Server Memory** should be similar. If **Total Server Memory** is significantly lower than **Target Server Memory**, the [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] instance may be experiencing memory pressure. During a period after [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] is started, **Total Server Memory** is expected to be lower than **Target Server Memory**, as **Total Server Memory** grows. Query this counter using the [sys.dm_os_sys_info](../system-dynamic-management-views/sys-dm-os-sys-info-transact-sql.md) dynamic management view, observing the **committed_target_kb** column.
68
-
69
51
-**Process: Working Set**
70
-
This counter indicates the amount of memory that is used by a process currently, according to the operating system. Query this counter using the [sys.dm_os_process_memory](../system-dynamic-management-views/sys-dm-os-process-memory-transact-sql.md) dynamic management view, observing the **physical_memory_in_use_kb** column.
71
-
72
-
-**SQL Server: Buffer Manager: Database Pages**
73
-
This counter indicates the number of pages in the buffer pool with database content. Does not include other nonbuffer pool memory within the SQL Server process. Query this counter using the [sys.dm_os_performance_counters](../system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql.md) dynamic management view.
74
-
52
+
75
53
-**SQL Server: Buffer Manager: Buffer Cache Hit Ratio**
76
-
This counter is specific to [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)]. 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. Find more information on the SQL Server Buffer Manager, see the [SQL Server Buffer Manager Object](sql-server-buffer-manager-object.md). Query this counter using the [sys.dm_os_performance_counters](../system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql.md) dynamic management view.
77
-
78
-
-**SQL Server: Buffer Manager: Page life expectancy**
79
-
This counter measures amount of time in seconds that pages stay in cache, on average across the buffer pool. Each NUMA node has its own node of the buffer pool. 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. On servers with more than one NUMA node, view each buffer pool node's page life expectancy using **SQL Server: Buffer Node: Page life expectancy**. Query this counter using the [sys.dm_os_performance_counters](../system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql.md) dynamic management view.
80
-
81
54
82
-
## Examples
83
-
84
-
### Determining current memory allocation
85
-
The following queries return information about currently allocated memory.
55
+
-**SQL Server: Buffer Manager: Database Pages**
56
+
57
+
-**SQL Server: Memory Manager: Total Server Memory (KB)**
58
+
59
+
The **WorkingSet** counter shows the amount of memory that is used by a process. If this number is consistently below the amount of memory that is set by the **min server memory** and **max server memory** server options, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] is configured to use too much memory.
60
+
61
+
The **Buffer Cache Hit Ratio** counter is specific to an application. However, a rate of 90 percent or higher is desirable. Add more memory until the value is consistently greater than 90 percent. A value greater than 90 percent indicates that more than 90 percent of all requests for data were satisfied from the data cache.
62
+
63
+
If the **TotalServerMemory (KB)** counter is consistently high compared to the amount of physical memory in the computer, it may indicate that more memory is required.
64
+
65
+
## Determining Current Memory Allocation
66
+
The following query returns information about currently allocated memory.
86
67
87
68
```
88
-
SELECT
89
-
(total_physical_memory_kb/1024) AS Total_OS_Memory_MB,
90
-
(available_physical_memory_kb/1024) AS Available_OS_Memory_MB
91
-
FROM sys.dm_os_sys_memory;
92
-
93
69
SELECT
94
-
(physical_memory_in_use_kb/1024) AS Memory_used_by_Sqlserver_MB,
95
-
(locked_page_allocations_kb/1024) AS Locked_pages_used_by_Sqlserver_MB,
96
-
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
70
+
(physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,
71
+
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,
72
+
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
97
73
process_physical_memory_low,
98
74
process_virtual_memory_low
99
75
FROM sys.dm_os_process_memory;
100
76
```
101
-
102
-
### Determining Current SQL Server Memory Utilization
103
-
The following query returns information about current SQL Server memory utilization.
104
-
```
105
-
SELECT
106
-
sqlserver_start_time,
107
-
(committed_kb/1024) AS Total_Server_Memory_MB,
108
-
(committed_target_kb/1024) AS Target_Server_Memory_MB
109
-
FROM sys.dm_os_sys_info;
110
-
```
111
-
112
-
### Determining Page Life Expectancy
113
-
The following query uses **sys.dm_os_performance_counters** to observe the SQL Server instance's current **page life expectancy** value.
114
-
```
115
-
SELECT
116
-
case when object_name like '%Buffer Manager%' and counter_name = 'Page life expectancy' then cntr_value end AS PLE_s
117
-
FROM sys.dm_os_performance_counters
118
-
WHERE case when object_name like '%Buffer Manager%' and counter_name = 'Page life expectancy' then cntr_value end is not null;
0 commit comments