none
spatial index not being used (NOT fixed by maxdop 1 or querytraceon 4199) RRS feed

  • Question

  • This is SQL Server 2012 (11.00.3381) running on Windows Server 2012.  It was 11.00.3000 but we tried the newest build to see if it affected anything).

    The spatial index is never being used without a query hint.  As mentioned in the title, I have tried all the usual suspects.  In addition, I have exported the table to multiple other SQL Server 2012 instances (all on non Windows Server 2012 machines), and the same query on the table with an identical spatial index and the problem does not occur.

    --
    --this does not help
    --dbcc traceon(4199, -1)
    --DBCC FREEPROCCACHE

    declare @geom geometry;

    select top 1 
    @geom=shape from  [gis].[PARCELS22]
    order by DC;


    SELECT top 100
    p.[OBJECTID]
          , p.[Shape].STSrid
      FROM [gis].[PARCELS22] p
      --WITH (INDEX([S4_idx])) --uncommenting this fixes the problem
      where p.shape.Filter( @geom  ) = 1

       --this is already set on the instance but I tested the option below with no success
       -- option (maxdop 1)

    To confirm the spatial index is not the culprit I compared the spatial index on the problem server with another server...

     declare @qs geometry;
    select top 1 @qs=shape from parcels22;
    exec sp_help_spatial_geometry_index 'dbo.parcels22', 's2_idx', 0, @qs;

    The results for both are 100% identical.....

    Total_Number_Of_ObjectCells_In_Level0_In_Index 3
    Total_Number_Of_ObjectCells_In_Level1_In_Index 29
    Total_Number_Of_ObjectCells_In_Level2_For_QuerySample 2
    Total_Number_Of_ObjectCells_In_Level2_In_Index 3495
    Total_Number_Of_ObjectCells_In_Level3_For_QuerySample 6
    Total_Number_Of_ObjectCells_In_Level3_In_Index 76365
    Total_Number_Of_ObjectCells_In_Level4_For_QuerySample 7
    Total_Number_Of_ObjectCells_In_Level4_In_Index 264946
    Total_Number_Of_Interior_ObjectCells_In_Level2_In_Index 23
    Total_Number_Of_Interior_ObjectCells_In_Level3_In_Index 1380
    Total_Number_Of_Interior_ObjectCells_In_Level4_For_QuerySample 2
    Total_Number_Of_Interior_ObjectCells_In_Level4_In_Index 28704
    Total_Number_Of_Intersecting_ObjectCells_In_Level1_In_Index 29
    Total_Number_Of_Intersecting_ObjectCells_In_Level2_For_QuerySample 2
    Total_Number_Of_Intersecting_ObjectCells_In_Level2_In_Index 3472
    Total_Number_Of_Intersecting_ObjectCells_In_Level3_For_QuerySample 6
    Total_Number_Of_Intersecting_ObjectCells_In_Level3_In_Index 74985
    Total_Number_Of_Intersecting_ObjectCells_In_Level4_For_QuerySample 5
    Total_Number_Of_Intersecting_ObjectCells_In_Level4_In_Index 236242
    Total_Number_Of_Border_ObjectCells_In_Level0_In_Index 3
    Number_Of_Rows_Selected_By_Primary_Filter 2
    Number_Of_Rows_Selected_By_Internal_Filter 1
    Number_Of_Times_Secondary_Filter_Is_Called 1
    Number_Of_Rows_Output 2
    Percentage_Of_Rows_NotSelected_By_Primary_Filter 99.9940781097326
    Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter 50
    Internal_Filter_Efficiency 50
    Primary_Filter_Efficiency 100

    The geometry column is srid 103562...

    PROJCS["NAD_1983_CORS96_StatePlane_Virginia_South_FIPS_4502_Ft_US",GEOGCS["GCS_NAD_1983_CORS96",DATUM["D_NAD_1983_CORS96",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]],PROJECTION["Lambert_Conformal_Conic"],PARAMETER["False_Easting",11482916.66666666],PARAMETER["False_Northing",3280833.333333333],PARAMETER["Central_Meridian",-78.5],PARAMETER["Standard_Parallel_1",36.76666666666667],PARAMETER["Standard_Parallel_2",37.96666666666667],PARAMETER["Latitude_Of_Origin",36.33333333333334],UNIT["Foot_US",0.3048006096012192]]

    This client is using ESRI 3rd party software, so query hints are not an option.   The only thing I can detect that is unique about this installation (from the dozens I have done internally and for clients) is the OS.

    Help me Obi Wan.  I know you're out there.

     
    Tuesday, November 19, 2013 4:25 PM

Answers

  • By the way, I did post this as a bug....

    https://connect.microsoft.com/SQLServer/feedback/details/809112/spatial-index-not-being-used-not-fixed-by-maxdop-1


    aric mueller

    Saturday, November 23, 2013 10:49 PM

