We recently upgraded our server from SQL Server 2008R2 to SQL Server 2014. We have a query that ran fine in 2008R2 but now in 2014 runs incredibly much slower and has a bad execution plan.
I've done several tests…
- Switch the 2014 DB back to 2008/2012 compatibility mode.
- Test the query using Pagination.
Both of those resulted in the query running the same as it did and fast as SQL Server 2008R2.
Why is the plan so bad and query run so long in SQL Server 2014?
This image shows 2 queries, one using rownumber the way it ran in 2008R2, and then the second is the fix with pagination. Both ran in 2014, both very different, but in 2008 we see the same performance as if we used pagination in 2014.
Best Answer
This is not an issue, it is by design and is considered a performance improvement.
If your queries do not run as expected you will need to address this in the code of your database/application.
You can force the old carnality estimator by using the trace flag 9481 at the server, session, or query level (using
OPTION (QUERYTRACEON 9481)). This will force it no matter what the compatibility level of the database is.There is also a ton of information that the CSS Team posted here.
A more detailed post on the Cardanility Estimator and changes around that process in SQL Server 2014, includes example.