I run a transactional MSSQL database on a db.z1d.2xlarge RDS instance, and I've noticed that the metrics suggest I'm overprovisioned. I'm considering scaling down to a db.r7i.xlarge instance, but I've got some questions about the NVMe SSD instance store that comes with my current setup. The documentation is a bit confusing; one page suggests MSSQL SE supports db.r7i.xlarge while another states it doesn't. I'm also unsure about how the instance store is being utilized, especially since there's no CloudWatch metrics that seem to reflect its use. I know my current instance has 300 GB of NVMe storage—how does that affect my database performance, particularly if I switch to an instance type lacking that store? Since this is a production database with 24/7 operation, I need to plan the transition carefully, and I don't have an accurate load generator for testing.
3 Answers
If you're not seeing LocalStorage metrics in CloudWatch, it may be an issue with the specific instance type. For the db.z1d instances, the documentation does say it uses instance store for TempDB, so you might want to double-check that with AWS support. You can typically view TempDB locations using SQL tools, so that might give you some insight into how it's currently set up and utilized.
No problem! Hope you get it sorted out soon.
The NVMe instance store is typically used for TempDB storage in SQL Server on your RDS instance. To check if TempDB is placed on the NVMe drive or managed on EBS, you can look at your database settings. If your database is utilizing Read Committed Snapshot Isolation, performance could change if you move away from the NVMe storage since read operations could be affected. You might want to monitor your TempDB usage closely before making any switches.
Do you have any tips on finding out where TempDB is currently stored? I’m not too familiar with MSSQL.
You can use SQL Server Management Studio (SSMS) to check the location of TempDB files. Simply run a query to see their paths.
Switching away from MSSQL could save you some headaches since it’s not the most cloud-friendly database solution out there. However, if you're locked into this third-party product, I understand the frustration. It sounds like you’ve already got a solid AWS-native ecosystem built around it, which is good! Just ensure you keep an eye on how that NVMe storage is being utilized—it could save you on performance costs if monitored correctly.
Yeah, but moving away from MSSQL isn't an option for me either. It’s tough navigating AWS with a MS-centric setup!
Yeah, it can be a learning curve. You've got a lot of AWS tools at your disposal. Just make sure to leverage them.
Thanks for clarifying! I'll check with AWS support to figure this out.