SQL Performance Tuning in Bulk in AWS

My company recently moved to AWS, so I’ve been looking for more information specific to SQL Server performance tuning in AWS. Unfortunately, a lot of what I’m finding is marketing copy devoid of technical detail. Apparently, moving my data to the cloud is great for all company sizes, always cheaper, and will cook for me and do my dishes after! Groovy. Can I get actual, actionable technical data with less hype, please?

We’re using EC2s because RDS didn’t have features that we were using and needed. Because of the way our app is written, we have very particular needs, especially in regards to backups.

Also, we have A LOT of databases. Like, A LOT. Don’t worry, they’re all log-shipped. All 18,000+ of them.

So.

There’s a lot of advice along the lines of standard SQL Server storage advice about separating my random reads from my sequential writes… but if I have A LOT of database log files on the same drive, are they really sequential writes?

Also, of these databases, some of them are really busy:

And some of them are accessed maybe once a year if that. I previously had tiered storage, where the busy ones were on the best storage and the barely used ones were on trash storage, but this was deemed unnecessary when we moved to AWS.

So. Things I can do to improve performance for SQL Server while I’m being mean to it in terms of how many databases it has:

1. Where is TempDB located? AWS recommends placing TempDB on its own special instance store.
2. Watch for master and msdb (especially msdb) contention. They’re both on C, is C fast enough? Should I move them?
3. Do we have enough memory? Is our storage fast enough? Is the EC2 appropriately sized? Where are our bottlenecks?
4. A lot of the advice is to find poorly performing queries and rewrite them to be more efficient. Thus far that’s something I would ask Engineering to do. Ditto for index improvements. I’d be happy to take a look at it, though!

Quirks related to Red Gate SQL Backup

I’m a big fan of Red Gate, but there are some quirks to be aware of.

1. If you’re being mean to SQL Server and loading it up with databases, SQL Backup might become upset with the number of files in a couple of locations: C:\ProgramData\Red Gate\SQL Backup\Log\[instancename] and wherever you’re writing your log backups. The primary symptom of this is slowness. Perhaps this is related to the “cleanup” feature that I was trying to use, but this feature is apparently not intended for our scale.
2. Red Gate has native features to write to network shares, or to S3! Hooray! But they don’t write directly to S3; they go into the same copy queue for S3 as they go to for network copies. Don’t get happy with cleanup.
3. SQL Backup is scriptable, which I am using extensively.

Other:

AWS has excellent PowerShell integration. Seriously, it’s LOVE.