You’re hosting your SQL Server databases in Amazon RDS, and performance has been getting slower over time. You’re not sure if it’s storage IOPs, instance size, SQL Server configuration, queries, or indexes. What’s the easiest way to find out?
Ask a few questions:
Are you using SQL Server Enterprise Edition? The smallest EE server, a db.r3.2xlarge, costs about $4,241 per month on demand (which isn’t the cheapest way to buy, of course). The next smallest doubles in cost, which means if the performance tuning efforts could drop you by just one single instance size, a consulting engagement would pay for itself well within two months.
Are you using mirroring for multi-AZ protection? If so, the delays required for sequential writes between availability zones may be your biggest bottleneck for inserts, updates, and deletes. Check your wait types with sp_BlitzFirst, and if the top ones are database mirroring, then the data changes aren’t likely to get faster with hardware tuning. Increased IOPs might help – but it takes deeper digging to get to that conclusion. It’s time to look at reducing your change rate in the database. If, on the other hand, your biggest bottleneck consists of select queries, consulting can help.
Are you locked into a long-reserved instance? You can sell reserved EC2 instances on the secondary market, but you can’t sell RDS instances as of this writing. If you’re having performance problems on it, this is definitely a time to call for consulting help fast. You want to avoid dumping the smaller instance and jumping into another commitment if a growing customer base or slowing code base could mean yet another instance type change.
Or are you running a single Standard Edition instance in just one AZ? Try standing up another RDS instance – but this time with the largest Standard Edition instance type you can get, around $12/hour as of this writing. Run the same types of queries against it, and within a couple hundred bucks of experimentation, you can get an idea of whether or not hardware will be a cheap enough solution. Granted – your time isn’t free – but it’s cheaper than a consulting engagement.
These questions help you figure out when it’s just cheaper to throw more virtual hardware at it.
3 Comments. Leave new
Good advice, Brent. However, I’d recommend that my clients look at Azure SQL Database if they’re going to right-scale instead. Even if I have to set up a VPN between the AWS VPC and the Azure Resource Group, it’s worth it to have my SQL databases running where they’re going to get some real love from Microsoft. There you get the latest and greatest features. For example, I was solving a problem for a client recently where I wished I had access to the new SPLIT_STRING function in SQL 2016. We wanted to replace a bunch of XML gobbledygook with some string comma delimited strings that an app was sending us. Then I remembered that the Azure SQL Database my client was on was V12. So we set the compatibility mode to 130 and BOOM! Splittin’ strings like a banjo player!
Kevin – yeah, there’s just a lot of gotchas around switching between Amazon RDS and Azure SQL DB. For example, cross-database queries technically work in Azure SQL DB – but the schema and code changes required are often a bit of a gotcha.
Azure SQL DB is great – don’t get me wrong, I love it – but it’s rarely an instant lift-and-shift fit for conventionally developed apps.
I can’t help but think of Brent’s recent comments about MS removing support for CLR in Azure SQL Database. If we had our application running on it, we’d be facing a lot of late nights to get things working again.
Also, if your application tends to run lots of really small queries (lookup on one indexed column, 1 or 2 rows returned), the performance on Azure SQL Database is horrible.