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.
我们使用VM在Azure云中设置SQL服务器。当我们确定数据/日志/ tempdb的最佳设置时,我们遇到了很多博客帖子,建议将tempdb放在Azure提供的Temporary Storage驱动器上。然而,更深入的研究揭示了微软的这些信息,据说不应该这样做。
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?
任何人都可以提供一个当前的结束答案,我们是否应该将tempdb放在临时存储器上?
有没有人对此事有明确的表现?
如果将tempdb放在临时存储上,可能会产生哪些副作用?
3 个解决方案
#1
6
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
由于最初的建议是将tempdb放在D:驱动器上,因此存在一些混淆。这不再是真的。有关最新信息,我建议您阅读此处的“Windows Azure虚拟机中的SQL Server性能指南”白皮书:http://msdn.microsoft.com/en-us/library/windowsazure/dn248436.aspx
Here's an extract with the TempDB section:
这是TempDB部分的摘录:
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.
如Windows Azure虚拟机磁盘和缓存设置一节中所述,我们建议您将tempDB放在操作系统磁盘或数据磁盘上而不是临时磁盘(D :)上。以下是基于我们使用SQL Server测试工作负载进行内部测试的建议的三个主要原因。
• 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.
•性能差异:在我们的测试中,我们注意到您可以获得与D:相同级别的性能,如果不是来自操作系统或单个数据磁盘的IOPS。但是,D:驱动器的性能不能保证与操作系统或数据磁盘一样可预测。这是因为D:驱动器的大小和从中获得的性能取决于您使用的虚拟机的大小。
• 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.
•在虚拟机停机时配置情况:如果虚拟机关闭(由于计划内或计划外原因),为了让SQL Server在D:驱动器下重新创建tempDB,启动SQL Server服务的服务帐户需要拥有本地管理员权限。此外,本地SQL部署的常见做法是将数据库和日志文件(包括tempDB)保存在单独的文件夹中,在这种情况下,需要在SQL Server启动之前创建该文件夹。对于大多数客户而言,这种额外的重新配置开销不值得回报。
• 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.
•性能瓶颈:如果将tempdb放在D:驱动器上并且应用程序工作负载大量使用tempDB,则可能会导致性能瓶颈,因为D:驱动器可能会在IOPS吞吐量方面引入约束。而是将tempDB放在操作系统或数据磁盘上以获得更大的灵活性。有关优化tempdb的配置最佳实践的详细信息,请参阅SQL Server TempDB IO最佳实践的编译。
#2
5
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):
更新:现在,临时驱动器可用作D系列Azure VM的SSD,@ CSharpRocks引用的白皮书略显过时。有关最新建议,请参阅以下两篇文章(截至2014年年中至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)
Azure虚拟机中SQL Server的性能最佳实践(2015年4月)
使用Azure VM中的SSD存储SQL Server TempDB和缓冲池扩展(2014年9月)
The articles above explicitly mention placing tempdb and Buffer Pool Extensions on D:\. Excerpt:
上面的文章明确提到将tempdb和Buffer Pool Extensions放在D:\上。摘抄:
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.
仅在使用D系列虚拟机(VM)时在D驱动器上存储tempdb和/或缓冲池扩展。与其他VM系列不同,D系列VM中的D驱动器是基于SSD的。这可以改善大量使用临时对象或具有不适合内存的工作集的工作负载的性能。
#3
1
For 2017:
I have a long running stored procedure that is tempdb intensive. Setting up Tempdb on the OS drive, as Azure configured it by default, on a DSv2 machine using SDD disks, the query ran at about 1 and a half minutes.
我有一个长期运行的存储过程,它是tempdb密集型的。在OS驱动器上设置Tempdb,就像Azure默认配置它一样,在使用SDD磁盘的DSv2计算机上,查询运行大约1分半钟。
Moving the Tempdb to the temporary storage (and doing nothing else) changed the query to run in 57 seconds, so about a 33% improvement in performance. The query was run repeatedly in both cases and the timing was (give or take) consistently at those numbers.
将Tempdb移动到临时存储(并且不做任何其他操作)会将查询更改为在57秒内运行,因此性能提高了33%。在两种情况下都重复运行查询,并且这些数字的时间一致(给予或接受)。
Putting the TempDB on temporary storage requires special consideration in terms of starting SQL server. There are two approaches. One is to point the files to the root of D and give the SQL server process local administration permissions. This is the scenario you want to consider if you already have something else starting the SQL Server process rather than just an automatic service startup. Otherwise it raises security eyebrows.
将TempDB置于临时存储上需要特别考虑启动SQL Server。有两种方法。一种是将文件指向D的根目录,并为SQL服务器提供本地管理权限。如果您已经有其他东西启动SQL Server进程而不仅仅是自动服务启动,那么这是您要考虑的方案。否则会引起安全人士的注意。
The second option is to set the SQL Server service to manual startup, and then write a powershell script to launch it, and put that powershell script on a scheduled task to run at startup. The powershell script would first ensure that the directory exists on the temporary storage before starting SQL server.
第二个选项是将SQL Server服务设置为手动启动,然后编写powershell脚本以启动它,并将该PowerShell脚本放在计划任务上以在启动时运行。 powershell脚本首先确保在启动SQL Server之前该目录存在于临时存储上。
It has already been linked to in another answer, but this document is updated as of 2017 and it does not formally recommend this kind of setup for TempDb, rather just moving it off of the OS partition. However, it says:
它已经在另一个答案中链接,但是这个文档从2017年开始更新,并没有正式推荐TempDb的这种设置,而只是将其从操作系统分区中移除。但是,它说:
If your workload makes heavy use of TempDB (e.g. for temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency.
如果您的工作负载大量使用TempDB(例如,用于临时对象或复杂连接),则将TempDB存储在D驱动器上可能会导致更高的TempDB吞吐量和更低的TempDB延迟。
My performance experience confirmed that last line.
我的表现经历证实了最后一行。
#1
6
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
由于最初的建议是将tempdb放在D:驱动器上,因此存在一些混淆。这不再是真的。有关最新信息,我建议您阅读此处的“Windows Azure虚拟机中的SQL Server性能指南”白皮书:http://msdn.microsoft.com/en-us/library/windowsazure/dn248436.aspx
Here's an extract with the TempDB section:
这是TempDB部分的摘录:
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.
如Windows Azure虚拟机磁盘和缓存设置一节中所述,我们建议您将tempDB放在操作系统磁盘或数据磁盘上而不是临时磁盘(D :)上。以下是基于我们使用SQL Server测试工作负载进行内部测试的建议的三个主要原因。
• 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.
•性能差异:在我们的测试中,我们注意到您可以获得与D:相同级别的性能,如果不是来自操作系统或单个数据磁盘的IOPS。但是,D:驱动器的性能不能保证与操作系统或数据磁盘一样可预测。这是因为D:驱动器的大小和从中获得的性能取决于您使用的虚拟机的大小。
• 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.
•在虚拟机停机时配置情况:如果虚拟机关闭(由于计划内或计划外原因),为了让SQL Server在D:驱动器下重新创建tempDB,启动SQL Server服务的服务帐户需要拥有本地管理员权限。此外,本地SQL部署的常见做法是将数据库和日志文件(包括tempDB)保存在单独的文件夹中,在这种情况下,需要在SQL Server启动之前创建该文件夹。对于大多数客户而言,这种额外的重新配置开销不值得回报。
• 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.
•性能瓶颈:如果将tempdb放在D:驱动器上并且应用程序工作负载大量使用tempDB,则可能会导致性能瓶颈,因为D:驱动器可能会在IOPS吞吐量方面引入约束。而是将tempDB放在操作系统或数据磁盘上以获得更大的灵活性。有关优化tempdb的配置最佳实践的详细信息,请参阅SQL Server TempDB IO最佳实践的编译。
#2
5
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):
更新:现在,临时驱动器可用作D系列Azure VM的SSD,@ CSharpRocks引用的白皮书略显过时。有关最新建议,请参阅以下两篇文章(截至2014年年中至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)
Azure虚拟机中SQL Server的性能最佳实践(2015年4月)
使用Azure VM中的SSD存储SQL Server TempDB和缓冲池扩展(2014年9月)
The articles above explicitly mention placing tempdb and Buffer Pool Extensions on D:\. Excerpt:
上面的文章明确提到将tempdb和Buffer Pool Extensions放在D:\上。摘抄:
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.
仅在使用D系列虚拟机(VM)时在D驱动器上存储tempdb和/或缓冲池扩展。与其他VM系列不同,D系列VM中的D驱动器是基于SSD的。这可以改善大量使用临时对象或具有不适合内存的工作集的工作负载的性能。
#3
1
For 2017:
I have a long running stored procedure that is tempdb intensive. Setting up Tempdb on the OS drive, as Azure configured it by default, on a DSv2 machine using SDD disks, the query ran at about 1 and a half minutes.
我有一个长期运行的存储过程,它是tempdb密集型的。在OS驱动器上设置Tempdb,就像Azure默认配置它一样,在使用SDD磁盘的DSv2计算机上,查询运行大约1分半钟。
Moving the Tempdb to the temporary storage (and doing nothing else) changed the query to run in 57 seconds, so about a 33% improvement in performance. The query was run repeatedly in both cases and the timing was (give or take) consistently at those numbers.
将Tempdb移动到临时存储(并且不做任何其他操作)会将查询更改为在57秒内运行,因此性能提高了33%。在两种情况下都重复运行查询,并且这些数字的时间一致(给予或接受)。
Putting the TempDB on temporary storage requires special consideration in terms of starting SQL server. There are two approaches. One is to point the files to the root of D and give the SQL server process local administration permissions. This is the scenario you want to consider if you already have something else starting the SQL Server process rather than just an automatic service startup. Otherwise it raises security eyebrows.
将TempDB置于临时存储上需要特别考虑启动SQL Server。有两种方法。一种是将文件指向D的根目录,并为SQL服务器提供本地管理权限。如果您已经有其他东西启动SQL Server进程而不仅仅是自动服务启动,那么这是您要考虑的方案。否则会引起安全人士的注意。
The second option is to set the SQL Server service to manual startup, and then write a powershell script to launch it, and put that powershell script on a scheduled task to run at startup. The powershell script would first ensure that the directory exists on the temporary storage before starting SQL server.
第二个选项是将SQL Server服务设置为手动启动,然后编写powershell脚本以启动它,并将该PowerShell脚本放在计划任务上以在启动时运行。 powershell脚本首先确保在启动SQL Server之前该目录存在于临时存储上。
It has already been linked to in another answer, but this document is updated as of 2017 and it does not formally recommend this kind of setup for TempDb, rather just moving it off of the OS partition. However, it says:
它已经在另一个答案中链接,但是这个文档从2017年开始更新,并没有正式推荐TempDb的这种设置,而只是将其从操作系统分区中移除。但是,它说:
If your workload makes heavy use of TempDB (e.g. for temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency.
如果您的工作负载大量使用TempDB(例如,用于临时对象或复杂连接),则将TempDB存储在D驱动器上可能会导致更高的TempDB吞吐量和更低的TempDB延迟。
My performance experience confirmed that last line.
我的表现经历证实了最后一行。