Question

One or more of my connectors are failing with the error 'Drop statistics constraint' but when I try to remove the statistics constraint, the constraint doesn't seem to exist. How can I resolve this?

Environment

SQL Server or Azure Synapse destination

Answer

Please execute the following steps in your warehouse:

1. Turn off AUTO_CREATE_STATISTICS

   ALTER DATABASE databaseName SET AUTO_CREATE_STATISTICS OFF;

2. Turn off AUTO_UPDATE_STATISTICS

   ALTER DATABASE databaseName SET AUTO_UPDATE_STATISTICS OFF;

3. Run the following query to get all constraints starting with _WA_Sys_

   SELECT OBJECT_NAME(s.object_id) AS object_name, COL_NAME(sc.object_id, sc.column_id) AS column_name, s.name AS statistics_name
   FROM sys.stats AS s
   INNER JOIN sys.stats_columns AS sc  ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
   WHERE s.name like '_WA_SYS%' ORDER BY s.name;

4. Drop all the statistics constraints which start with _WA_Sys_

   DROP STATISTICS schema.table._WA_Sys_XXXXX

Cause

The Query Optimizer in SQL Server and Azure Synapse uses automatically generated statistics constraints to create query plans that improve query performance. More Info.

What can be confusing is that these constraints do not appear in the regular places one might look for them - they can even be described as 'phantom' constraints. This is because they exist exclusively on the Azure compute node instance, and they are created via the _WA_SYS_ constraints. More Info. 

However, the caveat is that this blocks Fivetran from altering the data type or data size of the field(s). Fivetran may need to alter the data type/size of a field in the warehouse if the corresponding change id present in the source.

It is a hard requirement on the Fivetran side to have the ability to change field data types. By having AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS set to on, the warehouse will continually re-create the statistics, blocking the changes from being made and thus blocking the sync.