We have been testing to change the compatibility level of our OLTP production databases from 2008 (100) to 2014 (120).
We are experiencing a lot of performance issues with queries that with high cardinality that are taking 60 seconds or more. The problems appear to originate from the changes to the cardinality estimator that Microsoft has introduced as part of SQL Server 2014.
In one instance, I am seeing SQL Server performing an index scan on an index with 200 million records (as opposed to doing a seek for a handful of records). The query is relatively straightforward and we are very confused as to how SQL Server can miss it.
We have 1000s of queries and testing all of them in all scenarios is impossible.
We have tried to optimize each of the bad performing queries. The only thing that seems to work is to either:
- Use ugly join hints
- Use trace flag 9481 at the query/global level (which basically forces queries to use the legacy cardinality estimator)
We have SQL Server 2014 SP1 installed. We tried using trace flag 4199 and 9476 with no luck.
At this point I see the following options:
- Postpone the upgrade of the compatility level until we migrate to SQL Server 2016 (or a future version)
- Use trace flag 9481 globally
- Use trace flag 9481 at the query level (I am more in favor of turning it on at the instance level though)
My understanding is that SQL Server 2014 SP2 does not have fixes to the cardinality estimator.
We noticed that SQL Server 2016 comes with another update to the cardinality estimator. Do we know if that update addresses most of the issues introduced as part of the SQL Server 2014 cardinality estimator?
Could someone please advise?