locked
sp_helpindex does not show spatial indexes? RRS feed

  • Question

  • Is there no system SP like sp_helpindex that shows details for spatial indexes in a friendly way?

    (newbie on spatial features here!)

    SQL 2008 R2.

    Thanks,

    Josh

    ps - I see you can select from sys.spatial_indexes but that does not list columns, etc.


    • Edited by JRStern Monday, August 12, 2013 5:25 PM
    Monday, August 12, 2013 5:25 PM

All replies

  • Hello Josh,

    See sp_helpindex (Transact-SQL) => Remarks: "... does not expose information about XML indexes or spatial indexes."

    And see sys.spatial_indexes (Transact-SQL); it returns the same columns as sys.indexes, so you can join on sys.tables and sys.columns


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, August 12, 2013 5:39 PM
  • Apparently the scripting features don't work right either, and if you double-click on a spatial index in SSMS you just get an error message, "index was out of range".

    So, none of these things were ever fixed in any service pack of SQL 2008 R2?  Are they any more finished in SQL 2012?  We're running SP2.

    Josh

    Monday, August 12, 2013 6:15 PM
  • As I wrote, simply query the information

    SELECT *
    FROM sys.spatial_indexes AS SI
         INNER JOIN
         sys.index_columns AS IC
             ON SI.index_id = IC.index_id
         INNER JOIN
         sys.tables AS TBL
             ON SI.object_id = TBL.object_id
         INNER JOIN
         sys.columns AS COL
             ON IC.object_id = COL.object_id
                AND IC.column_id = COL.column_id


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, August 12, 2013 6:34 PM
  • Olaf, that doesn't show all the zippy new features in the spatial indexes.

    But guess what - there ARE such help SPs already!

    http://boomphisto.blogspot.com/2011/04/black-art-of-spatial-index-tuning-in.html

    • sp_help_spatial_geography_index
    • sp_help_spatial_geometry_index

    They're just not well cross-indexed in BOL.

    Josh

    Monday, August 12, 2013 7:12 PM
  • Olaf, that doesn't show all the zippy new features in the spatial indexes.

    Hello Josh,

    What for "zippy new feature"? Like those from sys.spatial_index_tessellations?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, August 12, 2013 7:21 PM
  • Includes those and I'm not sure what else.

    Josh

    propname

    propvalue

    Base_Table_Rows

    4514604

    Bounding_Box_xmin

    -180

    Bounding_Box_ymin

    -90

    Bounding_Box_xmax

    180

    Bounding_Box_ymax

    90

    Grid_Size_Level_1

    256

    Grid_Size_Level_2

    256

    Grid_Size_Level_3

    256

    Grid_Size_Level_4

    256

    Cells_Per_Object

    16

    Total_Primary_Index_Rows

    1481037

    Total_Primary_Index_Pages

    10550

    Average_Number_Of_Index_Rows_Per_Base_Row

    0

    Total_Number_Of_ObjectCells_In_Level0_For_QuerySample

    1

    Total_Number_Of_ObjectCells_In_Level4_In_Index

    1481037

    Total_Number_Of_Intersecting_ObjectCells_In_Level4_In_Index

    1016265

    Total_Number_Of_Border_ObjectCells_In_Level0_For_QuerySample

    1

    Total_Number_Of_Border_ObjectCells_In_Level4_In_Index

    464772

    Interior_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage

    0

    Intersecting_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage

    0

    Border_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage

    0

    Average_Cells_Per_Object_Normalized_To_Leaf_Grid

    0

    Average_Objects_PerLeaf_GridCell

    0

    Number_Of_SRIDs_Found

    2

    Width_Of_Cell_In_Level1

    1.40625

    Width_Of_Cell_In_Level2

    0.005493

    Width_Of_Cell_In_Level3

    2.15E-05

    Width_Of_Cell_In_Level4

    8.38E-08

    Height_Of_Cell_In_Level1

    0.703125

    Height_Of_Cell_In_Level2

    0.002747

    Height_Of_Cell_In_Level3

    1.07E-05

    Height_Of_Cell_In_Level4

    4.19E-08

    Area_Of_Cell_In_Level1

    253.125

    Area_Of_Cell_In_Level2

    0.98877

    Area_Of_Cell_In_Level3

    0.003862

    Area_Of_Cell_In_Level4

    1.51E-05

    CellArea_To_BoundingBoxArea_Percentage_In_Level1

    0.390625

    CellArea_To_BoundingBoxArea_Percentage_In_Level2

    0.001526

    CellArea_To_BoundingBoxArea_Percentage_In_Level3

    5.96E-06

    CellArea_To_BoundingBoxArea_Percentage_In_Level4

    2.33E-08

    Number_Of_Rows_Selected_By_Primary_Filter

    0

    Number_Of_Rows_Selected_By_Internal_Filter

    0

    Number_Of_Times_Secondary_Filter_Is_Called

    0

    Number_Of_Rows_Output

    0

    Percentage_Of_Rows_NotSelected_By_Primary_Filter

    100

    Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter

    0

    Internal_Filter_Efficiency

    0

    Primary_Filter_Efficiency

    0

    Monday, August 12, 2013 11:47 PM