I have a development machine and a production machine with identical databases, but I'm getting huge differences in execution plans when running a spatial query.
First, my development machine:
It's SQL Server Express x64 with Reporting Services running on Win7 with 8Gb RAM.
Then my production machine:
SQL Server with Reporting Services running on Windows Server 2008 with 8Gb RAM (x86 though, so only 4Gb usable).
I have a table that contains about 3000+ records containing polygon coordinates. The database that holds the table is identical on both machines. A sample query I'm running on the dev machine executes in a fraction of a second but takes 11 seconds on the production machine. When I looked at the execution plans, I was surprised to see that they are completely different on the two machines.
The query is:
WHERE geography::STGeomFromText('POINT(-86.7535 33.6499)', 4326).STIntersects(Polygon) = 1
On the production machine the execution plan shows 3 steps:
SELECT Cost: 0%
Filter Cost: 97%
Clustered Index Scan Cost: 3%
But on the dev machine the execution plan is much more complex and includes things like "Nested Loops", "Hash Match" and "Compute Scalar". The indexes on the two machines look identical from what I can see and the data is identical. I would expect a performance difference just based on x86 vs x64, but I don't understand the difference in execution plans...
I really don't know much of anything about db optimization, any tips from the experts here?