Azure |
|
Know your VM SKU and how that effects performance!! |
|
Ran into a performance issue where the SQL instance was encountering Non-Yielding Schedulers. In the past, that was usually a bug in SQL or AV installed on SQL causing the exception in the Memory Address space and then opening a ticket with MS. So for this issue, we were using an MClass VM. Pretty heavy CPU usage but the main issue was that IO throughput was being throttled as the VM SKU is spec'd for 250 MBPS and per MS we were hitting over 500 mbps. The Azure portal gives you some pretty quick dashboard capabilities to look into this issue. Using the counters, Data Disk Queue Depth, Data Disk Read Bytes/Sec and Data Disk Write Bytes/Sec and setting the Aggregation to Max we can see that when Data Disk Read Bytes/Sec hits 250 mbps the VM is throttled and subsequently Disk Queue goes up, meaning IO requests are stacking up and for my specific issue, non-yielding schedulers happened causing all kinds of issues. In the below chart you can see where we scaled up the VM to a different SKU and now are hitting above 500 mbps.