I'm experiencing significant delays in retrieving results from a stored procedure on SQLExpress, which is hosted on AWS. I'm using a db.t3.medium instance with 2 vCPUs and 4 GB of RAM, and it has 3000 provisioned IOPS and a storage throughput of 125 MiBps. The SSMS Activity Monitor indicates that ASYNC_NETWORK_IO is the culprit, taking 12 seconds or more to load results into my application or the SSMS results grid. I estimated the dataset to be around 2.5 MB. When I ran the stored procedure via sqlcmd, it also took about 13 seconds to return results, but the STATISTICS TIME shows that CPU time is 47 ms and elapsed time is 45 ms. This leads me to believe that the issue is not with the query itself, but rather in how the data is being delivered to the client. Given that the baseline network bandwidth for a t3.medium instance is supposed to be 256 Mbps, which seems adequate, I'm looking for help in identifying the exact metrics to examine or settings to adjust to improve this situation.
2 Answers
Honestly, a db.t3.medium instance isn't ideal if you're looking for solid performance, especially for something critical. Burstable instances can fall short under load. If your app is performance-sensitive, you might want to consider switching to a dedicated instance type with more resources. But first, check if your current instance is hitting CPU or memory limits. Monitoring your resources will help clarify if the current setup is indeed the bottleneck.
I see where you're coming from! Even though you're not seeing high CPU usage, the ASYNC_NETWORK_IO might suggest that it's a networking issue rather than the query itself. You could experiment with increasing the buffer size on your client-side settings, as larger buffers can sometimes help improve data transfer speeds. Also, look into optimizing your network configuration to see if there's any packet loss or latency affecting the delivery.
Related Questions
How To: Running Codex CLI on Windows with Azure OpenAI
Set Wordpress Featured Image Using Javascript
How To Fix PHP Random Being The Same
Why no WebP Support with Wordpress
Replace Wordpress Cron With Linux Cron
Customize Yoast Canonical URL Programmatically