Instance memory usage


Job details

Name: Instance memory usage
Platform: Sqlserver
Category: Performance
Description: Collects total memory usage and data cache memory usage for SQL Server instance. This dbWatch task can be configured to automatically reduce the amount of memory used by the SQL Server instance.
Long description: Collects total memory usage and data cache memory usage for SQL Server instance. This dbWatch task can be configured to automatically reduce the amount of memory used by the SQL Server instance.
Version: 2
Default schedule: 0 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2008′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name Default value Description
history threshold 7 The number of days the statistics are hold in the history table.
buffer cache usage threshold 1000 The average value of reads per second before the dbWatch task starts to reduce the memory usage of the SQL Server instance. This parameter is used in combination with the “time threshold” parameter.
time threshold 60 The amount of minutes which must past before the dbWatch task starts to reduce memory usage by the SQL Server instance if the average value of buffer reads per second stays bellow the “buffer cache usage threshold” value.
min memory usage 500 The minimum amount of memory (in MB) the SQL Server instance can be reduced to.
enable memory reduction NO Enables the dbWatch task to reduce the SQL Server instance memory usage (possible values YES/NO). This parameter will stay enabled for a period of time controlled by the parameter “memory reduction time threshold” (default value: two weeks – 14 days).
memory reduction steps 20 The amount of memory (in MB) the SQL Server instance will be reduced with when parameters “buffer cache usage threshold” and “time threshold” values are reached.
max memory usage 1500 The maximum amount of memory to be allocated to SQL Server instance (in MB).
memory reduction time threshold 14 The number of days the parameter “enable memory reduction” will stay enabled before automatic disableing.
auto memory configure NO Auto memory configuration option. If set to “YES“ the task will set the “max server memory (MB)” to a value based on collected statistics over period of time controlled by the parameter “Memory reduction time threshold”. The parameter “enable memory reduction” must be set to “YES“.
min data cache size 200 The minimum data cache size (in MB) the SQL Server instance can be reduced to when memory reduction is enabled.
min page life expectancy 60 The minimum value (in minutes) of “Page Life Expectancy” may have for memory reduction to be allowed (when parameter “enable memory reduction” is set to “YES“).

Job Summary

SELECT total_physical_memory_kb, available_physical_memory_kb, total_page_file_kb, available_page_file_kb FROM sys.dm_os_sys_memory;
SELECT name, value, value_in_use, minimum, maximum, is_dynamic, is_advanced FROM sys.configurations WHERE name LIKE '%server memory%';

Job Logic

Dependencies and Parameters

Presentations and Reports

Modifications and Upgrades

In summary, this dbWatch task is an essential utility for database administrators who aim to safeguard and optimize the performance of SQL Server environments through effective and efficient memory management strategies.