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.