Blabberbox » Performance Issues in SQL Server 2014Share on Twitter

Performance Issues in SQL Server 2014

October 10th, 2015 | Posted by pftq in Blabberbox | #
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:
Quote
*your query*   OPTION ( QUERYTRACEON 9481 )

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.
82 unique view(s)

Leave a Comment

Name: (Have an account? Login or Register)
Email: (Won't be published)
Website: (Optional)
Comment:
Enter the code from image: