POST
|
Oh, I noticed that what you were suggesting was a hotfix - not a patch. We don't apply hotfixes unless we know that it fixes a problem - as it suggests, so we probably don't have that loaded. I can check that out as well. Thanks so much for the help.
... View more
06-13-2017
10:56 AM
|
0
|
1
|
646
|
POST
|
Just to let you know. I did try it with MAXDOP set to 1 and it actually does work. I rechecked the execution plan and the old plan actually did have parallelism listed. When I set it to 1, it does not AND it uses the spatial index. And... the query returns in seconds. YOU WERE RIGHT!!!
... View more
06-13-2017
10:54 AM
|
0
|
0
|
688
|
POST
|
I just thought that might work as a test. We have the latest patches on both servers.
... View more
06-13-2017
10:09 AM
|
0
|
4
|
688
|
POST
|
Hmmm... No, the prod server has more cores. But the execution plan doesn't show anything about a parallel plan. I suppose I could configure MAXDOP just as a test, eh?
... View more
06-13-2017
09:19 AM
|
0
|
6
|
688
|
POST
|
Unfortunately, I can't. My systems are on a closed network. What I can tell you is that the query is the same as what you'd get by adding your feature class to ArcMap and doing something simple like panning. Well, I guess the query is small enough to just enter by hand, so here goes: select SHAPE, TYPE, <DB_NAME>.dbo.SDE_GEOMETRY203.CAD from <DB_NAME>.dbo.<BusinessTable> LEFT JOIN <<DB_NAME>.dbo.SDE_GEOMETRY203 on <DB_NAME>.dbo.SDE_GEOMETRY203.GEOMETRY_ID = <DB_NAME>.dbo.<BusinessTable>.OBJECTID WHERE <BusinessTable>.SHAPE.Filter(<Long Hex Number>) = 1 So there you have it. The execution plan for this query on the prod server shows it doing a scan of the clustered index. On the dev server, it makes use of the spatial index. As I said before, if I drop the spatial and create it with a grid size of medium, it does use the spatial index. And yes, I understand that the optimizer will choose what it thinks is the best index based on cost (taking into account statistics, etc.). I just don't understand why it might do this for two identical systems, freshly loaded data and all. I guess there has to be SOME difference there somewhere, but nothing terribly obvious. I've checked SQL Server software versions, database versions (both 110), DB settings and so on. The only thing I can think of that might be different is storage, but the optimizer shouldn't care about that. If I was getting the same execution plan on both and one was performing horribly, I could then look at storage. I suppose I can just adjust the keywords such that it always uses a medium gridsize. I'm not too familiar with doing that. Is there a good write-up on adding keyword/parameter values with some examples?
... View more
06-13-2017
07:29 AM
|
0
|
0
|
688
|
POST
|
Holy Buckets - what a long thread. I came across this because we noticed (SQL server 2012) that we have two servers - one dev and the other prod. On the dev server, we have a feature class (line) with a spatial index (GEOGRAPHY_AUTO_GRID). It performs well on dev, but the identical setup does not on prod. What I've found thru tracing the SQL while doing things like panning in ArcMap is that the spatial index on prod is not being used. If I capture the SQL and use a hint for the spatial index it works just fine. If I do not hint, the execution plan shows that the spatial index is not used. I have found that if I drop the spatial index and recreate it with GEOGRAPHY_GRID and medium grid size, it does use the spatial index. Anyone have ideas there as to why that is the case? We, at least, have a workaround, but I'd like to resolve the issue just the same.
... View more
06-13-2017
05:32 AM
|
0
|
0
|
629
|
Online Status |
Offline
|
Date Last Visited |
11-11-2020
02:24 AM
|