A naukri.com initiative
Amazon
1M
123
Image Credit: Amazon
Capture and diagnose I/O bottlenecks on Amazon RDS for SQL Server
- In this post, we demonstrate how you can use Amazon RDS monitoring tools along with SQL Server monitoring capabilities to capture, diagnose, and resolve I/O issues on an RDS for SQL Server instance.
- It’s important to understand the I/O requirements of a SQL Server workload, which is dependent on various factors like query access patterns, database schema, and state of database maintenance.
- Before we dive into understanding I/O bottlenecks, let’s examine the configuration of an RDS instance, including the instance class, storage type, and storage size.
- I/O bottleneck can be detected by checking how fast the I/O request is processed, understanding I/O latencies and throughput, identifying top SQLs contributing to high I/O activity and understanding database physical and logical design.
- Addressing bottlenecks on a database typically requires multiple approaches. The first and foremost is to ensure that the database design, schema, and maintenance are optimal for best performance.
- We observed that tempdb is also busy contributing to I/O and network utilization because tempdb is on an EBS volume, indicating a tempdb bottleneck.
- We showed you how to use Amazon RDS Performance Insights to identify and diagnose I/O performance-related issues.
- Additionally, we discussed other performance optimization strategies that can serve as guidance to improve the I/O performance of your RDS instance.
- This change reduced the network throughput usage as compared to before.
- Besides right-sizing your RDS instance, table and index design as well as optimized code can help to minimize I/O bottlenecks.
Read Full Article
7 Likes
For uninterrupted reading, download the app