Trying to determine why a plan changed with a syntax change

Mark Wilkinson 2014-04-30 15:09:41

Pre syntax change I was getting the plan in tmp407C (sorry for the lack of name. After the syntax change I am seeing 'betterplan'.

Implicit JOINs were converted to INNER JOINs.

Aaron Bertrand 2014-04-30 15:12:35
Without even looking at the plan, I can offer that the actual specifics of the syntax change are irrelevant – you issued a new query (even if it is the same thing logically), and got a new plan based on the fact that the query text – converted to binary – was different. The query optimizer generated a new plan based on the information it has now, not the information it had when the previous version of the query was running and its plan was compiled.
Mark Wilkinson 2014-04-30 15:23:28
Aaron – Thanks, I forgot about this. If I ran it with RECOMPILE though, it should have generated a new plan, correct? Or would it still look for an existing?
Aaron Bertrand 2014-04-30 15:25:09
Not sure I understand the sequence of events. If you changed the query text, and this was a new query you've never sent to SQL Server before, it will generate a new plan with or without RECOMPILE.
Mark Wilkinson 2014-04-30 15:31:20
The RECOMPILE option was added to BOTH versions of the query. Even with that option, the version with the implied joins runs slow, the version with INNER JOIN runs fast.
@SQLTrooper 2014-04-30 15:25:28
WITH (RECOMPILE) always generates a new plan.
Mark Wilkinson 2014-05-06 11:49:21
Sorry everyone that has looked at this. I took someones word for it when they said they only changed the syntax. After comparing the actual code, it turns out they also removed a NOT IN (…). This operation is what was causing all the trouble. Thanks for looking at this though, and thanks for the information!
@SQLTrooper 2014-04-30 15:24:49
There appear to be a few issues at first glance. I see some RID look-ups – perhaps you can examine those heaps and place useful indexes on the tables. Note the exclamation point on the SELECT – it's suggesting indexes as such. That lazy spool resulting in 976M rows isn't helping much either, but without seeing the query it's hard to make a recommendation beyond that.
Mark Wilkinson 2014-04-30 15:33:51
Thanks for the suggestions. Unfortunately I am well aware of the issues with this database. I am just confused why a simple syntax change would cause such a huge performance increase, with completely different plans.
@SQLTrooper 2014-04-30 15:37:50
The lazy spool is the big differential between the two plans. Without seeing the query, it's rather difficult to understand why the optimizer is choosing to go with the spooled approach.
Kevin 2014-05-02 19:23:12
Like Aaron said, a plan will always change even for the pettiest and most minor of changes. Even something incredibly minor like adding an extra space or capitalizing a word in one version and not the other will cause SQL Server to compile a new plan.

@SQLTrooper is also correct in that the new plan, without the lazy spool, saves an enormous amount of IO. The lazy spool rewinds more than 43k times, causing 10Gb of data churn. By avoiding the lazy spool, MSSQL is able to greatly improve the execution time of the query.