Once storage requirements are known, select a virtual machine that supports the necessary IOPS and throughput with the appropriate memory-to-vCore ratio.Ĭhoose a VM size with enough storage scalability for your workload and a mixture of disks (usually in a storage pool) that meet the capacity and performance requirements of your business. To find the most effective configuration for SQL Server workloads on an Azure VM, start by measuring the storage performance of your business application. To compare the storage checklist with the others, see the comprehensive Performance best practices checklist. SQL Server VMs deployed through Azure Marketplace come with data disks formatted with a block size and interleave for the storage pool set to 64 KB.
Always stop the SQL Server service before changing the cache settings of your disk.Do not enable read/write caching on disks that contain SQL Server data or log files.Set host caching to none for log file disks.Set host caching to read-only for data file disks.Stripe multiple Azure data disks using Storage Spaces to increase I/O bandwidth up to the target virtual machine's IOPS and throughput limits.This configuration will need custom monitoring and action to ensure the local ephemeral SSD (default D:\) drive is available all the time as any failures of this drive will not trigger action from FCI. If the FCI workload is heavily dependent on tempdb disk performance, then as an advanced configuration place tempdb on the local ephemeral SSD (default D:\) drive which is not part of FCI storage.For FCI place tempdb on the shared storage.See Data file caching policies for more information. If the capacity of the local drive is not enough for tempdb, consider sizing up the VM.Place tempdb on the local ephemeral SSD (default D:\) drive for most SQL Server workloads that are not part of Failover Cluster Instance (FCI) after choosing the optimal VM size.For M-series virtual machine deployments consider write accelerator over using Azure ultra disks.If submillisecond storage latency is required, use Azure ultra disks for the transaction log.For the log drive plan for capacity and test performance versus cost while evaluating the premium P30 - P80 disks.For the data drive, only use premium P30 and P40 or smaller disks to ensure the availability of cache support.Place data, log, and tempdb files on separate drives.To optimize storage performance, plan for highest uncached IOPS available and use data caching as a performance feature for data reads while avoiding virtual machine and disks capping.Monitor the application and determine storage bandwidth and latency requirements for SQL Server data, log, and tempdb files before choosing the disk type.Review the following checklist for a brief overview of the storage best practices that the rest of the article covers in greater detail: To learn more, see the other articles in this series: Checklist, VM size, Security, HADR configuration, and Collect baseline. Consider your performance needs, costs, and workload patterns as you evaluate these recommendations. If your workload is less demanding, you might not require every recommended optimization.
P1 LEVEL LOGSHEET CD TRAINING LEVEL SERIES
This performance best practices series is focused on getting the best performance for SQL Server on Azure Virtual Machines. There is typically a trade-off between optimizing for costs and optimizing for performance. This article provides storage best practices and guidelines to optimize performance for your SQL Server on Azure Virtual Machines (VMs).
0 kommentar(er)
