AzurePerformance Tuning Best Practices for SQL on Azure VM April 11, 201611056 views3Share By IG ShareIn this post, I am going to talk about Performance Tuning Best Practices for SQL running on an Azure VM with key considerations. Understand the characteristics of running SQL Server on a Windows Azure VM (IaaS). and what impacts performance. Additionally, learn how to monitor and troubleshoot performance of SQL Server running on Windows Azure VM. Let’s start with the best Practices for SQL on Azure VM.Note: If you buy something from our links, we might earn a commission. See our disclosure statement.Best Practices for SQL on Azure VMOLTP systems with transactional workloadsLarge number of small transactions with both Reads as well as Inserts & UpdatesTransactions are mostly similar in patternSignificant amount of reads (Varies between 60% to 80%)Random Read/Writes between 8k-64k chunks.User concurrency is High.Read my earlier post on SQL Server Performance Tuning for All Editions.Windows Azure VM CharacteristicsArchitectureLatencyVariabilityConfiguration optionsVM Configuration OptionsVirtual Machine sizeNetwork bandwidthDisk types and configurationsDisk cache settingsVM Disk OptionsDefault OS disk with persistent data and caching.This is a 127GB disk optimized for OS access patterns and Windows (or Linux) boot up time. This is not meant for either transactional or Data Warehouse (Analytical) workloads.Attach additional Persistent Data DisksYou can add up to 16 disks with 1 TB in size and 500 IOPS each.Temporary local disk drives which are a non-persistent diskIt is used for temporary data storage & OS page files. This is not meant for data storage or SQL Server files.Hosted in attached disks on every physical host. This means that it is not a part of the Azure Blob Storage and does not give you the regular SLA provided by Azure.It is cleaned up in the event of a VM Failover from one host system to another or recycling.Physical disks are shared across other VMs on the same physical machine. This means that the performance is not guaranteed and can have a large variance.*Not recommended for user or system database filesIf you agree to all the conditions mentioned above, you can still go ahead and put the TEMP DB system files on the non-persistent data drive for performance reasons. Windows Azure IO subsystemThe storage disks are implemented as a shared multi-tenant service with built-in triple redundancy, and with optional geo-redundancy across regions. Given below are the main characteristics of Azure Disk subsystem:Performance is less predictable than on-premises due to multi-tenancy and other reasons.It is a shared infrastructure, where host machines, storage services, network bandwidth are shared between subscribers.Performance can depend on which region the VM is provisioned. If your VM is at a far away distance, then it will have a direct impact on network latency.Subject to downtime and maintenance operations. Availability still stands at 99.99%.Choose between granular control & configuration for on-premise deployment vs. cost, simplicity, out of box redundancy for Azure deployments.How does VM Disk Caching work?The VM disk is cached in both inside physical host machine and also to an external Azure Page Blobs. This greatly reduces disk I/O latency by reducing the number of transactions hitting against the Azure Storage subsystem.The VM Cache is a 2-tier cache which stores recently accessed data in host RAM cache – space shared by all VMs on the machine in the form of Page Blobs. The second Tier stores less recently accessed data stored on local hard disks of the physical host machine. There is a reserved cache space for VM “OS Disk” and “Data Disks” based on the VM size.VM cache settingsRead Only: All requests cached for future reads. All writes persisted directly to Windows Azure Storage Blobs.Read Write: This is a kind of a “Read Ahead” algorithm. The reads and writes are cached for future reads. Non-write-through writes persisted to local cache first. For SQL Server, writes are persisted to WA storage because it uses Write-through It provides the lowest disk latency for light workloadsNone (disabled): This allows you to bypasses the cache. All the data are transferred to disk and persisted to the Windows Azure Storage. This method gives you the highest I/O rate for I/O intensive workloads. You also need to take “Transaction Cost” into consideration.Caching by default is disabled on the Data Disks. You can enable caching of upto 4 Read only and 4 Read write disks for data drives.Disk Caching Best Practices for SQL ServerFor the OS drive, the default caching is “Read Write” which reduces latency as mentioned before.For Data Disks, the default is “None (Disabled)” which gives you the highest throughput. For Read intensive workload, Enable “Read Only” caching for better performance.VM network performance best practicesNetwork latency is higher compared to an on-premise deployment. Reducing network round trips can greatly improve performance.Consolidate applications which have high transactions or “Chatty” Apps on the same virtual machine to reduce round-trips.Host your VM’s on the same V-NET & Cloud Service.VM’s with Always On availability groups needs to be in the same Cloud Service. Routing of Read-Only transactions to secondaries is recommended using a listener on the Public IP interface.Azure Disk Configuration Best PracticesFor Databases of <1TB storage, a Single Data Disk can be recommended with acceptable performance.For >1TB DB files and Higher IOPS requirement, use multiple data disks with the following configuration:[visualizer id=”910″]Just like SQL Server On-Premises, use Filegroups with multiple database files(MDF* & Ldf*), and place each database file on a separate data disks.For more performance, you can create a striped Disk using multiple data disks using the Windows Server storage space.Storage spaces are recommended over OS striped volumes.Spreading the I/O workload across a number of data disks benefits smaller random access patterns for OLTP operations where IOPS and bandwidth scale in a nearly linear fashion.As the I/O block size increases, for reading operations, IOPS or bandwidth doesn’t linearly scale by adding more data disks.If your workload is read intensive with analytical queries, adding more disks will not necessarily help.For write intensive workload, adding more data disks can increase performance in a nearly linear fashion. This means that you can benefit from placing each transaction log for multiple databases on a separate data disk.For large sequential, I/O block sizes write generally scale better than reads.Use Data Compression to reduce IOAs you can see, there is a significant reduction of logical and physical reads with page compression enabled, and the elapsed time. Unsurprisingly, CPU time of the query does go up with page compression as expected, because SQL Server needs to decompress the data while returning results to the query. Please note that your results will vary, depending on your workload and scenario.Use these data compression techniques for I/O intensive workloads and then decide which tables and indexes to compress. If your CPU is already highly utilized, do not implement this or upgrade the VM to a higher service tier.This is not enabled by default in Azure VM images. Reduces IO for Creating a DB Restoring, DB Adding files to a DB, Extending file size, Autogrow, etc. Add SQL service account to Perform Volume Maintenance Tasks security policy.This step will not help, if you have performance issues at the Transaction Log throughput level. Recommendations for Instant File InitializationEnsure that you Pre-size all database files appropriatelyConfigure Instant File Initialization before creating databaseConfigure Instant File Initialization first and then extend files like TEMPDB.Configure Instant File Initialization first before restoring a database on a new server or on a newly create VM.Restart SQL Server Service for the configuration manager after configuring Instant File Initialization.[visualizer id=”918″]Other Recommendations for Data Disk PerformanceSpreading Data files of a single Database into multiple storage accounts is not recommended. This is because Blobs are not written at the same time and could be out of sync. Instead, spread the data files of different databases across multiple disks to achieve higher IOPS / bandwidth performance.A storage account has a hard limit of 20000 tps (Transactions per second)Performance Counter to Capture and Analyse Database performance on an Azure VMSee my post on Free Tools for Monitoring SQL Server PerformanceLogicalDisk(*)Avg. Disk Bytes/ReadLogicalDisk(*)Avg. Disk Bytes/WriteLogicalDisk(*)Avg. Disk Queue LengthLogicalDisk(*)Avg. Disk sec/ReadLogicalDisk(*)Avg. Disk sec/WriteLogicalDisk(*)Current Disk Queue LengthLogicalDisk(*)Disk Read Bytes/secLogicalDisk(*)Disk Reads/secLogicalDisk(*)Disk Write Bytes/secLogicalDisk(*)Disk Writes/secMemoryAvailable MBytesMemoryFree System Page Table EntriesMemoryPages/secNetwork Interface(*)*Process(*)*Processor(*)% Privileged TimeProcessor(*)% Processor TimeSQLServer:Availability Replica(*)*SQLServer:Access Methods*SQLServer:Buffer Manager*SQLServer:Buffer Node*SQLServer:Databases(*)*SQLServer:Database Replica(*)*SQLServer:General Statistics*SQLServer:Latches*SQLServer:Locks(_Total)Average Wait Time (ms)SQLServer:Locks(_Total)Lock Requests/secSQLServer:Memory Manager*SQLServer:Plan Cache*SQLServer:Wait Statistics*SQLServer:SQL Statistics*SystemContext Switches/secSystemProcessor Queue LengthUse tools like PAL from Codeplex to analyze the results.I hope this was helpful. Please comment below to give feedback.ReferencesPerformance Guidance for SQL Server in Windows Azure Virtual Machines: Download it hereAzure Storage Scalability and Performance TargetsAzureAzure virtual machine disks and cache settingsAnalyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database ApplicationsBest practices and recommendations for optimizing SQL Server performance in Azure VMsAnalyzing I/O Characteristics and Sizing Storage Systems for SQL Server Database ApplicationsData Compression: Strategy, Capacity Planning and Best PracticesHow and Why to Enable Instant File InitializationI hope that you like this post on Best Practices for SQL on Azure VM. Any comments or suggestions are welcome in the comment section below. Cheers! Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0
Office 365How to Fix Deleted Reoccurring Meeting Recreating Itself on Outlook 2016 By IGOctober 14, 2017
AzureDownload Backup and Restore a Azure SQL Database Locally for TestingAzure SQL Database is Microsoft’s Database as a service on the cloud. While your production ...
AzureStep by Step Deployment for SQL Analysis Services PAAS on Azure CloudSQL Server Analysis Services (Both Multi-Dimensional and Tabular) only supports Windows Authentication. However, Analysis Services ...
AzureMove & Load Data to Azure SQL DataWarehouse using Data Platform StudioThere are multiple ways to populate data into Azure SQL DataWarehouse PAAS (Platform as a ...
SQLHow to Upgrade to SQL Server 2016 from Earlier and Older VersionsHow to Upgrade to SQL Server 2016 from Earlier and Older Versions SQL Server 2016 ...