A three tier application running on Azure VMs
We were recently contacted by a customer because they had moved their application to a test environment on Azure and performance had become unacceptable. The application consisted of a three tier architecture: an HTTP layer, a middle tier of J2EE applications running on JBoss and the SQL Server databases on the back-end.
Checking SQL Server performance on the Azure VMs
After running our instaSQLAudit report we were a bit stumped because the overall performance counters for SQL Server looked good. Page life expectancy hovered around 800,000, disk latency was low; CPU usage was also low and there were no blocking locks. What put us on the right trail was to look at the profiler trace data.
SP_CURSORFETCH and SP_CURSORCLOSE are server-side cursors. What happens is that the application server (JBoss) fetches small batches of rows from SQL Server as it requires the data. This is commonly called a “chatty” application. The term “chatty” refers to the high amount of round trips an application makes to retrieve data from SQL Server. In some cases, a round trip is made for each record to be fetched.
Server side cursors are very sensitive to network latency because of the many round-trips made on the network. According a paper by the Microsoft SQL Server Customer Advisory Team, Azure data center latency can magnify performance problems caused by cursors and 10-fold slow-downs to retrieve the data are not uncommon.
Improving SQL Server performance on the Azure VMs
The final recommendation to this customer was to do one or all of the following:
- Ensure that minimal latency exists between the application and SQL Server by using Azure affinity groups
- Tune the queries being sent through the cursor by improving indexes.
- Retrieve only the fields that are needed by the application. Note above that the query consuming 99.9 % of reads is doing a “SELECT *” operation.
- Increase the cursor batch size.
- Switch to a client side cursor instead of a server side cursor. Often this is not possible because it requires a significant re-write of application code.
- Move the application and database to the same server.
* AZR376-How to scale Windows Azure SQL Databases (Rm224-225).pdf
If you need help performance tuning your SQL Server on Azure