Azure VM SQL Server Tempdb for temporary storage

Question

We configure SQL servers in the Azure cloud using virtual machines. When we determined the best setting for our data / logs / tempdb, we came across a lot of blog posts that recommend hosting tempdb on a temporary storage drive provided by Azure. However, more in-depth studies have shown this information from Microsoft , which says that this should not be done.

So, we still have the following questions:

• Can someone provide a current final answer to the question of whether tempdb should be placed in temporary storage or not?
• Does anyone have clear results regarding this issue?
• What are the possible side effects if tempdb is put in temporary storage?

Answer 1

There was some confusion about this since the initial recommendation was to place tempdb on the D: drive. This is no longer the case. For the latest information, I recommend that you read the "Performance Guide for SQL Server on the Windows Azure Virtual Machine" manual: http://msdn.microsoft.com/en-us/library/windowsazure/dn248436.aspx

Here is an excerpt with the TempDB section:

As mentioned in the "Disks and Caching Options for Windows Azure Virtual Machines" section, we recommend placing tempDB on the operating system disk or data disk instead of a temporary disk (D :). The following are the three main reasons for this recommendation, based on our internal testing with SQL Server test workloads.

• Dispersion of performance. In our testing, we noticed that you can get the same level of performance that you get on D :, if not more than IOPS from the operating system or one data disk. However, D: drive performance is not guaranteed to be as predictable as an operating system or data drive. 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 are using.

• VM downtime configuration: If the virtual machine shuts down (due to planned or unplanned reasons) so that SQL Server can recreate tempDB under drive D: a service account in which the SQL Server service must first have local administrator rights. In addition, it is common practice with local SQL deployments to save the database and log files (including tempDB) in a separate folder, in which case the folder must be created before SQL Server starts. For most customers, this additional reconfiguration is not worth the return.

• Performance bottleneck: if you host tempdb on drive D: and your application workloads greatly affect tempDB, this can cause a performance bottleneck because drive D: can introduce limitations in terms of IOPS bandwidth. Instead, place tempDB on the operating system or data disks for more flexibility. For more information about best configuration settings for optimizing tempdb, see "Compiling SQL Server TempDB IO Best Practices".

Answer 2

Update: Now that temporary disks are available as SSDs for the D-Series Azure VM, the document quoted by @CSharpRocks is a bit outdated. See the following two articles for more recent recommendations (as of late 2014 to mid-2015):

• Best Performance Practices for SQL Server in Azure Virtual Machines (April 2015)
• Using SSDs in Azure VM to store SQL Server TempDB extensions and buffer extensions (September 2014)

The above articles explicitly mention hosting tempdb and Buffer Pool Extensions in D: \. Excerpts:

Only store tempdb and / or buffer pool extensions on disk D if using D-Series Virtual Machines (VMs). Unlike other VM series, the D drive in D-Series virtual machines is based on SSD. This can improve the performance of workloads that use temporary objects heavily or that have work sets that do not fit into memory.

Answer 3

During 2017:

I have a long-running stored procedure that works intensively in tempdb. Setting up Tempdb on the OS disk, since Azure configured it by default on the DSv2 machine using SDDs, the request was completed after about a minute and a half.

Moving Tempdb to temporary storage (and doing nothing) changed the launch request in 57 seconds, which is about a 33% performance improvement. The request was repeated in both cases, and the time was (give or take) sequentially in these numbers.

Putting TempDB in temporary storage requires special consideration in terms of starting an SQL server. There are two approaches. One of them is to point the files to root D and grant local administration permissions to the SQL server. This is a scenario that you want to consider if you already have something else, starting the SQL Server process, and not just starting the service automatically. Otherwise, he raises his brows.

The second option is to configure the SQL Server service to start manually, and then write a powershell script file to run it, and put this powershell script in a scheduled task to run at startup. In a PowerShell script, first verify that the directory exists in the temporary storage before starting the SQL server.

It was already linked to another answer, but this document has been updated since 2017, and it does not officially recommend this kind of setup for TempDb, and not just move it from the OS section. However, it says:

If your workload makes heavy use of TempDB (for example, for temporary objects or complex joins), saving TempDB to drive D can result in higher TempDB throughput and lower TempDB latency.

My experience has confirmed that the last line.