Script for statistics in Synapse SQL

Once the data is loaded into a dedicated SQL pool, statistics collection from data is very important for continuous query optimization. The dedicated SQL pool query optimizer is a cost-based optimizer that compares query plans and chooses the plan with the lowest cost. The dedicated SQL pool engine will analyze incoming user queries where the statistics are constantly analyzed and the database AUTO_CREATE_STATISTICS option is set to ON. If the statistics are not available, then the query optimizer will create statistics on individual columns.

How to do it…

By default, statistics creation is turned on. Check the data warehouse configuration for AUTO_CREATE_STATISTICS using the following command:

SELECT name, is_auto_create_stats_on
FROM sys.databases

Enable statistics with the following command:

ALTER DATABASE <datawarehousename>
SET AUTO_CREATE_STATISTICS ON

Once the statistics command is received, it will trigger...