Azure, data warehouse

Azure DW, The Peril of AUTO_CREATE_STATISTICS

Last year the Azure DW team announced the availability of added another hotly requested feature, auto creation of statistics.

Auto creating statistics has many known benefits which impacts DevOps, as database deployment pipelines don’t have to include the statistics creating queried, DBA’s who don’t have to worry about the lack of statistics on objects and business users who normally suffer from poorly perming reports and queries.

The Azure DW blogpost describes the impact well.

The Peril, yes there is one – is that auto created stats objects will sometimes prevent columns from being dropped from tables. The following error can sometimes be observed:

SQL exception has occured in script: '0000001-dropColum.sql'
Script block number: 0; Block line 1; Message:
System.Data.SqlClient.SqlException (0x80131904): The statistics 'Stat_b71bb60085b5420bbd7b95c46e578bb6' is dependent on column 'CostValue'.

Should this error occur, then the best option would be to disable auto create stats, and after the db deploy / code has been run we can enable it:

Disable
ALTER DATABASE SalesDW SET AUTO_CREATE_STATISTICS OFF;

Enable
ALTER DATABASE SalesDW SET AUTO_CREATE_STATISTICS ON;

Top tip: disable at the start of your database deployment and then enable at the end. MS Docs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s