0

I have 2 databases that contain nearly identical data.

  • The first has compatibility level 100 and is restored on a sql2012 instance.
  • The second has compatibility level 110 and is restored on a sql2014 instance.

I am running the same query on both databases.

On the first database the query finishes in approximately 20-30 seconds.

I tried running the same query on the second, and it did not finish after 50 hours. If i try running the query on the second, but changing the compatibility level to 120 with trace 2312, the query finishes in 40 minutes.

I have tried rebuilding statistics for all tables affected by the query, and i also checked fragmentation (the first database actually looks to have far more fragmentation) Why is there such a difference in performance, and what can I do to fix this?

This is the actual query plan for the first database:

https://www.brentozar.com/pastetheplan/?id=rJUN72gQB

This is the estimated query plan for the second database:

https://www.brentozar.com/pastetheplan/?id=SysNr2gQr

The full query looks like this:

https://justpaste.it/53b9c

  • 3
    Was this code written by an automated tool? The formatting and names are just hideous to decipher. Even after an automated formatting this is just painful, not to mention nearly 450 lines. How many rows is this returning? 20-30 seconds is pretty slow for code that runs frequently. – Sean Lange Aug 1 at 19:06
  • yes, this code is generated. This query is a single query from a group of queries that runs as part of a process. I singled this query out as one that seems to be extremely slow. In this case the query does not affect any results, im just trying to figure out why its so slow. I don't really know what the query is doing either, but looking at the 2 query plans they seem similar enough that im not sure what is causing the performance to be so bad – Steven Hsu Aug 1 at 20:02
  • 2
    Honestly with this sort of awful ORM query, you could either true-up your compatibility levels or revert to hand writing this query for the application side. Especially for ORM queries, you should expect performance across versions to differ. – LowlyDBA Aug 1 at 20:22
2

Why is there such a difference in performance...

Check out this part of the estimated plan for the slow query (second server):

screenshot of 21 row estimate in slow query plan

This estimates that 21 rows will come out of the join to RatesByCategory (which is after _Results323 has been joined to AttributesTable twice, and DataStreamDirection). If we look at the equivalent part of the actual plan for the fast query:

screenshot of 16 million row estimate in fast query plan

There will actually be ~16,000,000 rows coming out of that join. These is bad news, because those millions of rows flow right into a wall of Nested Loops joins, which is likely where that query slows to a crawl.

You were on the right track looking at estimates and trying to update stats, but this actually seems to be because of a row goal set by the semi-joins later in the execution plan.

Here's where the semi-join lives, and notice that everything "under it" has an estimate of 1 row:

screenshot of semi join where the row goal effect starts

...and what can I do to fix this?

Try adding this hint to the end of the query:

OPTION (QUERYTRACEON 4138)

This will disable the row goal and should you give you a different plan (possibly similar to the one on the lower compatibility level).

Since you said this query is generated, your best bet for solving this might be to create a plan guide so that you can add that hint without having to change the application that produces the query.


If i try running the query on the second, but changing the compatibility level to 120 with trace 2312, the query finishes in 40 minutes.

Changing compat level to 120 would have enabled the "new" cardinality estimator, resulting in a different query plan - likely one that was less affected by the row goal (and thus why you were able to see it finish in 40 minutes, where the compat 110 query ran for 50 hours without finishing).

  • I tried running the query with the hint you mentioned, but the query plan is still identical as before. Maybe my alternative is to create a plan guide using the querytraceon 2312 hint instead? – Steven Hsu Aug 6 at 19:29
  • just to make sure im understanding plan guides properly, it looks like i need to add an entry for the exact query, and then any time that query is ran the hint is automatically added? Just want to clarify because this doesnt seem very robust (i.e if a column name is changed etc) but i guess thats the best alternative? – Steven Hsu Aug 6 at 19:34
  • @StevenHsu Yeah, that's how they work and that's the exact downside of plan guides. If anything changes about the query text, it just stops working. You can try TF 2312 instead with the plan guide, if 40 mins is "good enough" for this query. I'm surprised there was no change at all with 4138, that stinks! – Josh Darnell Aug 6 at 20:09
  • weirdly enough, i ran a full run of the application that generates and runs the query in question (in addition to many others), and now the query runs in under a minute without any query hints. I don't think this is related to caching or anything since i tried dbcc dropcleanbuffers, dbcc flushprocindb, and dbcc freesystemcache and its still finishing in a minute now. I guess maybe some big changes were made on some relevant tables? This is that the new estimated plan looks like: brentozar.com/pastetheplan/?id=Bkf06vv7r – Steven Hsu Aug 6 at 21:17
  • looking at this some more, im pretty sure this is a statistics thing. The tables being referenced look unchanged, but the queryplan is completely different. I'm not sure if i missed a table when updating statistics previously, but i re-restored the backup and updating statistics seems to have fixed the query – Steven Hsu Aug 6 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.