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.
Microsoft SQL Server
The default schedule for this script is Every 10 minutes.
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. |