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:
Version: 2
Default schedule: 0 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & maj_version < ‘2012′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’]

Parameters

Name Default value Description
history threshold 4 The number of days the statistics are hold in the history table.
buffer cache usage threshold 1000
time threshold 60
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).
memory reduction steps 20
max memory usage 1500 The maximum amount of memory to be allocated to SQL Server instance.
memory reduction time threshold 14
auto memory configure NO
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 FROM sys.dm_os_sys_memory

Detailed Monitoring Approaches and Output

Dependencies

Parameters Configuration

Implementation Tables and Procedures

Table Implementations:

Table Name Description
inst_buffer_cache_last Stores the last captured buffer cache data for quick retrieval.
inst_mem_usage_tab Captures snapshots of memory usage statistics over regular intervals.
inst_mem_usage_tab_avg Provides average, maximum, and minimum memory usage over daily periods to analyze trends.
inst_mem_reduction_periods Tracks periods where memory reduction measures were active, including initial and resulting memory configurations.

Procedures:

Monitoring Outputs and Presentations

This job facilitates several reports and visual presentations including:

Visual Presentations Include:

This comprehensive set of tools aids in maintaining the stability and performance of SQL Server instances by managing memory proactively. The generated reports also help in compliance with internal standards and can guide capacity planning and future scaling decisions.