Questions Using the Azure Temp Drive for TempDB

Chris Stamey 1 Reputation point
2021-02-22T15:55:43.247+00:00

I have seen several articles around the Internet describing the use of the Azure temporary drive for TempDB storage. It brought up a couple of questions for me. 1. What is the temp drive physical hardware, Standard SSD, Premium SSD, Ultra SSD? How can I find out? Does the disk type match the disk type of C:? 2. As I don't think I can change the block size for the temp drive, and it is set to 4K, what will that do to performance vs me setting up my own drive set for 64K block size? 3. Should TempDB data AND log go on the temporary drive? Thanks, Chris

SQL Server on Azure Virtual Machines
{count} votes

2 answers

Sort by: Most helpful
  1. Guillaume 81 Reputation points
    2021-02-22T16:08:05.617+00:00

    Regarding the hardware behind it, this is voluntarily not documented by Microsoft as answered elsewhere by an employee :
    "We will not provide performance metrics on temp drive has it varies with every VM size. Hence it's not been documented and since temp drive are provided to VMs, from underlying compute machine itself the read/write operations will be fast. However it can't be expanded or increased based on requirement. You may have to increase the size of VMs if you need more temp drive space and if that temporary files are important, then you need to go for premium disks as it provide persistent data."

    I would be against storing logs on it as you could loose them and they can matter a lot depending of the situation.

    It seems that putting TempDB on the temp drive is recommended : https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices
    "Place TempDB on the local SSD D:\ drive for mission critical SQL Server workloads (after choosing correct VM size)." (D: being the temp drive)

    1 person found this answer helpful.

  2. John Heimiller 5 Reputation points
    2023-09-29T15:45:29.3466667+00:00

    Sorry posting to an old thread...but just saying this in case someone else hits this. You can store tempdb files on azure temp disk (D:) on azure VMs. BUT, put them in root and dont path them. when it clears the paths go too and SQL service wont start because it cant find the paths. So put them in D:\ root.

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.