How can I create CloudWatch alarms to monitor my Amazon RDS SQL Server DB instance’s memory consumption using Enhanced Monitoring?

Last updated: 2020-06-02

My Amazon Relational Database Service (Amazon RDS) SQL Server DB instance is using more memory than expected. How can I set an Amazon CloudWatch alarm to monitor how much memory is consumed by SQL Server only?

Short Description

After you enable Enhanced Monitoring for your RDS DB instance, you can create a CloudWatch alarm and use Amazon Simple Notification Service (Amazon SNS) to receive notifications about SQL Server memory consumption. This example uses the Enhanced Monitoring metric sqlServerTotKb to create a CloudWatch alarm and sends an SNS notification about the memory consumed by your RDS SQL Server DB instance.

Resolution

  1. Open the CloudWatch console, and then choose Log groups from the navigation pane.
  2. Filter for RDSOSMetrics from the list of Log Groups, and then choose Create Metric Filter.
  3. Enter a Filter Pattern for your RDS DB instance, such as {$.instanceID = "nameOfYourRDSInstance"}. For example, you can enter the RDS DB instance name {$.instanceID ="sqltest"}.
  4. From the Select Log Data to Test section, choose the resource ID of your RDS DB instance.

    Note: To find the resource ID of your RDS DB instance, open the Amazon RDS console, and then choose Databases from the navigation pane. Choose your RDS DB instance, and navigate to the Configuration tab. The Resource id appears in the Instance section.

  5. Choose Assign Metric.
  6. Enter a Metric Namespace and Metric Name. See the following example:

    Metric Namespace: EMMetric

    Metric Name: SQLServerMemoryConsumption

  7. Choose Show advanced metric settings.
  8. Enter the metric value $.memory.sqlServerTotKb, and then choose Create Filter. A custom metric with the name specified is created. This metric reports the Enhanced Monitoring data in a CloudWatch graph.

  9. Choose Create Alarm.
  10. From the Metrics section on the next page, verify the Namespace and Metric name, and set the Period to 1 minute.
  11. From the Conditions section, define the threshold of the metric alarm. See the following example:

    Threshold type: Static

    Whenever SQLServerMemoryConsumption is: Greater > threshold

    Than: Enter 20971520

    Note: To specify 20 GiB as a threshold, enter the value in KiB. For example, 20971520 (20 * 1024 * 1024).

  12. Choose Next.
  13. From the Configure Action section, choose In Alarm.
  14. Select an SNS topic, or choose Create new topic using the email address where you want to receive alerts, and then choose Next.
  15. Enter an alarm name and description. See the following example:

    Alarm name: RDS DB instance: SQLTEST: SQL Server Memory Consumption> 20 GiB

    Alarm description: SQL Server Memory consumption on your RDS DB instance is high

  16. Choose Next, and from the Preview and Create page, verify the details of your alarm.
  17. Choose Create alarm.

After the alarm is created, you can view it under Alarms on the CloudWatch console. Whenever your SQL Server memory usage exceeds the defined threshold, your alarm enters the ALARM state and you receive an email notification.


Did this article help you?

Anything we could improve?


Need more help?