3.16 MemUtil

Use this Knowledge Script to monitor the amount of memory used by Microsoft SQL Server processes: sqlservr, sqlagent, and sqlexec. This script raises an event if memory usage exceeds the maximum threshold you set, and if the number of free buffer pages falls below the minimum threshold you set.

Resource Object

Microsoft SQL Server

Default Schedule

The default schedule for this script is Every 10 minutes.

Setting Parameter Values

Set the following parameters as needed:

Description

How to Set It

General Settings

Job Failure Notification

Raise event if job fails unexpectedly?

Select Yes to raise an event if the SQLServer_MemUtil job fails unexpectedly. The default is Yes.

Event severity when job fails unexpectedly

Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the job fails unexpectedly. The default is 5.

Additional Settings

Event Details

Event detail format

Select the format in which you want to display the event detail. You can select from HTML Table or Plain Text. The default is HTML Table.

Monitor memory utilization

Event Notification

Raise event if memory usage exceeds threshold?

Select Yes to raise an event if the amount of memory used by the server process and the agent process exceeds the threshold. The default is Yes.

Event severity when memory usage exceeds threshold

Set the event severity level, from 1 to 40, to indicate the importance of an event in which the amount of memory used by the server process and the agent process exceeds the threshold. The default is 5.

Threshold- Maximum memory used by server process

Specify the maximum amount of memory that can be used by a server process before an event is raised. The default is 2000 MB.

Threshold- Maximum memory used by agent process

Specify the maximum amount of memory that can be used by an agent process before an event is raised. The default is 75 MB.

Raise event if server memory usage exceeds threshold?

Select Yes to raise an event if the amount of server memory used by Microsoft SQL Server and all related processes exceeds the threshold. The default is Yes.

Event severity when server memory usage exceeds threshold

Set the event severity level, from 1 to 40, to indicate the importance of an event in which the amount of memory used by Microsoft SQL Server and all related processes exceeds the threshold. The default is 5.

Threshold- Maximum server memory used

Specify the maximum amount of memory that can be consumed by Microsoft SQL Server and all related processes before an event is raised. The default is 3000 MB.

Raise event if number of free buffer pages are below threshold?

Select Yes to raise an event if the number of free buffer pages falls below the threshold. The default is unselected.

NOTE:This parameter is applicable for a version prior to SQL Server 2012.

Event severity when number of free buffer pages are below threshold

Set the event severity level, from 1 to 40, to indicate the importance of an event in which the number of free buffer pages falls below the threshold. The default is 5.

Threshold- Minimum number of free buffer pages

Specify the minimum number of buffer pages that must be free to prevent an event from being raised. The default is 50 pages.

Raise event if free memory is below threshold?

Select Yes to raise an event if the free memory available with Microsoft SQL Server falls below threshold. The default is unselected.

NOTE:This parameter is applicable for SQL Server 2012 or a later version.

Event severity when free memory is below threshold

Set the event severity level, from 1 to 40, to indicate the importance of an event in which the free memory available with Microsoft SQL Server falls below threshold. The default is 5.

Threshold- Minimum free memory

Specify the amount of memory that must be free with Microsoft SQL Server to prevent an event from being raised. The default is 100 MB.

Data Collection

Collect data for maximum memory used?

Select Yes to collect maximum memory used data for charts and reports. If enabled, data collection returns the maximum amount of memory used by the server process and the agent process.

Collect data for server memory used?

Select Yes to collect server memory used data for charts and reports. If enabled, data collection returns the amount of memory used by Microsoft SQL Server and all related processes.

Collect data for number of free buffer pages?

Select Yes to collect the number of free buffer pages for charts and reports. If enabled, data collection returns the number of buffer pages that are available.

NOTE:This parameter is applicable for a version prior to SQL Server 2012.

Collect data for free memory?

Select Yes to collect the free memory of Microsoft SQL Server for charts and reports. If enabled, data collection returns the amount of free memory available with Microsoft SQL Server.

NOTE:This parameter is applicable for SQL Server 2012 or a later version.