Welcome Back! in the last post, we discussed some important information that you need to be aware of when provisioning SQL Server in Azure virtual machines. More specifically, we talked about squeezing extra performance out your virtual machine and storage accounts. Next, we will talk about how to configure storage at the operating system level and multiple configuration changes in SQL Server that will help with performance and backups.
Individual data disks attached to an Azure virtual machine have a maximum throughput of 300-500 IOPS depending on the VM series. This doesn’t seem like a lot, and it isn’t, but there is an important workaround that helps overcome this limit and it involves striping your data disks. Disk striping is the process of dividing a body of data into blocks and spreading the data blocks across multiple storage devices, such as hard disks or solid-state drives. In this case, Azure storage backed vhds represent the storage devices that are being striped. Windows 8 and Windows Server 2012 come with a feature called storage spaces. Storage spaces allow you create a pool of storage from multiple data disks so you can then allocate disk drives from free space in the pool. See the Microsoft documentation for specifics on formatting your data drives and allocation unit sizes. One final thing is that you should separate your data and log file I/O paths to obtain dedicated IOPs for each type of operation. The real trick is to create each vhd in a separate container or even storage account if needed and then create dedicated storage, log, and backup drives in Windows. Long story short, each container in a storage account corresponds to a different partition with the azure storage subsystem which means that data can be accessed from multiple partitions in parallel. This is how you can scale your IOPS efficiently.
SQL Server is a very large product with so many many configuration options that it can be difficult to setup correctly let alone plan for performance and availability. We discussed this briefly before but isolating your tempdb file on a dedicated drive can really help you squeeze extra performance out of your SQL instance. Azure VM’s all have a temporary storage drive (d:) that work great for this purpose. If you are using SQL Server 2014 and have premium storage then you need to look at the buffer pool extension. Introduced in SQL Server 2014, the buffer pool extension integrates solid-state storage to the Database Engine buffer pool and can significantly improve I/O throughput. See the link below on for details on how to move your tempdb and configure the buffer pool extensions.
Another quick change you can make to your databases is to limit or disable autogrow. The default values provided by SQL are bad in most costs and will cause your database to spending way too much time expanding it’s footprint instead of serving requests. In general I find that setting a fixed size for auto growth works much better. Something in the 100MB – 1GB range depending on workload should accommodate most scenarios. Also, you will want to disable autoshrink on your databases. The overhead and unpredictability introduced by by these operations aren’t worth the cost so you should be handling these tasks during your maintenance windows. Finally, don’t forget to apply these settings to your Model database so that new databases will be provisioned exactly the way you want them.
Finally, the topic of maintenance. Having a maintenance plan that backs up your transaction logs and databases periodically is imperative. This will help you expand predictably and effectively. And don’t forget your integrity checks. Make sure you your backup drive is separate from your data/log drives to minimize the performance impact. SQL Server 2014+ also supports new features that allow you to backup directly to blob storage. This is a great option that eliminates another abstraction layer in the storage chain. I recently came across this awesome SQL script that is great for automating maintenance procedures and even supports the Backup to URL feature. I highly recommend it as a starting point for your maintenance plans.
As you can see, there are quite a few things that you need to be aware of when deploying SQL in general and more specifically in Azure. There are some other things you should research including service packs and locked pages so take a look at the reference links below for further clarification and reach out directly or through comments with your questions.