Instance memory check

Job details

Name:	Instance memory check
Platform:	Sqlserver
Category:	Performance
Description:	This job checks the target memory value of the SQL Server instance, and gives a warning/alarm if the instance is not able to allocate a
Long description:	This job checks the target memory value of the SQL Server instance, and gives a warning/alarm if the instance is not able to allocate a
Version:	1.3
Default schedule:	3,23,43 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2008′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’]

Parameters

Name	Default value	Description
warning threshold	60	Gives a warning if the
alarm threshold	30	Gives an alarm if the

Here’s the job summary and details formatted in Textile markup:

Job Summary

Purpose: The purpose of this job is to monitor the memory allocation capability of a Microsoft SQL Server instance in terms of target memory. It alerts when the instance is unable to allocate a specified percentage of the total server memory.
Why: This monitoring job is crucial for ensuring that the SQL Server instance has enough memory resources available for optimal performance. Memory shortages can lead to slower response times or even service outages. Monitoring memory allocation helps detect if other processes are consuming too much memory, which could restrict the amount of memory available to SQL Server.
Manual checking: You can manually check the memory allocation of the instance by executing the following SQL commands:

SELECT physical_memory_kb/1024 as [Physical memory], committed_kb/1024 as [Instance memory usage], committed_target_kb/1024 as [Target memory] FROM master.sys.dm_os_sys_info

Job Configuration Details

Configuration Item	Details
Group	com.dbwatch.job
Artifact ID	ms_instance_memory_check
Object	inst_mem_check_proc
Description	Checks the target memory value of the SQL Server instance and provides a warning/alarm if the instance cannot allocate a certain percentage of the total server/machine memory.
Version	1.3
Company	dbwatch.no
Default Schedule	Runs at minutes 3, 23, and 43 of each hour every day.
Dependencies	Dependent on itself (inst_mem_check_proc).
Category	Performance

Parameters

Parameter	Default Value	Description
Warning threshold	60	Gives a warning if the instance cannot allocate more than this percentage of the total server memory.
Alarm threshold	30	Triggers an alarm if the instance cannot allocate more than this percentage of the total server memory.

Reporting Details

Title: Instance Memory Usage
Reporting Schedule: Hourly
Table Content: Physical memory, Instance memory usage, and Target memory are displayed in MB. This helps identify memory utilization in relation to the instance’s settings and physical machine capabilities.
SQL Query for Report:

SELECT physical_memory_kb/1024 as [Physical memory], committed_kb/1024 as [Instance memory usage], committed_target_kb/1024 as [Target memory] FROM master.sys.dm_os_sys_info

This formatted summary provides a structured overview of the job’s intents, usability, and how it technically digs into SQL Server’s memory metrics to help with performance evaluations and capacity planning.