0

Given the following query (note the SELECT pKey sub query is dynamically generate in code thus the reason for the sub query):

SELECT COUNT(p.pKey) AS [Value] FROM ( 
  SELECT pKey
  FROM [Profile] LEFT OUTER JOIN [Groups] ON gKey = pgKey
  WHERE gName = 'Acme' AND pSearch04 = 'ACTIVE'
) p LEFT JOIN(SELECT dlpKey FROM DataLoads WHERE dlprocKey = 60909) d ON p.pKey = d.dlpKey
WHERE d.dlpKey IS NULL

I'm getting very different query plans on the 'same' database (albeit different SQL Server versions).

In one data center we have Microsoft SQL Server 2008 R2 (SP2) – 10.50.4000.0. In another server we have Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) - 11.0.5388.0 (X64). The 2008 has compat level of 90 while the 2012 has compat level 110. This query is generating different plans where 2012 does table scan (times out) and 2008 appears to use an existing index (returns immediately).

The 2012 plan also suggests an index creation that would have a 98% impact while 2008 makes no such suggestion. Note, I don't really want to make an index (on field pSearch04) as suggested because our database is a multi-tennant database and pSearch04 could be completely different data across clients.

I used Red Gate SQL Compare to compare the schemas and it said they were the ‘same’ (just few minor diffs in users).

I tried looking at the saved plans on the 2008 server, and was told to compare the CardinalityEstimatorModelVersion property and/or try the plan comparison option - I had neither of them in my version of SSMS.

Any suggestions as to how I might debug to see why the 2012 seems to generate an execution plan that uses a table scan? My limited SQL DBA skills are at a loss. Examining the table that had the table scan on it, the indexes and relationships are identical (via manual compare vs just using Red Gate).

2008 Execution Plan: https://pastebin.com/zgUTNy2q
2012 Execution Plan: https://pastebin.com/ab2qf5ut

Let me know if there is any additional information I can provide to help solve this.

Terry
  • 2,148
  • 2
  • 32
  • 53
  • What if you add `OPTION(RECOMPILE)` to your query(at the end) – Hackerman May 04 '17 at 20:58
  • Googled and found http://stackoverflow.com/questions/20864934/option-recompile-is-always-faster-why regarding RECOMPILE. And as answer suggested, I updated statistics and now the query actually runs vs timing out, but the execution plan still has a table scan in 2012 and not in 2008. Still troubling... – Terry May 04 '17 at 21:04
  • A query will have different execution plans on the *same* machine on different dates as the data changes and the distribution of values changes. Different versions have different optimizers, newer and better algorithms and features so of course, the execution plans will be different – Panagiotis Kanavos May 05 '17 at 12:19
  • As for adding the index, 98% is a huge difference. It means that you have an extremely serious performance issue. That's a 20x improvement, or 2000%. If you want to be sure, capture a trace with SQL Server Profiler and analyze it with the Database Tuning Advisor. I suspect you'll find a lot of missing indexes. This can be very bad, especially in a multi-tenant environment – Panagiotis Kanavos May 05 '17 at 12:20
  • As for versions - the earliest supported version is 2012 and SQL Server 2017 is coming. – Panagiotis Kanavos May 05 '17 at 12:23
  • If SQL Server 2012 returns bad execution plans you either have stale statistics, or the data just isn't the same. Make sure to update statistics in order to get accurate execution plans – Panagiotis Kanavos May 05 '17 at 12:24

0 Answers0