Azure VM SQL Server Tempdb on Temporary Storage

Question

We're setting up SQL servers in the Azure cloud using VMs. When we were determining the best setup for our data/logs/tempdb we ran into many blog posts that recommend placing the tempdb on the Temporary Storage drive provided by Azure. However deeper research revealed this information from Microsoft where it's said that this shouldn't be done.

So we're left with following questions:

• Can anyone provide a current closing answer to whether or not we should place the tempdb on the Temporary Storage or not?
• Does anyone have clear performance results in regard to this matter?
• What are possible side-effects if the tempdb is placed on the Temporary Storage?

Answer 1

There's some confusion about this since the original recommendation was to place tempdb on the D: drive. This is no longer true. For the latest info, I recommend that you read the "Performance Guidance for SQL Server in Windows Azure Virtual Machine" whitepaper located here: http://msdn.microsoft.com/en-us/library/windowsazure/dn248436.aspx

Here's an extract with the TempDB section:

As mentioned in section Windows Azure virtual machine disks and cache settings, we recommend that you place tempDB on the operating system disk or the data disk instead of the temporary disk (D:). Following are the three primary reasons for this recommendation based on our internal testing with SQL Server test workloads.

• Performance variance: In our testing, we noticed that you can get the same level of performance you get on D:, if not more IOPS from the operating system or a single data disk. However, the performance of D: drive is not guaranteed to be as predictable as the operating system or data disk. This is because the size of the D: drive and the performance you get from it depends on the size of the virtual machine you use.

• Configuration upon VM downtime situation: If the virtual machine gets shutdown down (due to planned or unplanned reasons), in order for SQL Server to recreate the tempDB under the D: drive, the service account under which SQL Server service is started needs to have local administrator privileges. In addition, the common practice with on-premises SQL deployments is to keep database and log files (including tempDB) in a separate folder, in which case the folder needs to be created before SQL Server starts. For most customers, this extra re-configuration overhead is not worth the return.

• Performance bottleneck: If you place tempdb on D: drive and your application workloads use tempDB heavily, this can cause performance bottleneck because the D: drive can introduce constraints in terms of IOPS throughput. Instead, place tempDB on the operating system or data disks to gain more flexibility. For more information on configuration best practices for optimizing tempdb, see Compilation of SQL Server TempDB IO Best Practices.

Answer 2

Update: Now that temp drives are available as SSDs for D-Series Azure VMs, the whitepaper cited by @CSharpRocks is slightly out of date. See the following two articles for more recent recommendations (as of late-2014 through mid-2015):

• Performance Best Practices for SQL Server in Azure Virtual Machines (April 2015)
• Using SSDs in Azure VMs to store SQL Server TempDB and Buffer Pool Extensions (September 2014)

The articles above explicitly mention placing tempdb and Buffer Pool Extensions on D:\. Excerpt:

Only store tempdb and/or Buffer Pool Extensions on the D drive when using the D-Series Virtual Machines (VMs). Unlike the other VM series, the D drive in the D-Series VMs is SSD-based. This can improve the performance of workloads that heavily use temporary objects or that have working sets which don't fit in memory.