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

Purpose: The monitoring job in dbWatch Control Center is designed to track the memory allocation of a Microsoft SQL Server instance and alert when it cannot allocate a sufficient percentage of the total server or machine memory.
Why: Monitoring memory allocation is critical because it helps ensure that the SQL Server instance has enough memory to operate efficiently and effectively. Insufficient memory allocation can lead to performance degradation and affect the stability of the server, especially in environments hosting multiple instances or heavy applications.
Manual checking: You can manually monitor the memory allocation of the SQL Server instance by executing the following SQL command:

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

Detail Analysis

The job performs several checks:

It identifies if the SQL Server version is above 2000.
Ensures that the instance is not a schema or a subsystem engine.
Checks if the user has the ‘processadmin’ privilege.

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:

Warning threshold: The percentage of total server memory that if the SQL Server instance can allocate less than this threshold, a warning is triggered.
Alarm threshold: A lower percentage indicating severe memory allocation issues, triggering an alarm.

Output and Alerts

The job will output several key memory metrics:

Physical server memory in GB.
Memory currently used by the instance in MB.
Target memory for the instance in MB.
Maximum server memory setting in MB.

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

If the target memory usage percentage is below the warning threshold but above the alarm threshold, a status of ‘1′ (warning) is triggered.
If the target memory usage is below the alarm threshold, a status of ‘2′ (alarm) is triggered.

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.