Different Query Plans from SQL 2008R2 to SQL 2012
There is not a way to edit the query, add hints, or any optimization on the App side.
We recently upgraded to SQL 2012 and are having some severe performance problems with this query.
See attached Actual Query Plan from SQL 2008R2.
See attached Actual Query Plan from SQL 2012.
SQL 2012 won't make it parallel unless I include Trace 8469 on this query. Even with that flag, the query returns in 7 seconds (vs 30+) where on the 5 year old server (same # cores), it returns in <2 seconds. I've been trying to create a new plan in SQL 2012 to use the query trace 8649 for this query (without startup trace 8649) but have been unable to make it stick. Am I barking up the wrong tree? What other tactic can i use to make it go parallel? I've also included sp-Config output from both servers (attached as well).
Moved from comments:
I've updated statistics and ran: exec sp_recompile N'cda_employeeExtensionBase'; exec sp_recompile N'cda_employeeBase'; …no change in query performance. I enabled trace 4199 globally, recompiled … no change in query performance. 8649 seems to be the only one that does the trick to get the parallelism juice flowing! I set the MAXDOP and Parallelism cost back to the same, all traces off, recompile, no change.
SSRS is running on this DB server so I reduced MAX memory, and left min memory high to reduce the amount of data SQL purges from memory. I will set them back now to the R2 values and see if that helps. Reading up on parameter sniffing at the moment.
I've applied CU8 with limited improvements.
Here are the SQL stats as requested.
The optimizer expected to have to process only 749 rows here to meet the ultimate goal of a maximum of TOP (51) matches. The query in fact only produces 31 rows in total, so the operators further down the tree end up processing their entire potential input set, rather than the small number of rows expected by the optimizer. There is a supported way to disable this Row Goal behaviour, using TF 4138, but applying this globally would likely regress other important plans. I am holding this option in reserve at this stage.
Most likely, the new plan is due to a new optimizer trick added in 2012. There is no supported way to turn this feature off, and although I know how to do it, I am reluctant to share it for supportability reasons. I would need a statistics-only copy of the three tables involved to validate my reasoning on this.
The standard mechanism to prevent a plan regression when upgrading to a new version is to use a plan guide. You have the old plan from SQL Server 2008R2, so a plan guide would be a perfect solution. However, the query uses LIKE which cannot be parameterized, meaning we cannot create a single plan guide for all future queries of the same pattern. This is most unfortunate. I doubt you will want to create a plan guide for every possible name combination!
You should be careful to set the system configuration options on 2012 exactly as they are on the R2 instance. There are differences in:
- The cost threshold for parallelism (5 vs 4)
- Max degree of parallelism (4 vs 8)
- Max server memory (120,000 vs 112,640)
- Min server memory (65,536 vs 102,400)
All these can affect plan selection.
Note: TF 8649 is undocumented, you cannot use it on a production system without invalidating your support agreement.
There are no parameters to sniff in this query (and there can't be, hence the plan guide problem).
BTW, it's hard to not notice the index naming convention – they were generated using the DTA. You realize that Dynamics will not preserve those indexes? If CRM is anything like AX, you have to do all your index tuning inside the product via the AOT, else you will lose those indexes. I think you need to take each query individually and tune them specifically – not sure if your version allows you to use included columns (mine does not).