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.
EXEC sys.sp_configure N'max degree';
? You may not have access to do that but you should also check the fine print on your contract - Amazon might be rightly limiting you to 1 core based on your service tier. All that said, it shouldn't be a difference between seconds and 20 minutes unless there are other things going on (blocking, excessive wait times on external resources, etc).