Today, I worked on a service request when our customer reported a performance issue for their Azure SQL Database. However, the CPU is low, and DATAIO and TLOG are not more than 20% of usage. So, in this case, what is the performance issue?
My lesson learned here is that reviewing these three counters are not enough to identify a performance issue, in this case, we found two important things that reveal the performance issue:
1) The first issue was the waiting stats (select * from sys.dm_os_wait_stats😞
- As you know every operation that SQL Server is doing is measure using waiting stats. In this case we found that the wait state with a high value was ASYNC_NETWORK_IO, that reports, a performance problem in any of these ways:
- The client is not able to manage all the information received from the Azure SQL DAtabase, for example, because the client application requested to much information, network issue, older driver, etc..
- The server is very busy and is not able to send in a way that the different client applications are demanding.
- In this case we found:
- The capacity of this Azure SQL Database with 1 vCore was not enough to reply all the requests performend by multiple clients.
- The multiple clients are requesting a high number of rows or the amount of data is high, because, we saw high logical reads in the different queries.
- So, increasing the capacity of the server to 2 vCores we gave the capacity to the Azure SQL Database to have more workers to reply and sent the data to the client applications.
2) The second point we found multiple timeouts that the client application dropped the connection after several time.
In this situation, as I mentioned in a previous article, enabling the Diagnostics Setting for your database is a good starting point when you are facing these types of issues.
Enjoy!
