SQL Performance in the Cloud

When using SQL Server, proper setup of the Database, Log Files, Memory and Disk I/O play a key part in the overall SQL Server performance. The same rings true when running SQL Server in the cloud, whether it be Azure, AWS or other. What complicates things is the myriad of choices you have for the underlying virtual infrastructure in the cloud. All choices have an impact and the more Processors, Memory or Disk I/O you contract, the more it costs you. You have to pay to play!

Confusion on the cloud…

I was called in to monitor a Microsoft Dynamics CRM SQL Server that was running in the cloud. It was being reported that an external integration was failing and losing data while communicating to the CRM SQL Server. In the initial discovery with the client, it was conveyed to me that the CRM SQL Server was in the “Cloud” and that the cloud has unlimited resources so the issue couldn’t be the underlying virtual infrastructure. After further discovery, it was revealed that there were several servers that made up the external integration to the CRM SQL Server. We promptly implemented a monitoring system looking at CPU Utilization, Memory Utilization, Disk I/O, SQL Locking, SQL Blocking, SQL Deadlocks, etc. across the entire integration solution (including the entire Microsoft Dynamics CRM solution). This method gave us a “big picture” which allowed us to see issues across the entire solution and what components of that solution were affected when the issue raised its ugly head. The monitoring showed deadlocks that occurred during the middle of the day when the “perfect storm” occurred. The perfect storm was the scheduled integration kicking off for a data import while someone else ran a report retrieving data from multiple tables where the integration data resided. Add that to the client having multiple databases on the same disk array and that database being used heavily for data analytics (not scheduled but run manually). This issue on the surface looked random but with the proper monitoring, all of the above was uncovered and made the issue perfectly clear. They were over-driving the I/O capabilities of the disk subsystem, even in the cloud.

Limits…

After validating that poorly written SQL code was not the cause of excessive I/O (which we see a lot) we reviewed the findings with the client. Again, the mindset that the server was in the cloud and that the cloud has unlimited resources was raised. We reviewed the contract for the cloud virtual server and discovered the underlying infrastructure was SSD (Solid State Disk) but had a limit on IOPS (I/O per Second). WAIT… WHAT? Limits?  But it’s the cloud and the cloud is powerful and limitless! Well… not exactly… Yes you can have A LOT of virtual server resources but you need to pay for them. So if you only pay for a portion of the cloud, then you have limits. The disk I/O limits of the contract constrained the server to 250 IOPS with bursting up to 3000 IOPS. The above perfect storm needed about 500-800 IOPS. But wait… I can burst to 3000 IOPS. Bursting is great but if it can’t burst fast enough then the applications start to timeout and fail. After painting the entire big picture, it was decided to move the secondary databases to another drive array, move log files to another drive array and schedule the reporting to not conflict with the integration. After continued monitoring after the changes, the integration continues to run smooth (no lost data) and performance is well within acceptable limits.  As a bonus, the end users of both CRM and the other DB application noticed an overall increase in system performance. Happy client!

The moral of the story…

If you are having stubborn performance issues in the cloud, data center or on premise… look at the big picture and don’t assume anything. Being able to look at the big picture technically and objectively allows for quicker resolution to stubborn issues. Also note that the cloud is not limitless so make sure you know what those limits are.

If you are challenged with an issue (cloud or not) and don’t know where to turn… contact Beringer. Solving those issues is just downright fun!

[code-snippet name=”blog”]