Azure SQL IaaS – TempDB Performance on SSD vs Premium Disk

When it comes to deploying SQL Server onto an Azure IaaS VM, it is sometimes difficult to know the best option to deploy the SQL Server tempdb database for your workload.

In many of the SQL templates on the marketplace it is often deployed to the C:\ by default after which you should redeploy the database to either D:\ (local SSD) or to an attached premium disk (P10, P20, P30).  The Microsoft SQL on IaaS Performance Best Practice article states both are possibilities under certain circumstances, however it does not provide empirical workload evidence as which to use when.

For those who have not seen the article – read here – https://azure.microsoft.com/en-us/documentation/articles/virtual-machines-windows-sql-performance/

The key comment of interest is this…

For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. 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.

…and this…

So I thought lets test a OLTP type SQL workload!

AND SO – lets do some testing to validate this puppy!

Continue reading

Advertisements