Monitoring and updating statistics about data across a system

Statistics is an important concept in query optimization. Generating statistics is the process of collecting metadata about your data—such as the number of rows, the size of tables, and so on—which can be used as additional inputs by the SQL engine to optimize query plans. For example, if two tables have to be joined and one table is very small, the SQL engine can use this statistical information to pick a query plan that works best for such highly skewed tables. The Synapse SQL pool engine uses something known as cost-based optimizers (CBOs). These optimizers choose the least expensive query plan from a set of query plans that can be generated for a given SQL script.

Let's look at how to create statistics for both Synapse dedicated and serverless pools.

Creating statistics for Synapse dedicated pools

You can enable statistics in Synapse SQL dedicated pools using the following command:

ALTER...