Scenario <SLOW Client> : Customer is running multiples queries and he sees there are some delays during the execution, some queries are in the wait state.
Consider Review this document: Best practices for serverless SQL pool - Azure Synapse Analytics | Microsoft Docs
Run this script while you recreate the scenario
Note in this scenario, there was a lot of wait for Async network IO:
By definition: ASYNC_NETWORK_IO is an indication that your client application is not able to efficiently retrieve the data it needs from the system.
Note these results are for the waits on the front end, based on that we will work towards a possible RCA.
One problem can have multiple reasons even with similar symptoms. This is a slow client problem. In this case, the customer was running multiple queries from the same storage ( dedicate to SQL OD), all parquet files. The process of distribution and execution against the nodes was fairly fast. However, while sending the results back to the client, it was taking more time than expected, and I mean customer network.
Hence consider two approaches in a scenario like this. As much SQL Serverless is fast, you need to consider if the client app is consuming the information fast enough and if the network is keeping up. One possible way to rule out the network is to create a VM on the same region as the datacenter is localized and execute the queries on SQL Serverless from that VM instead of your computer.
Credits to Veljko Vasic on this as also the great product group team of SQL Serverless.
That is it!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.