Q&A From My Azure Webinar With Scott Klein

Recently (OK, on July 23rd, 2014 to be exact) I co-presented a webinar with Scott Klein (blog | @SQLScott) titled “Know Before You Go: SQL Server on Microsoft Azure Virtual Machines“. It was a repeat performance of the session we delivered at TechEd in Houston earlier this year. There were a handful of questions asked during the webinar and as I was going through them two thoughts came to mind:

1. There is significantly more interest in Azure today than a year ago, even six months ago.
2. There is a real need for knowledge sharing, not just from Microsoft outward, but from Azure users getting together to connect, share, and learn.

So today I wanted to share some of the questions that Scott and I had about Azure during our webinar. I’m going to use just a handful of the questions for this post but you can read the full Q&A here, and you can watch our webinar (and many more) by going here.

Azure Disk Questions

You can find many of the webinar talking points contained in this MSDN article titled “Performance Best Practices for SQL Server in Azure Virtual Machines“. In that article you will find a handful of recommendations such as:

• On the data disk, both read and write caching is disabled by default. Use this default and do not enable any caching on the data disk.
• For Windows 8/Windows Server 2012 or later, use Storage Spaces. Set the stripe size to 64 KB for OLTP workloads and 256 KB for data warehousing workloads to avoid performance impact due to partition misalignment. In addition, set column count = number of physical disks. For Windows 2008 R2 or earlier, you can use dynamic disks (OS striped volumes) and the stripe size is always 64 KB.

I found that there were a lot of questions about how best to add and configure disks in an Azure VM. Here’s a few of the ones we saw related to disk management:

Why not use Azure disk caching? Shouldn’t that increase read performance?

Disk caching, while good for other server workloads, can be an issue with regards to SQL Server maintaining transactional consistency (ACID) as outlined here by Microsoft Customer Support and Services (CSS): http://blogs.msdn.com/b/psssql/archive/2013/06/03/how-it-works-the-i-o-path-sql-server-running-in-windows-azure-virtual-machine-iaas.aspx. However, as that article points out, your disks in Azure use something called the XStore to provide caching at the host level. This XStore does provide you with what is considered by SQL Server to be “stable media”, and therefore it will satisfy the ACID requirements. But the use of disk caching in Azure really depends on the workload you have, and the article goes into more details about why caching is not likely to give most users any benefit. Thus, the recommendation that it be disabled by default. 

What is the default allocation unit size?

Great question, and one that came up at TechEd as well. When adding a new disk to your Azure VM you are prompted to format the disk, and for that you need to choose an allocation unit size. The word ‘Default’ is the, well, default choice, but what does that mean? Turns out the default size is 4k. We know that we want to set this to be 64k (or possibly 256k, see above), which led to the next question…

What if I use 128-kb allocation when formatting? Will it performance better than the 64-kb allocation?

Great question! As it turns out that you might want 256-kb stripes as outlined here: http://msdn.microsoft.com/en-us/library/azure/dn133149.aspx#disks. There is this article from CSS, and although it is 9 years old it would seem that a 128k allocation unit size is not likely to help you here.

Should we create multiple disks in storage areas and/or stripe them?

Yes and yes. The MSDN post is very clear about this, and if you download the whitepaper titled “Performance Guidance for SQL Server in Azure Virtual Machines” that the MSDN article references, you will see even more talk about the use of Storage Spaces and striping.

Other Azure Administration Questions

Besides the disk management questions we fielded questions in lots of areas. In that whitepaper I just linked to you will find an exhaustive list of recommendations so we had lots of material to share. One such nugget of information is this:

• With Windows Azure disks, we have observed a “warm-up effect” that can result in a reduced rate of throughput and bandwidth for a short period of time.

I’ll let you read the whitepaper for yourself to understand the details of this effect, but it has to do with how Azure handles the load balancing of resources. Anyway, that comment led to this natural (and great!) question:

Is there a standard algorithm to warm up the disks…or a utility perhaps?

If your VM is continuously active then you need not worry about this. If the VM is inactive for about 20 minutes, then yes, your VM will be “put to sleep” in order to free up resources for other VMs. And once you start using your VM again it can take up to ten minutes for you to see your disk performance back to normal levels. So, how to keep your VM busy the entire time? Well, you could just schedule a job to run a simple query. Or, and this is what I prefer, you could use a monitoring tool (and I can recommend one for you).

Finally, here is a great question that I’ve heard from more than one person over the past year:

If using the gallery SQL VM how much preconfigured is it?

Great question! The template comes with all SQL Server services installed. You can see all the details here: http://msdn.microsoft.com/en-us/library/azure/dn133151.aspx#sqlserver.

Summary

I’ve authored previous posts on Azure, and I’ve been delivering sessions on Azure since 2010 (including a full day pre-conference seminar at TechEd in 2013 with Grant Fritchey (blog | @gfritchey), Dandy Weyn (blog | @ilikesql), and some help from Scott, too.) But clearly the need for content is greater than what is available. And as Azure changes frequently, I see that the need for content will be ongoing.

I really enjoyed presenting this webinar with Scott. It helps that I’ve known Scott for what seems like forever. Back in 2012 I was lucky enough to be the technical editor for the book he co-authored titled “Pro SQL Database for Windows Azure: SQL Server in the Cloud“. Since that book was published in 2012 Scott and I have collaborated on a handful of sessions including some at Microsoft TechEd in New Orleans, Madrid, and Houston. And every now and then we bump into each other at events such as SQL Konferenz and SQL Bits where we sit down and will just talk tech and Cloud.

I enjoy learning new things in Azure from Scott every chance I can, and I’m looking forward to Scott and I coming up with a new presentation in the coming months.