Context
Daily loads take the entire night and spill over into the morning blocking user reporting. MSSQL servers hosted on Azure (IAAS) are sized mainly by CPU. Despite an initial performance campaign that brought improvements, far too many load processes remain slow. New projects are postponed because they are too impactful on current chains
Challenge
Reduce load times to allow new projects to come to fruition.
Technologies used
DBMS: MS SQL Server 2016 EE • ETL: SSIS and stored procedures • Sources: SQL Server System and files • Reporting: SSRS
Methodology and implementation
In this MS SQL Server performance audit on Azure IAAS case, the client wanted to review the disk performance of their Azure E32s.v3 VM that hosted their MSSQL 2016 production instance for their datawarehouse. The client had RedGate SQL Monitor and therefore a history of performance traces centralized in an MSSQL database.
**Disk performance analysis**
Using RedGate SqlMonitor traces with a specific dashboard on disk I/O consumption, we were able to detect a fairly low bandwidth limitation fixed at 512MB/s.
Each point on the graph is a 30s reading of the average disk bandwidth (in MB/s) of each disk.
Other findings from direct reading showed that when a disk X consumed part of the bandwidth (200MB/s for example), the other disks could not exceed 512MB-BPx (312MB/s for our example).
We therefore requested a new E32ds.v4 VM with as much vCPU and RAM as our E32s.v3 but having different bandwidth limits according to Azure documentation. With this new VM and the old one, we performed an iometer benchmark.
**Conclusion**
You must choose your VM carefully to get good SQL Server performance on Azure IAAS (or any other cloud!). A datawarehouse will often consume a lot of disk bandwidth, so you must pay particular attention to VM bandwidth limitations on the cloud (AWS, Azure or others...). You often need powerful VMs (i.e. with many vCPUs) to claim decent bandwidth. Added to this are MS SQL licensing issues. A VM with many vCPUs will generate a very high licensing cost, thus slowing down the upgrade. Some clouds (AWS or Azure to my knowledge) have understood this well and offer (for the same cost) VMs limited in vCPU but not less in bandwidth.
Results
After a quick study of performance traces and especially disk traces, the audit showed the disk bandwidth limitation on the VM hosting the SQL Server database. The audit will recommend changing the VM type from E32s.v3 (limited to 512MB/s for all disks) to an E32ds.v4 (limits of 1.9GB/s for local SSD disk and 768MB/s for external disks) to free up the MSSQL instance.
Visualizations
Performance benchmark between E32s.v3 and E32ds.v4