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) ) )
cost threshold for parallelismon both servers is 5 (the default).
- The development instance has 4 physical cores, 8 logical; the live instance has 8 virtual cores.
max server memoryis 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.