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.