AzureEnterprise TechSQLTech Posts Best Practices for Configuring SQL on Azure IaaS VM March 10, 20161286 views0 Share Best Practices for Configuring SQL on Azure IaaS VM Running SQL Server workloads on Azure as IAAS (Infrastructure as a service) is becoming popular. With more SQL Servers running on Azure virtual machines, there has been a need to know how to effectively and efficiently one can run them with minimal bottlenecks. In order to have the maximum performance out of your Azure VM’s, here are the list of steps to look out for: A minimum of A2 Azure VM is recommended for running SQL workloads. Ensure that the storage account and the VM are located in the same region to reduce any network latency Azure Geo-Replication should be disabled at the storage level. This ensures maximum IOPS for SQL Server to use. Do not use the “TEMP” storage for storing any SQL Server files like Log files and Data files. Disable Data Disk Caching (caching policy = None) Configure RAID or striping using multiple Data Disks for maximum Read-Write performance. Format the Disk’s in 64 KB chunks. Do not use the default “File Allocation Units” Ensure seperate disks for Log files (ldf’s) and Data files (mdf’s). This will get dedicate IOPS for both Data and log files. Less than 10 ms – very good Between 10 – 20 ms – okay Between 20 – 50 ms – slow, needs attention Greater than 50 ms – Serious I/O bottleneck You need 2 ms – 5 ms of response time for the SQL Server Log files. Enable “Page Compression” for Databases. Do not enable this feature if you have high CPU usage. Disable “Autogrowth” for Databases & Tembdb Do not keep the Database files on the default location. Move them to a different disk drive. This includes “System Databases”. SQL Server Error Log’s and Trace Files should be pointing to a different Data Disk. Enable “Lock Pages in Memory” feature. This is only applicable for SQL Server Enterprise Editions only. Se Backup jobs to be saves directly on Azure Storage Blob’s. Enable and use “Buffer Pool Extension” on dedicated premium Azure Storage. These are SSD Disks and will give you close to 400 MB/s of throughput. Configure Tempdb on SSD disks where possible. Hope this covers the basics of what steps needs to be followed in order to run SQL Server more efficiently on Azure VM’s. Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0 IG Website Twitter
Tech Posts How to Change Notification Settings on Google Drive on iOS Android & Windows By IGJune 7, 2021
Azure SQL Server Managed Instance BYOL Hybrid Licensing & Dev/Test Options Microsoft offers SQL Server Managed Instance BYOL Hybrid Licensing for your On-Premises products and discounted ...
Azure Azure SQL Managed Instance General Purpose vs Business Critical In this article, I will try to compare the Azure SQL Managed Instance General Purpose ...
Azure Azure SQL DTU to vCore Migration Steps for PAAS Databases on Cloud In this article, we are going to talk about Azure SQL DTU to vCore Migration. ...
Azure How To Change Azure SQL DB MAXDOP Settings Using T-SQL Command In this article, we are going to discuss how to change Azure SQL db MAXDOP ...