Category: Trace Flags

Categories
Performance SQL Server Trace Flags Tuning

SQL 2014 Cardinality Estimator Trace Flags

For reference:

  • 9481  – Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 7.0 through SQL Server 2012) of the cardinality estimator when creating the query plan.
  • 2312 – Use when running SQL Server 2014 with database compatibility level 110, which is the compatibility level for SQL Server 2012. Trace flag 2312 forces the query optimizer to use version 120 (the SQL Server 2014 and later) of the cardinality estimator when creating the query plan.
Categories
SQL Server Trace Flags

Trace Flag 2371

This trace flag (available in SQL Server 2008 R2 SP1 and later) alters the threshold calculation for auto statistics update on SQL Server instances so that larger tables do not require as many updates before statistics are updated i.e. the old 20% + 500 rows calculation does not apply for larger tables.

Personally, I would always set this as a start-up parameter on instances hosting PeopleSoft databases as in general those systems have a small number of very large tables which typically also have significant columns with a skewed data distribution.

Read more here