Performance Monitoring and Cost Management

Performance Monitoring and Cost Management in Cloud Databases

Cloud platforms give database administrators real-time visibility into how their databases are running and what they cost. Understanding how to read that data, act on it, and right-size resources is a core DBA skill tested by Microsoft DP-300, the Oracle DBA CA, and the DAMA CDMP.

Key Performance Metrics to Watch

Three metrics appear on almost every cloud monitoring checklist:

• CPU Utilization – the percentage of allocated compute being used. Sustained CPU above roughly 80–85% signals a workload that exceeds the current tier, a missing index forcing full scans, or a runaway query.
• FreeStorageSpace – the number of bytes remaining on the database storage volume. When this drops near zero, writes fail and the database goes offline. Alert before it becomes critical.
• DatabaseConnections – the count of open connections. A sharp spike can indicate a connection-pool leak or an application retry storm; a sustained high count may mean the instance needs more memory or a higher connection limit.

AWS surfaces these metrics through Amazon CloudWatch. Each metric is a time series you can graph, filter by instance, and attach an alarm to. Azure surfaces similar data through Azure Monitor, with an extra blade called Query Performance Insight that shows the top queries by CPU, duration, or I/O so you can drill straight to the problem SQL.

Setting Threshold-Based Alerts

An alert does nothing useful unless the threshold is meaningful. A CPU alarm set at 100% fires too late; one set at 50% fires constantly. A practical starting point for a production RDS instance is:

• CPU Utilization ≥ 80% sustained for 5 consecutive minutes – use CloudWatch's "M out of N" evaluation period (5 out of 5) so brief spikes do not page your team.
• FreeStorageSpace ≤ 10% of provisioned storage – calculate the byte value yourself and enter it as a static threshold.
• DatabaseConnections ≥ 90% of the instance's max_connections parameter – check the RDS documentation for the default value for your instance class.

In CloudWatch, you create an alarm by choosing a metric, setting the threshold condition, and attaching an SNS topic that sends an email or text to the on-call team. Document the SNS ARN and the alarm ARN; you will need both if you automate remediation later.

In Azure Monitor you create Metric Alerts with the same logic: choose a metric, set a threshold, pick an Action Group that holds the notification targets. Query Performance Insight does not send alerts on its own, but it identifies the queries you should target for tuning before alerts become necessary.

Index Tuning from Query Performance Insight

Query Performance Insight ranks queries by resource consumption. When you find a high-CPU query, open its execution plan. Look for:

• Index Scan on a large table where an Index Seek should be possible – suggests a missing index on the filter column.
• Key Lookup after a nonclustered index seek – suggests the index is missing the columns the query selects (a covering index would fix this).
• A listed index that never appears in any plan – that index is unused and costs write overhead; consider dropping it.

When you write the `CREATE INDEX` statement, include:

1. A meaningful name (e.g., `IX_Orders_CustomerID_OrderDate`).

2. The table and the key column(s) in the `ON` clause.

3. Any covering columns in an `INCLUDE` clause.

4. `WITH (ONLINE = ON)` if the table must stay available during the build (supported on most Azure SQL tiers).

Cost Optimization and Rightsizing

Cloud spend grows when instances are provisioned at peak capacity and then never reviewed. Rightsizing means matching the compute tier to the actual workload. Steps:

1. Pull a 30-day utilization report from AWS Cost Explorer or Azure Cost Management.

2. Identify instances averaging below 40% CPU and low memory pressure for the full period – these are candidates for a smaller tier.

3. Check storage usage – if provisioned IOPS are far above consumed IOPS, switch to a lower IOPS tier or gp3 storage on AWS.

4. Compare on-demand vs. reserved pricing – AWS Reserved Instances and Azure Reserved VM Capacity offer significant discounts for 1- or 3-year commitments on stable workloads. Use the AWS Pricing Calculator or Azure Pricing Calculator (both are free, publicly available, and require no login) to model the savings.

5. Write the recommendation with specific numbers: current monthly cost, proposed tier, new monthly cost, and percentage saved.

Safety and Access Controls

Performance monitoring involves reading live production data. Always use a read-only IAM role or Azure RBAC role scoped to monitoring; never run optimization tests against production without a change-control ticket. When modifying indexes on production tables, run changes during a maintenance window or use online index operations to avoid blocking. Keep backups verified before any schema change.

Cost changes—like moving to a different compute tier—can require a brief instance restart. Coordinate with application owners and schedule downtime properly. Document every change in your change log.