Azure Synapse Analytics Serverless SQL Best Practices

Microsoft have a best practices document available here which highlights certain actions that can be taken to optimise use of the Serverless SQL service within Azure Synapse Analytics. The best practices document covers several actions which can be grouped into the following categories.

Part 1: Storage & Files

• Co-locate Storage
• Azure Storage throttling
• Prepare files for querying
• Azure AD pass-through performance

Part 2: Data Types

• Use appropriate data types
• Check inferred data types

Part 3: Filter & Search Optimisation

• Push wildcards to lower levels in the path
• Use filename and filepath functions to target specific partitions

Part 4: CSV Optimisations

• Use PARSER_VERSION 2.0 to query CSV files
• Manually create statistics for CSV files
  

Part 5: Optimise Repeated Data Usage

• Use CETAS to enhance query performance and joins