Instance memory check


Job details

Name: Instance memory check
Platform: Sqlserver
Category: Performance
Description: This task collects the size of memory objects that are currently allocated by the SQL Server
Long description: The alert checks the target memory value of the SQL Server instance, and gives a warning/alarm if the instance is not able to allocate a certain percentage of the total server/machine memory.
Version: 2
Default schedule: 3,23,43 * * *
Requires engine install: No
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[engine_edition = ‘Microsoft SQL Server’]→i/$i[maj_version > ‘2000′ & hasengine =‘NO’ & priv_processadmin = 0]

Parameters

Name Default value Description
warning_threshold 3 Gives a warning if the instance can not allocate more than this percentage value of the total server memory.
alarm_threshold 3 Gives an alarm if the instance can not allocate more than this percentage value of the total server memory.

Job Summary

select cast(value as int) from master.sys.configurations where name = 'max server memory (MB)'

Detail Analysis

The job performs several checks:

This monitoring primarily focuses on instances that do not have an engine (‘hasengine = ‘NO’‘) and where the user does not have ‘processadmin’ privileges.

Configuration Parameters

The job configuration includes parameters such as:

Output and Alerts

The job will output several key memory metrics:

These values are then used to calculate the percentage usage of instance memory against the maximum allowed server memory. Depending on these calculations:

Report and Data Presentation

Reporting is facilitated by data visualization concepts, presenting detailed tables including:

Physical Memory (GB)
Instance Memory Usage (MB)
Target Memory (MB)
Max Memory (MB)

These tables are dynamically generated based on real-time monitoring data, and they form a comprehensive report that can be scheduled periodically (default every minute) or triggered based on specific events.

In summary, this job efficiently monitors SQL Server’s ability to allocate memory, ensuring optimal performance and providing early warnings for potential memory-related issues. This proactive monitoring is essential for maintaining the health of SQL Server instances and the stability of the hosting server.