SQL Server Performance on IaaS

Of course, when moving your SQL workloads to the cloud, a good option would be going "server-less". But we know it is not always possible, preferable or most cost effective - IOW "it depends".

SQL Server Performance is a deep deep topic. If you don't believe me, ask Paul Randal. I am only focusing on a very specific segment in SQL Server Performance on IaaS.

For those who choose SQL Server on IaaS, and then keep scratching their head thinking "why my SQL is not performing well on IaaS, it was great on-premises", I have some quick tips and tricks. These are "low hanging fruit" that you can harvest without breaking your wallet.

There are 3 things that severely impact your SQL Server performance on IaaS: Storage, Storage, Storage. :) I will go on explaining all 3 one by one.

1. Storage: IOPS and Throughput: Consider your throughput as well as your IOPS. In Azure, Ultra SSD's does not need striping, but Premium SSD's can benefit from it to get more throughput. In AWS, make sure that you understand and wisely choose the EBS volume types available to you.
2. Storage: TempDB, data and log files. I see a lot of SQL Servers on IaaS, and I see a lot of data and log volumes attached to the server, while actual TempDB data and logs files are still sitting on the C drive. It requires a little bit of an effort to get this right, but it pays off. In Azure, use the "D:" drive (the locally attached "FREE" SSD) for TempDB files because TempDB is recreated upon server restart, so there is no risk of data loss.
3. Storage: Use Storage options available to you wisely to your benefit. Enable "Read Caching" on the data volume. DO NOT enable any caching on the log file volume. Enable instant file initialization to reduce the impact of file-growth activities.

If you are using SQL Server on IaaS on Azure, I would strongly suggest you take advantage of SQL Server IaaS extension to easily monitor and manage SQL Server specifics on your server. If you are on AWS, Cloud Watch is your friend.

To conclude, storage is the most impactful element of SQL Server Performance on IaaS in my experience, as it was on premise. I see it again and again. Therefore, I thought I should share some easy pointers even though it may be "stating the obvious".

Until the next episode, happy clouding!