Exam DP-300 topic 1 question 42 discussion

According this article: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-storage?view=azuresql "For the log drive ... If submillisecond storage latency is required, use Azure ultra disk" - so the answer is correct "Place tempdb on the local ephemeral SSD (default D:\) drive for most SQL Server workloads" - thus the answer should be perhaps "local" ?

I would say the requirement "• Minimize the impact on IO throughput of the virtual machine." rules out usage of Local disks entirely. If you use Local disk for logs or tempdb you definetly affect IO of the virtual machine.

• Use the D: drive (the locally attached SSD) for TempDB files because TempDB is recreated upon server restart, so there is no risk of data loss https://learn.microsoft.com/en-us/training/modules/configure-sql-server-resources-optimal-performance/2-explain-how-optimize-azure-storage-virtual-machines

I agree with Licna - Ulta Disk for logs is straightforward , and in the strictest sense of the questions terms , local disk will provide lowest latency - there are no other requirements

Using a local disk for TempDB can minimize the impact on IO throughput of the virtual machine by offloading the I/O operations of TempDB from the primary managed disk to the local disk. Dedicated I/O Path: Local disks typically have a separate I/O path from the managed disks (such as Premium SSDs or Standard SSDs) attached to the VM. By using the local disk for TempDB, you can leverage this dedicated path, reducing the load on the managed disk I/O. High IOPS and Low Latency: Local disks provide high IOPS and low latency because they are directly attached to the VM's physical host. This high performance is particularly beneficial for TempDB, which handles a lot of temporary data and requires fast read/write operations.

TempDB: This database is heavily used for temporary objects and should be on the fastest storage available to minimize latency. In this case, the best choice would typically be either Premium SSDs or Ultra Disks, depending on the performance requirements. Ultra Disks offer the highest throughput and lowest latency, but they are also more expensive. Transaction Logs: The transaction log records all transactions and the database modifications made by each transaction. The performance of transaction logs can be critical for write-intensive applications. Therefore, low latency is very important. Premium SSDs are generally a good choice for transaction logs because they provide a balance between cost and performance. However, for workloads that require the lowest latency possible, Ultra Disks would be the preferred choice.

Tempdb should be local.

https://learn.microsoft.com/en-us/training/modules/deploy-iaas-solutions-with-azure-sql/4-explore-performance-and-security The best practices for SQL Server on Azure recommend using Premium Disks pooled for increased IOPs and storage capacity. Data files should be stored in their own pool with read-caching on the Azure disks. Transaction log files won't benefit from this caching, so those files should go into their own pool without caching. TempDB can optionally go into its own pool, or using the VM’s temporary disk, which offers low latency since it's physically attached to the physical server where the VMs are running. Properly configured Premium SSD will see latency in single digit milliseconds. For mission critical workloads that require latency lower than that, you should consider Ultra SSD.

correct If possible, use Write Acceleration over ultra disks for the transaction log disk. For VMs that don't support Write Acceleration but require low latency to the transaction log, use Azure ultra disks.

Tempdb should be local. Not sure about the other one, but I guess if we are minimising latency, it must be Ultra Disk?

which one is correct option? I think, Tempdb should be local and transaction log should be premium

correct

Yes I agree, the tempdb should be on a local drive