Need help to troubleshoot query performance issue in SQL 2014(120) which works fine in SQL 2008 R2(100).

In my scenario a db was restored from a system(S-OLD) with SQL 2008 R2 to another system(S-NEW) with SQL 2014(12.0.02342) and the compatibility level changed to 120(For SQL Server 2014) on S-NEW. Post migration steps taken are DBCC CHECKDB, UpdateUsage, Index Rebuild and Stats Update in that order.

Now the application pointing to S-NEW is running extremely slow. But on S-NEW when the compatibility level was changed to 100 the application runs fast.

Comparing the Execution Plan of 100 vs 120 both on S-NEW shows that the nodes are all same with difference in cost of two nodes Index Seek and Index Lookup.

On 100 - Index Seek(1%) and Index Lookup(1%)

On 120 - Index Seek(25.3%) and Index Lookup(25.3%)

Notice that in 120 compatibility level the cost has gone considerably high. Query running on 120 took several hours(15hrs) compared to old time of less than 2min. Based on the DMV output the CPU usage is keeps increasing when in 120.

Roughly the query contains a join and around 20-25 Exists clause. Changing the Exists to Join also did not help.

Has anyone faced this issue and managed to solve or can provide pointers?

Did you try to change compatibility level to 110 and see how the performance is? There is a known issue that new cardinality estimation in sql 2014 causes some queries to perform badly compared to older compatibility levels which doesnt have this issue.

http://blogs.msdn.com/b/psssql/archive/2014/04/01/sql-server-2014-s-new-cardinality-estimator-part-1.aspx

http://blogs.msdn.com/b/saponsqlserver/archive/2014/01/16/new-functionality-in-sql-server-2014-part-2-new-cardinality-estimation.aspx

The big change in SQL 2014 is the new cardinality estimator which is a major shakeup in the optimization space. A lot of things can happen:

1) Queries run faster, because the new estimates fit the actual data distribution better.
2) Queries run slower, because the new estimates are further away from the actual data distribution.
3) Queries run slower, because there are bugs/limitation in the new CE, which results in completely incorrect estimates.

Micrssoft are aware of issues in the last group and are working with fixes. I got feedback on one item I had reported myself today that it had been fixed. One thing to note is that this fix will only be available under trace flag 4199. (Microsoft generally hides optimizer fixes under this trace flag, since an optimizer change can always cause plan regression. The exception are incorrect-results bugs.)

Since we don't know anything about your system, we can't tell if you are hit by the bugs, or just are unlucky with the new estimates.

I would advice that you look closer at the plans. What is important is not the percentages for the operators; these are only estimates, and since there are no actual values to compare with they say nothing. But if you look in the actual plans and compare the actual values with the esimates, you can get a grip off whether the estimates are completely off the target or still reasonable (but unfortunate for your query).

This white paper has more information, not the least how you can switch between the new and old CE for a single query:
https://msdn.microsoft.com/en-us/library/dn673537.aspx

Thank you Erland and Ashwin for responding.

Erland,

Can you let me know what details are required in order to classify this situation as hit by bug or being unlucky.

I'll take a look at the EP in detail as per your suggestion to compare actual vs estimates for all the nodes. Also I searched for 2014 CE issue and as a workaround people have tried replacing HASH JOIN with LOOP JOINS and in another instance split the query into two blocks. I'm going to try these as well. But would like to mention that as part of post migration activity I did do a index rebuild and stats update.

Let me also share brief details on the suspect query. At a glace the EPs have same nodes but will do a detailed analysis.

UPDATE T1

SET .....

FROM T1 JOIN T2 ON T1.C1 = T2.A1 and T1.C2 = T2.A2 and T1.C3 = T2.A3 and T1.C4 = T2.A4

WHERE EXISTS (

SELECT TOP 1 1

FROM

T3 JOIN T4

ON T3.B1 = T4.D1

AND T4.D2 = T2.A5

AND T2.D3 = T1.C5 AND T3.Type = 'Type1'

)

AND 

EXISTS (

SELECT TOP 1 1

FROM

T3 JOIN T4

ON T3.B1 = T4.D1

AND T4.D2 = T2.A5

AND T2.D3 = T1.C6 AND T3.Type = 'Type2'

)

.....Around 20-25 Exists clauses

T1 - Table variable in Actual query but changed to physical Table for perf testing. Under 100 rows

T2/ T4 - Large Tables

T3 - Under 300 rows

Can you let me know what details are required in order to classify this situation as hit by bug or being unlucky.

Hard work and good understanding of execution plans. I did not say that it was easy. But without seeing the query and the execution plan, I can only talk in general terms.

I'll take a look at the EP in detail as per your suggestion to compare actual vs estimates for all the nodes. Also I searched for 2014 CE issue and as a workaround people have tried replacing HASH JOIN with LOOP JOINS and in another instance split the query into two blocks. I'm going to try these as well. But would like to mention that as part of post migration activity I did do a index rebuild and stats update.

Beware that all such workarounds are likely to be specific to a certain query. I have not heard of that changing join types would be a general cure, and it sounds unlikely that it would be.

But, yes, splitting up a huge query in several where you bounce intermediate results in a temp table has always been a possible strategy to deal with poor estimates, long before the new CE came into play. By materialising the intermediate result, you give the optimizer more information for the next step. But there is of course also a cost for using the temp table.

.....Around 20-25 Exists clauses

Ouch!

T1 - Table variable in Actual query but changed to physical Table for perf testing. Under 100 rows

Beware that table variables do not have distribution statistics and alone do not trigger recompilation. This means that table variables are often estimated to have a single row, so if they have a lot more, the optimizer is likely to really wrong. Again, this is no different with the old or new CE.

I understand 20-25 exists clauses can be an overkill. I changed the query to use INNER JOIN instead of Exists clauses but to no avail.

Upon checking the EP for 100 vs 120, 120 had done some optimization on Actual vs. Estimated and Logical IO Reads but the optimization still doesn't yield good query response times.

To conclude I'll wait for SP1 for SQL 2014 to use the new CE.