Best Practices for SQL Server on Azure VMs
Implementing best practices for SQL Server on Azure VMs helps optimize performance and efficiency. Considerations include VM sizing, storage, network bandwidth, and application requirements. Recommendations for disk placement, premium storage usage, and data replication contribute to a well-managed SQL Server environment.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Section 6: SQL Server on IaaS Best Practices Lesson1: Best Practices SQL Server IaaS Best Practices 1
Different Considerations for SQL Server on Azure VM Sizing considerations Storage and IO considerations Network considerations Application considerations 2
Sizing For Standard storage: (do not choose this if performance is critical) Use minimum Standard A2 VM for SQL server Considerations For premium storage: SQL Server Enterprise Edition: DS3 or higher SQL Server Std or web Edition: DS2 or higher Network bandwidth is also limited by VM size Max data disks and IO throughput dependent on VM size 3
SQL Server Enterprise Edition: DS3 or higher SQL Server Std or wed Edition: DS2 or higher Sizing Considerations Use Premium Storage Network bandwidth is also limited by VM size Max data disks and IO throughput dependent on VM size 4
Storage account and the SQL server VM are in the same datacenter to reduce transfer delays Storage considerations Place all the data disks on the same storage account for faster recovery Storage Accounts Disable Geo-Replication on storage accounts 5
Azure VM creates 2 different disks during VM creation Disks and Performance considerations OS Disk C:\ Drive Temporary Disk D:\ Drive You can attach multiple data disks as needed up to the maximum capacity the VM can offer 6
OS Disk (C:\) OS Disk is a bootable VHD stored as a Page blob on the storage account that you can boot and mount as OS drive This disk is optimized for the OS IO patterns and not recommended to put any heavily used databases Default caching policy on OS disk in Read\Write Always use Data disks instead of OS Disks to store database files 7
Temporary Disk (D:\) Is the scratchpad disk on the VM This disk is always created the VM is restarted All the contents in the disk are lost and is not a persistent disk Not a good candidate to store data and log files Starting D series VM s, D:\ drive is an SSD based disk A good candidate to store tempdb files or Buffer pool extensions (SQL 2014) If your VM supports premium storage, Make sure tempdb disk has Read caching enabled 8
Data Disks (Persistent storage) Additional disks which you can add to your VM depending on the VM series and edition The data disks could be attached from the standard storage or the Premium storage. Premium storage disks are SSD based and are more IO performant to satisfy SQL Server workloads We recommend to put all the database files on Data disks 9
Standards disks are based on HDDs Data Disks Standard Storage Considerations Consider Warm up effect on Data disks, Normally 20 mins after inactivity Every data disk is limited to a maximum of 500 IOPS. To drive more IOPS, add more data disks and perform disk striping Disable Disk Caching on Data disks when using standard storage 10
Premium disks are based on SSDs Data Disks Premium Storage Considerations Use minimum 2 P30 Disks where one disk contains log files and other disk contains data and tempdb If you require more IOPS or bandwidth, use Disk Striping to increase IO b/w Enable read caching on the data disks hosting data files and TempDB only Disable caching on data disks serving log files or write intensive data files 11
For Win 8/Svr 2012, Use Storage spaces Disk Striping considerations Set stripe size to 64KB for OLTP and 256KB for Data warehouses. Use Powershell script to create the storage pool For Windows 2008 R2 or earlier Use Dynamic disks or OS Striped volumes instead Determine the number of disks associated with each storage pool based on your load Use different storage pool for log files and data files when possible 12
Perform Quick format of the data disk when formatting in Windows Disk Management Disk Formatting considerations Windows default File Allocation Unit is 4K Change the formatting FAU size to 64K for SQL Server data disks 13
Create a single striped volume using storage spaces leveraging multiple disks and place database and log files on this volume Database File Placement Create separate volumes, each composed of different data disks to achieve specific IO performance and place your data and log files accordingly 14
Compression reduces the size of the table Enable Page Compression Use either Row or Page compression In SQL 2014, leverage the Clustered Columnstore index and Columnstore Archive technologies Compressed data can minimize IO and improve performance Compression might increase the CPU consumption on the database server. 15
Set the right Windows Policy for Lock pages in memory for SQL Service account Enable Locked Pages in Memory It prevents the system from paging the data to virtual memory on the disk Buffer pool cannot be paged out by Windows Reduces IO and paging activities 16
Instant file initialization to reduce the time that is required for initial file allocation Instant File Initialization Permission has to be granted to the SQL service account for Perform volume maintenance tasks in Local security policy Applicable only for data files and not log files There is a security risk that the data can be accessed even after the data in the file is deleted until the file is overwritten 17
Try to prestage data files and log file to prevent autogrowth AutoGrow AutoShrink Autogrowth should be available as a safety option Try to set Autogrowth value to MB instead of Percentage Do not set Autogrow to 1024MB or 4096MB for transaction logs Disable Autoshrink- Can cause overhead 18
VLFs are created inside your transaction log files Virtual Log Files Too many VLFs can impact performance VLF creation is achieved according to the following Growth in MB <64 >64 and < 1024 >1024 No of VLF 4 8 16 In order to reduce to VLFs, Shrink and regrow the log in larger chunks 19
Host virtual machines in the same cloud service to enable direct communication between virtual machines via internal IP addresses Network Considerations Use Windows Azure Virtual Network for virtual machines that reside in different cloud services Load balance multiple virtual machines in the same cloud service via public virtual IP addresses There is a network overhead for Chatty applications to connect to Azure VM. Try to put the application and SQL Server on the same VM if possible. 20
Enable CPU Power option to High Performance instead of Balanced Other SQL Server Considerations Enable Trace flags 2371, 1118, 4199 Enable Optimize for Adhoc workloads setting Configure tempdb datafiles as per the algorithm and fixed sizes Uninstall any unused components on the server Enable Backup compression For Warehouses, Enable trace flag 610 21
Lesson Knowledge Check Question: Which process increases the overall IO throughput on Azure Data disks Answer: Disk Striping (Storage spaces in Windows 2012 and higher; Striped Volumes on Windows 2008 R2 Question: How does compression improve IO? Answer: Page\Columnstore compression reduces the amount of IO footprint for the data and this in turn improves the overall IO performance requiring less IO operations to be performed for any IO Question: What are some of the considerations for storage accounts for SQL Server ? Answer: Need to be in the same datacenter as the VM, Disable Geo replication, Place all data disks on the same storage account for faster recovery 23
