How should the company identify the source of the problem?
Install and configure Amazon CloudWatch Application Insights for Microsoft .NET and Microsoft SQL Server. Use a CloudWatch dashboard to identify the root cause.
Enable RDS Performance Insights and determine which query is creating the problem. Request changes to the query to address the problem.
Use AWS X-Ray deployed with Amazon RDS to track query system traces.
Create a support request and work with AWS Support to identify the source of the issue.
Explanations:
Amazon CloudWatch Application Insights helps monitor application performance, but it’s not the most direct way to diagnose database query performance issues on Amazon RDS for SQL Server. Performance bottlenecks are typically tied to database queries or resource usage, and CloudWatch Application Insights is more geared toward overall application insights rather than detailed database performance.
Enabling RDS Performance Insights provides detailed metrics on SQL queries running on the RDS instance, allowing for a direct examination of which queries are consuming the most resources. This is the best way to identify and resolve performance issues caused by inefficient queries.
AWS X-Ray is primarily used for tracing requests through a distributed application. While it can help with tracking application flow, it is not specifically optimized for analyzing SQL Server database queries, making it less suited for identifying the root cause of slow queries in this context.
While AWS Support can help with troubleshooting, the company should first attempt to diagnose and resolve the issue using available monitoring and performance tools, such as RDS Performance Insights, before escalating to AWS Support. Using Performance Insights allows the company to quickly identify the root cause of the performance issue without needing to open a support case.