All replies

  • Hello,

    Can you post a screen shot of the query plan for further analysis?
    You can also refer to the following blog about how to ensure spatial index is being used:
    http://www.sqlskills.com/blogs/bobb/how-to-ensure-your-spatial-index-is-being-used/

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Thursday, November 21, 2013 7:03 AM
    Moderator

  • aric mueller

    Thursday, November 21, 2013 2:50 PM
  • Here is the execution plan with the spatial index hint uncommented...


    aric mueller

    Thursday, November 21, 2013 3:15 PM
  • Finally, here are the details on the spatial index seek operation (from the execution plan with the hit enabled)...



    aric mueller

    Thursday, November 21, 2013 3:16 PM
  • The problem is not with the view at all, it's because of the TOP clause. I was able to repro your behavior with a table, and the same query uses the spatial index (on either the table or view) if I leave off the TOP clause.

    The nearest neighbor optimization in SQL Server 2012 (which you have) has a set of requirements, documented here: http://technet.microsoft.com/en-us/library/ff929109.aspx The query that you posted is missing some of the requirements (ORDER BY clause and filtering NULL values). Unless these requirements are met in the view (and you can't have ORDER BY in a view unless TOP is also used), it won't use the spatial index without hinting by definition. You can post your view definition if you think the view helps satisfy the requiements.

    If this is a vendor-generated query, I'd contact the vendor about it. You can always put a hint in a plan guide (separate from the query), but unless you only use a few queries like this (and in spatial that's not usually the case), the plan guide route isn't feasible.

    Hope this helps, Bob


    Saturday, November 23, 2013 9:19 PM
  • Thanks for the reply but there is no view.  This is a simple query against a table which is passing a geometry into a Filter function to pull intersection candidates.  Ignore the top query as that is just to pull a geometry that I know will return records in the bottom query.  The top 100 in the bottom query is not relevant.  If I remove that it has no effect.  It is unprecedented for this type of query to not use the spatial index.  I can use the same table with the same spatial index on every other SQL server instance (2012, 2008r2, standard/express, etc...) and it behaves properly.  As a matter of fact, I can't make any other databases NOT use a properly built index for a simple Filter() call like this.

    I chose this query because it mimics one of the commont queries that the Esri ArcGIS software generates.   That software does not pass hints, nor should it.  I can not make the database use the spatial index on any spatial queries for this table putting the hint in there.  The database is acting like it doesn't know the spatial index is even an option.

    Thanks for your reply.

    -a

     

    aric mueller

    Saturday, November 23, 2013 9:37 PM
  • Hi Aric,

    Rereading your posting, I have no idea why I thought this was related to a view. Ignore this.

    Your key statement is this one: "I can use the *same* table with the same spatial index on every other SQL server instance" (emphasis mine). If you really are talking about exactly the same table working differently with regards to same spatial index on different machines/SQL Server instances, I have seen this problem before. This was reported to Microsoft by the person involved, and they (Microsoft) could not reproduce the problem, even with his exact data, so I don't know if it even "got better" in future service packs or cumulative updates.

    The workaround that this person discovered by accident was, after the spatial diagnostic procs were run against that spatial index on the SQL Server instance in question, the problem did not reoccur. On occasion, reloading the spatial index and rebuilding the spatial index on the machine in question would also help. I you'll need to report this to Microsoft as a bug.

    If I am at all misrepresenting your situation or making an invalid assumption based on your last response, please correct me, and perhaps I could think of something more useful.

    Hope this helps, Bob

    Saturday, November 23, 2013 10:06 PM
  • Hi Bob,

    I am indeed referring to the exact same table, with the same spatial index, and I ran the sp_help_spatial_geometry_index for the table on the "problem" db instance and 2 other "non-problem" instances (for the same input shape) and the results were completely identical (results shown in original post) which is why I'm so confused.  I'm not sure how the execution plan could be different.  Sounds like maybe I should recommend a complete re-install of SQL Server which won't go over well (because this client does not allow us to do the install) but it might work.  Am I correct that you are referring to the sp_help_spatial_geometry_index when you refer to "spatial diagnostic procs"?  If not, what else can I run?

    Thanks again for the reply! 

    -a


    aric mueller

    Saturday, November 23, 2013 10:47 PM
  • By the way, I did post this as a bug....

    https://connect.microsoft.com/SQLServer/feedback/details/809112/spatial-index-not-being-used-not-fixed-by-maxdop-1


    aric mueller

    Saturday, November 23, 2013 10:49 PM
  • Yes, that's what I meant by "run the diagnostic sprocs". Strange...that always worked for the person who had the original same symptoms as you did.

    From what I understand, he backed up with database where it didn't use the spatial index, opened a support case (I saw your next mail message, report as a bug is OK, but opening a support case is the way to go), sent the backup to the support engineer, who restored it and it worked (used the spatial index) on their machine. That's why they couldn't work on it any more, they couldn't repro it. Only ever heard of it once, other than yourself, too.

    I'll check with him to see if he ever got a better resolution, or a more reliable workaround, or .... anything that could be helpful to you. I'll reference the URL for him too to possibly speed things up.

    Cheers, Bob

    Sunday, November 24, 2013 3:31 AM
  • Thanks Bob,

    I've done some more tests tonight with no improvement.  To be safe I tried running the sp_help_spatial_geometry_index a few times and confirmed that it has no effect.  I will open a support case.


    aric mueller

    Monday, November 25, 2013 2:28 AM