Query Optimizer Hotfixes

SQL Scotsman covers the query optimizer hotfixes which you can turn on with trace flag 4199:

The query optimiser hotfixes contained under Trace Flag 4199 are intentionally not enabled by default.  This means when upgrading from SQL Server 2008 R2 to SQL Server 2012 for example, new query optimiser logic is not enabled.   The reason behind this according to the article linked above is to prevent plan changes that could cause query performance regressions.  This makes sense for highly optimised environments where application critical queries are tuned and rely on specific execution plans and any change in query optimiser logic could potentially cause unexpected / unwanted query regressions.

Read the whole thing.

Related Posts

Statistics and Multiple Single-Column Indexes

Erik Darling is fusing together queries like Dr. Frankenstein in his lab: You may have noticed that both queries get pretty bad estimates. You might even be thinking about leaving me a comment to update stats. The thing is that I created these indexes, which means they get stats built with a full scan, and […]

Read More

Explaining Column Statistics

Bert Wagner takes us through column statistics in SQL Server: Statistics are the primary meta data used by the query optimizer to help estimate the costs of retrieving data for a specific query plan. The reason SQL Server uses statistics is to avoid having to calculate information about the data during query plan generation. For […]

Read More

Categories

December 2016
MTWTFSS
« Nov Jan »
 1234
567891011
12131415161718
19202122232425
262728293031