Performance Issues in SQL Server 2014

I just spent the last week trying to figure out why some of my SQL queries were suddenly taking hours to run on a large dataset when they used to take seconds on an older machine.  The queries I have use a lot of subqueries and left-joins across several tables with hundreds of GB of data each.  At first I thought it might be because my dataset has grown to the point my queries were no longer efficient.  Perhaps I had too many joins or subqueries that were not using the indexes on the table.  I actually tried removing every case of "OR" condition in my queries, which seemed to help a little but nowhere near bringing down the hour or so it still took to run.  After messing with indexes and re-arranging queries non-stop, it turns out it's because of an update to cardinality estimation on the back-end in Microsoft SQL Server 2014.

Luckily, you can disable the cardinality estimation update on a query-by-query basis by doing:

After appending "  OPTION ( QUERYTRACEON 9481 )" at the end of my queries, I'm back to seeing them execute within seconds.  Whatever update Microsoft did to its cardinality estimation clearly missed a few edge cases (perhaps they don't expect large queries and datasets?).  If anyone else is running into this issue, I hope this saves them some time.  Bigger worry is if Microsoft thinks their update is so great that they end up removing the ability to backtrack down the road.