Search code examples
azure-synapse

Azure SQL Serverless inbuilt Pool Column/Field Limitations

We have created a SQL Database from our Azure SQL Serverless Pool. We have a table that has over 450 fields. Whenever we try to extract the table with all the fields the query times out and produces the following error:

Msg 15884, Level 16, State 1, Line 2
Query timeout expired.

However, when I we try to extract just a few fields it successfully gives us all the rows.

Therefore, can someone let me know if there are any limitations on the number fields when extracting tables from Azure SQL Serverless Pool?

Solution

  • Msg 15884, Level 16, State 1, Line 2 Query timeout expired.

    This error is because the SQL query takes long time to execute. Unfortunately, timeout settings cannot be modified in Synapse SQL serverless pool. The solution is to either optimize the query or to optimize the data stored in external storage.

    Below are some points for better performance.

    • Try to store data in parquet format than csv or Json file. Parquet files are columnar format and size will be lesser for same data which is stored as csv or Json format.
    • Do not use the storage account with other workloads during query execution.
    • In order to query large amount of data, use Azure Data Studio or SQL Server Management Studio than azure synapse studio.
    • Make sure to have Synapse serverless SQL pool and Storage in the same region.

    Refer Microsoft document on Best practices for serverless SQL pool - Azure Synapse Analytics .