Your SQL is tuned perfectly and delivers thousands of rows in milliseconds on your test system, but your end users are complaining about slow application performance. Time is of the essence because poor performance affects productivity and the company’s ability to make money. Everybody is looking at the database as the culprit. You know it’s not. But what do you do? Let us learn in this blog about How to Identify Application vs Network Performance Issues?
Using SQL Server Dynamic Management Views (DMV) you can run a query on sys.dm_os_wait_stats that will return stats that will help determine if it’s a CPU, Memory, or I/O issue. The columns for sys.dm_os_wait_stats provided by Microsoft can be found here. In this case, we are interested in wait_type and wait_time_ms. Create a query that results in wait_type being sorted by highest to lowest wait_time_ms. If the top wait_type is ASYNC_NETWORK_IO you probably have a problem either with the client-side of the application or the network.
It is not unusual for some client applications such as those built on MS Access to wait for an ACK from SQL Server for each row it is retrieved. If there is a large result set, then all those ACKs add up to significant wait times. Such applications should be restructured so that they issue the ACK for batches of rows rather than by single rows.
If the client app is well behaved in terms of handling large result sets, then you should start considering that you have a network issue. In this case, you need to consider the end user and/or app server locations. It could be that end users within the physical data center location have no problems, but those located in branches or home offices do. Using perfmon you can check stats such as output queue length (if it’s 2 or more you have network saturation/latency issues), bandwidth level, and available bandwidth (if traffic exceeds 65% of capacity the network can be an issue). You will want to run this on both the SQL Server and some representative clients.
In the case of the performance issue being related to the network, you have three basic choices:
- Upgrade network bandwidth. If available, this can be expensive – especially if you have multiple remote sites/users that need upgrading. And it’s a recurring monthly cost.
- Purchase WAN Acceleration appliances. This can be expensive as you would need the appliance at each location where the performance issue is occurring. Additionally, WAN Accelerators commonly will issue a false ACK to SQL Server – which does accelerate traffic, but also puts your data at risk since you cannot ensure that it was delivered. Many shops with WAN Accelerators turn them off for SQL Server traffic for this reason.
- NitroAccelerator is software from Nitrosphere that runs on each endpoint, so you can install it at each location having performance issues. It optimizes SQL Server’s TDS (Tabular Data Stream) protocol by compressing and optionally encrypting traffic between SQL Server and the connecting application (another SQL Server, client app, or app server). It is relatively low cost compared to the first two options and ensures full data recovery as it is compatible with SQL Server’s recovery processes. The nice thing with this option is it can be remotely installed and requires no configuration so you can address the issue in literally minutes.
It is important that before you look for solutions that you know what the cause is of the problem. Why fix a network issue if it is an application problem and vice versa? Using simple techniques as described can help you avoid unnecessary costs and solve the problem quickly with minimal disruption. Let us learn about Network Performance?
Reference: Pinal Dave (https://blog.sqlauthority.com)
