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

Purpose: The monitoring job is designed to track and manage memory usage for SQL Server instances. It ensures optimal memory use and performance by monitoring various parameters and establishing controls for memory allocation.
Why: Efficient memory usage is critical for the performance of SQL Server instances. This job helps in avoiding over-utilization or under-utilization of memory which can lead to performance degradation or system instability. By dynamically adjusting memory settings based on actual usage data, the risk of system slowdowns or crashes is minimized.
Manual checking: Manual tracking can be done by querying specific system views and configuration settings in SQL Server. For instance, checking the current memory usage can be manually done using the following query:

SELECT total_physical_memory_kb FROM sys.dm_os_sys_memory

Detailed Monitoring Approaches and Output

Dependencies

Instance Memory Usage Processing
This monitors the actual usage of memory and actively modifies the configuration to optimize performance.
Buffer Cache Last Statistics

Parameters Configuration

Memory thresholds and limits are set to manage the memory reduction effectively.
Certain thresholds trigger specific memory handling operations (like memory reduction).
Configuration parameters, like buffer cache usage threshold and history threshold, provide control over how aggressive the monitoring job is in maintaining performance.

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:

inst_mem_period_update_proc
An automated procedure that updates memory configuration settings based on collected data over specific periods.
inst_mem_usage_proc

Monitoring Outputs and Presentations

This job facilitates several reports and visual presentations including:

Current configuration of SQL Server instances regarding memory limits and usage.
Historical memory usage and buffer statistics.
Automated adjustment records over predefined periods providing insight into memory management effectiveness.

Visual Presentations Include:

Charts displaying trends of physical memory, target memory, instance memory limits, and usage over time.
Period statistics summarizing actions taken during specific monitoring cycles such as adjustments made, reasons, and outcomes.

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.