Two servers, different execution plans. Several orders of magnitude difference in run time

Question

Two servers, Development and Live. Live server is an Amazon RDS SQL Server Web instance. Both servers have identical schema and data. There is a good spatial index on the geometry column. On my development server the query executes in < 30 milliseconds. On Live server the query takes > 20 minutes.

Examining the execution plans shows that they are drastically different. For one thing, on my development environment the query is parallelised whereas on the live server it is not.

• I have rebuilt the indexes and regenerated the stats.
• I cannot account for the massive discrepancy.
• The servers' DOP are the same.
• The Live server's CPU is getting hammered 100% during the execution of the query.

I'd appreciate any insights into the cause or how to best go about diagnosing the problem.

DECLARE @geoBoundary geometry;
SET @geoBoundary = geometry::STGeomFromText('POLYGON((407439.5 108792.25, 408022.5 108792.25, 408022.5 108575.75, 407439.5 108575.75, 407439.5 108792.25))', '0');

SELECT
     ogr_geometry.ToString() AS strGeometry
    ,ogr_geometry
FROM inspire as geo
WHERE geo.ogr_fid IN
(
    SELECT
        geo.ogr_fid
    FROM .inspire as geo
    WHERE
    (
        (@geoBoundary.STContains(geo.ogr_geometry) = 1)
    )
    UNION
    SELECT
        geo.ogr_fid
    FROM .inspire as geo
    WHERE
    (
        (@geoBoundary.STOverlaps(geo.ogr_geometry) = 1)
    )
)

• The cost threshold for parallelism on both servers is 5 (the default).
• The development instance has 4 physical cores, 8 logical; the live instance has 8 virtual cores.
• max server memory is the same on both.
• Development instance is SQL Server 12.0.4100.1; Live is SQL Server Web 12.0.2100.60
• There is a huge disparity between actual and expected rows. However, this remained after rebuilding the statistics.
• I cleared the plan cache. It keeps compiling the same plan.
• Execution plans can be downloaded from here.

Answer 1

Comparing details from the two execution plans provided:

• Slow plan was compiled on build 11.0.2100.60 - SQL Server 2012 RTM
• Fast plan was compiled on build 12.0.4100.1 - SQL Server 2014 SP1
• Slow plan uses the version 70 cardinality estimator
• Fast plan uses the version 120 cardinality estimator
• Slow plan has estimated available DOP of 2 (half the visible CPUs)
• Fast plan has estimated available DOP of 4 (half the visible CPUs)
• Estimated memory grant available is also different between the two
• Parameter values used during compilation are identical

With so many fundamental software and hardware differences, it is not really surprising the plans are different.

Ideally, development and test environments should match production as closely as possible. They should certainly be on the same version of SQL Server (and note the 2012 installation there is still at RTM, two full Service Packs and seven further Cumulative Updates behind current.

Without a statistics-only copy of the schema involved in the query, it is hard to know what to suggest to improve the 2012 query plan as it stands. If you are comfortable using hints, you might try adding:

OPTION (LOOP JOIN, HASH JOIN, CONCAT UNION, QUERYTRACEON 8649);

at the end of the query to encourage the optimizer in the general direction of the better plan. The last part of that hint collection (setting trace flag 8649 for the query) is undocumented and not supported for production.

Those hints may or may not be successful, since the underlying issue is the inaccurate cardinality estimation provided by the 70 level cardinality estimator. Query rewrites or indexing changes might be successful as well, but it's really not practical to guess on that from here.

Related reading:

Forcing a Parallel Execution Plan
Different Plans on UAT and PROD
Multiple Plans for an "Identical" Query
Different Plans for "Identical" Servers