SQL Server on Azure Virtual Machines (SQL VM) brings cloud agility, elasticity and scalability benefits to SQL Server workloads at the lowest TCO [1]. SQL VM offers full control on the OS, VM size, storage subsystem and the level of manageability needed for your workload. If you choose to self-install SQL Server on Azure Virtual Machines instead of choosing a preconfigured SQL Server image from Azure Marketplace, you should register with SQL VM Resource Provider (SQL VM RP) for simplified license management and free SQL Server manageability benefits like automated backup , automated High Availability with Always On AG and automated patching.   Today, we are excited to announce Performance Optimized Storage Configuration capabilities for the VM’s registered with SQL VM RP. This feature automates storage configuration according to performance best practices for SQL Server on Azure virtual machines through Azure Portal or Azure Quick start Templates when creating a SQL VM. Automated performance best practices include separating Data and Log files, cache configuration for Premium SSD, support for Temp DB on local disk, support for Ultra Disks to host data, log or Temp DB files and database engine only images. In this article, we will discuss each automated performance best practice in detail.   Automate Premium Disk Cache Configurations Azure VMs have a multi-tier caching technology called Blob Cache when used with Premium Disks. Blob Cache uses a combination of the Virtual Machine RAM and local SSD for caching. Disk caching for Premium SSD can be ReadOnly, ReadWrite or None. ReadWrite caching should not be used to host SQL Server files as SQL Server does not support data consistency with the ReadWrite cache. None cache configuration should be used for the disks hosting SQL Server Log file as the log file is written sequentially and does not benefit from ReadOnly caching. Additionally, writes waste capacity of the ReadOnly blob cache and latencies slightly increase if writes go through ReadOnly blob cache layers.   ReadOnly caching is highly beneficial for SQL Server data files that are stored on Premium Storage. ReadOnly caching brings low Read latency and very high Read IOPS and Throughput as, Reads performed from cache, which is on the VM memory and local SSD, are much faster than reads from the data disk, which is on the Azure blob storage. Premium Storage does not count the Reads served from cache, towards the disk IOPS and Throughput. Therefore, your application is able to achieve higher total IOPS and Throughput.   Performance Optimized Storage configuration supports both Premium SSD and Ultra Disks for SQL Server Data and Log files and automatically sets the caching configuration according to the best practices. If Premium SSD is chosen to host SQL Server data or Temp DB files, then ReadOnly caching will be enabled for all the disks; if Premium SSD is used for the Log files, then caching will be disabled. Cache configuration is only applicable to Premium SSD as Ultra Disk does not support caching.   Separate Data and Log Files Separating data and log files to different drives has been a well-known performance best practice for SQL Server for a long time. It is still valid on Azure because Data and log files has different caching requirements. The impact of separating data and log files to different storage pools with the right cache configuration is around 27% when tested with HammerDB TPC-C [2] on a small scale database. The Impact will be higher with large data sizes and for read heavy workloads.   We used a small VM size, Ds12_v2 with 2 P30 disks to test the performance impact of single or separate storage pools for SQL Server data and log files. In the first run, both disks had ReadOnly cache enabled, stripped on a single storage pool; Data, Log and Temp DB files are hosted on that storage pool. When HammerDB TPC-C with 200 warehouses is run on this configuration, TPM (transaction per minute) ranged between 274,446 to 423,079 for virtual users 16 to 96 (Table-1). Table 1- DS12_v2, Single Storage pool with 2 P30 Premium Disks for Data and Log   For the second run, same test is repeated with 1 P30 disk (ReadOnly cache enabled) for Data and 1 P30 disk (none cache) for log and throughput increased significantly. With data and log on separate disks, the same client could drive up to 539,091 TPM (Table -2). Table 2- Ds12_v2, Data and Log is on separate P30 disks   Performance Optimized Storage configuration automates separating Data and Log files when storage is configured through Azure Portal or Azure Quick start Templates. Hosting Data and Log files on the same drive is supported only for General Purpose workloads, separate drives are the default configuration for OLTP and DW workloads as seen in Figure-1. Figure 1 – Performance Optimized Storage Configuration for SQL VM on Azure Portal   Support Temp DB on Local Disks Performance of Temp DB is critical for SQL Server workloads as SQL Server uses Temp DB to store intermediate results as part of query execution. Local Storage (D: drive) available to Azure VM’s offers very low response times and included in the cost of the VM. Hosting Temp DB on the Local Storage has significant price/performance advantages if the size and the storage scale limits of the VM is enough for the workload. You need to measure the IO bandwidth needed to meet the demands of your workload and test to find the required storage capacity for the Temp DB. If the local storage capacity on the VM is not enough for your workload’s Temp DB requirements than consider hosting Temp DB on Premium SSD or Ultra Disks to get very low response times. Performance optimized storage configuration automates hosting Temp DB on the Local Storage of the VM. You do not need to worry about failovers or VM restarts any more, SQL VM RP automates re-configuration required after a restart (to bring the temporary drive back available to the VM). Hosting Temp DB on the local disk is the default configuration for OLTP and DW workloads, it is also supported for General Purpose workloads as shows in Figure-2.   Figure 2- Performance Optimized Storage Configuration supports Local SSD for Temp DB   Support for Ultra Disks Azure ultra-disks deliver high throughput, high IOPS, and consistent low latency disk storage for Azure VMs. Use ultra-disks when storage latencies become the bottleneck to increase the throughput. Premium Disks has great price/performance advantages with ReadOnly caching and monthly low-end storage cost. If your workload requires storage response times at micro second level, then you should use ultra-disk as it offers consistent sub millisecond read and write latencies at all IOPS levels (up to 160,000 IOPS). Start with leveraging ultra-disks to optimize storage performance for the Log file or Temp DB files (if local disk on the VM does not have enough capacity). Additionally, for read heavy TPC-E type workloads with limited data modifications, hosting data files on ultra-disks can significantly help to increase throughput.   Performance optimized storage configuration supports using ultra-disks to host data, log and Temp DB files through Azure Portal. Alternatively, use the new Azure QuickStart template to deploy a SQL VM with log file on ultra-disk.   Database Engine only images SQL Server images on Azure Marketplace comes with full and default installation of SQL Server. We are launching Database Engine only images for SQL Server 2016 SP1, SQL Server 2016 SP2, and SQL Server 2017 Enterprise and Standard editions today. You can use those images to create a SQL VM through Azure Portal, PowerShell or ARM template deployments.   Leverage free manageability to simplify SQL Server administration and Performance Optimized Storage configuration to boost SQL Server performance on Azure VMs by creating a new SQL VM through Azure Portal or registering with SQL VM RP today. If you have a question or would like to make a suggestion, you can contact us through UserVoice. We look forward to hearing from you!   [1] Total cost of ownership (TCO) is up to five times lower than with SQL Server on Amazon Web Services EC2. Cost difference includes Azure Hybrid Benefit for Windows Server (exclusive to Azure) and a three-year offer of free Extended Security Updates in Azure Virtual Machines for no additional costs. Prices are as of October 24, 2018 and subject to change. Actual regional pricing and program discounts may apply. Actual savings may vary based on region, instance size, and performance tier. Savings exclude Software Assurance costs, which may vary based on Volume Licensing agreement. Contact your sales representative for more information. Learn more.   [2] HammerDB provides an implementation of the TPC-C benchmark. HammerDB TPC-C is derived from TPC-C and as such is not comparable to published TPC-C results, and the HammerDB TPC-C results do not fully comply with TPC-C.