We are pleased to announce that Azure SQL Data Warehouse (Azure SQL DW) now supports automatic creation of column level statistics. Azure SQL DW is a fast, flexible, and secure analytics platform for the enterprise.
Modern systems such as Azure SQL DW, rely on cost-based optimizers to generate quality execution plans for user queries. Even though Azure SQL DW implements a cost-based optimizer, the system relies on developers and administrators to create statistics objects manually. When all queries are known in advance, determining what statistics objects need to be created is an achievable task. However, when the system is faced with ad-hoc and random queries which is typical for the data warehousing workloads, system administrators may struggle to predict what statistics need to be created leading to potentially suboptimal query execution plans and longer query response times. One way to mitigate this problem is to create statistics objects on all the table columns in advance. However, that process comes with a penalty as statistics objects need to be maintained during table loading process, causing longer loading times.
Azure SQL DW now supports automatic creation of statistics objects providing greater flexibility, productivity, and ease of use for system administrators and developers, while ensuring the system continues to offer quality execution plans and best response times. The picture below shows a sample statistics histogram that Azure SQL DW query optimizer uses during the query optimization phase.
Automatic statistic creation is enabled by default for all new data warehouses that are being created. For existing instances, this option is disabled by default and users need to opt-in to enable it. Just like in SQL Server, auto create option exists at the database object level.
To enable or disable automatic statistics creation in SQL DW, execute the following statement:
ALTER DATABASE { database_name } SET { AUTO_CREATE_STATISTICS { OFF | ON } } [;]
As a best practice and guidance, we recommend setting AUTO_CREATE_STATISTICS option to ON.
For more information about statistics objects in Azure SQL DW, including automatic statistics creation process, refer to our documentation.