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?
SQL Server or Azure Synapse destination
Please execute the following steps in your warehouse:
- Turn off
ALTER DATABASE databaseName SET AUTO_CREATE_STATISTICS OFF;
- Turn off
ALTER DATABASE databaseName SET AUTO_UPDATE_STATISTICS OFF;
- Run the following query to get all constraints starting with
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;
- Drop all the statistics constraints which start with
DROP STATISTICS schema.table._WA_Sys_XXXXX
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_UPDATE_STATISTICS set to on, the warehouse will continually re-create the statistics, blocking the changes from being made and thus blocking the sync.