This article is contributed. See the original author and article here.
Scenario: Customer is running multiples queries and he sees there are some delays during the execution, some queries are in the wait state.
Questions
- are all the files been queried in the same region?
- is the storage account been used only to query the SQL Serverless files or do we have other processes accessing this storage account at the same time?
- which type of files? Parquet, CSV, JSON? 4)If CSV? did the customer created the stats?
Consider Review this document: Best practices for serverless SQL pool – Azure Synapse Analytics | Microsoft Docs
Troubleshooting
Run this script while you recreate the scenario
Execution: Troubleshooting SQL On-demand or Serverless DMVs – Microsoft Tech Community
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.
Possible RCA
One problem can have multiple reasons even with similar symptoms. 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!
Liliam
UK Engineer
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.
Recent Comments