Troubleshooting Performance on SQL On-demand or Serverless - Network issues

Published Feb 12 2021 03:37 AM 2,994 Views
Microsoft

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. 

Questions

  1. are all the files been queried in the same region?
  2. 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?
  3. 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.

async.png

Note these results are for the waits on the front end, based on that we will work towards a possible RCA.

 

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!

Liliam 

UK Engineer

 

Co-Authors
Version history
Last update:
‎Sep 15 2021 12:10 PM
Updated by: