SQL with Manoj

 
I was setting up an environment on Azure Premium Storage (G-Series) server for my Project, and was not getting the performance I was expecting. The comparison was against an existing Database Server setup on an On-Premise VM.

Microsoft Azure Premium Storage delivers high-performance, low-latency disk support for VMs running I/O-intensive workloads. Uses SSD (Solid State Drives) to store your data and Caching capability to provides highest throughput and low latency. You can configure disk caching policy on the Premium Storage disks as ReadOnly, ReadWrite or None, based upon your workloads, and type of data processing.

The highest Premium Storage Disk type, P30 provides you Disk size of 1TB, with 5000 IOPS, which can provide throughput of 200 MBPS.
 

Here are some of the Best Practices [suggested by Microsoft] to ensure that your SQL Server instance is optimized upto its maximum on an Azure Virtual Machine:

1. Use Premium Storage. Standard storage is only recommended for dev/test.

2. Keep the storage account and SQL Server VM in the same region.

3. Format the Data disks, and use NTFS 64-KB allocation unit size for data and log files.

4. Stripe multiple Azure data disks to get MAX IOPS and throughput.

5. Avoid using operating system or temporary disks for database storage or logging.

6. Enable read caching on the disk(s) hosting the data files and TempDB.

7. Do not enable caching on disk(s) hosting the log file.

8. Setup SQL Server tempDB on D: Drive which is a SSD (Solid State Disk) in providing MAX throughput on handling heavier workloads.

9. Enable SQL Server database page compression.

10. Enable instant file initialization for data files, to reduce the time that is required for initial file allocation. Grant the SQL Server (MSSQLSERVER) service account with SE_MANAGE_VOLUME_NAME and add it to the Perform Volume Maintenance Tasks security policy. Database File Initialization.

11. Disable autoshrink and Limit autogrow on the database.

12. Move all databases and error log & trace file directories to data disks, including system databases.

13. Enable the Lock Pages in Memory Option (Windows level). This security setting determines which accounts can use a process to keep data in physical memory, which prevents the system from paging the data to virtual memory on disk. Exercising this privilege could significantly affect system performance by decreasing the amount of available random access memory (RAM).

14. Set MAX SQL Server memory to 64GB.

15. Run a full Index REBUILD (without REORGANISE) and UPDATE STATS with FULL SCAN option on all tables in the database.

16. And last and not the least, tune your Queries. Badly created Queries and Indexes won’t run on highly optimized systems 🙂