SQL 2016/SQL 2017 CE Performance on Information_Schema inferior to SQL 2014 out of the box I have a Database with 2300 Tables/Views. When i run this query to do a simple select on information_schema, i get over 2 minutes on SQL 2016/2017 but under 5 seconds in SQL 2014. When i enable the trace flag 9481. It works. Here is the query. The difference is the Cardinality Estimator, however my question is why would it not work out of the box like in SQL 2014.

SELECT 
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.TABLE_SCHEMA,    
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.TABLE_NAME,       
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.COLUMN_NAME as CONSTRAINT_KEYS,  
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_SCHEMA as REFERENCE_SCHEMA,       
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME as REFERENCE_TABLE,           
  unique_usage.COLUMN_NAME as REFERENCE_COLUMN      
FROM       
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE      
INNER JOIN  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS      
ON        
  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME =  
  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME      
INNER  JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS      
ON       
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = 
  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME      
INNER JOIN       INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE unique_usage    
ON 
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =     
  unique_usage.CONSTRAINT_NAME


If any of you has a database with large number of tables.You are welcome to try it and see what you see. Our adhoc reporting tool depends on these queries and it is proving a roadblock to upgrade to SQL 2017.
Here is an excellent article that discusses all the aspects of the new cardinality estimator.
Bottom line - as with all changes, you win some, you lose some, and hopefully overall you win more than you lose.
With the new cardinality estimator, it is a huge win overall, in spite of the occasional hickups like you are experiencing.
You have several work arounds other than the TF, and they are discussed in the article. Easiest should be either using a query hint, not to lose the benefits of the new estimator.

USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')