I currently have two tables cities and listings, both have a field called Position and that is a spatial field. My question is have i set up my indexes correctly or should I add/remove some? For cities I do not show them on a map, I only use one query that is relevant to cities and that is i take a lat/long and i get the 50 nearest cities and for listings I do the nearest as well and I show them on a map like I do here
For both tables I have 2 indexes each that are set as follow.
256 HHHH
/****** Object: Index [Position_Index] Script Date: 1/25/2015 3:56:50 PM ******/
CREATE SPATIAL INDEX [Position_Index] ON [dbo].[Listings]
(
[Position]
)USING GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 256, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
16 HHHH
/****** Object: Index [Spatial_Index] Script Date: 1/25/2015 3:58:23 PM ******/
CREATE SPATIAL INDEX [Spatial_Index] ON [dbo].[Listings]
(
[Position]
)USING GEOGRAPHY_GRID
WITH (GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = HIGH,LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
using sql server 2012
You only need one of the indices for the Position field in each table. Having 2 is redundant and will harm performance from the standpoint that any database writes to those fields would have to update both. You do require having the index in both tables, since you are looking for nearest points in both cities and listings.
In terms of which one to start with, it depends on whether or not you are using points or areas.
For areas (i.e. not just points), you should start with the 16 cells per object one since according to MSDN
By default, the cells-per-object limit is 16 cells per object, which provides a satisfactory trade-off between space and precision for most spatial indexes.
I would apply this to the cities and see how your query performs. If not satisfactory, increase it (probably just keep doubling) until you see no performance gain. I imagine 16 should work just fine for you for the reason stated above, but it will be very data specific. Again, it will only matter if you are not using points.
If your data only contains points (which seems probable in your situation, at least for listings), then the CPO value is not important, and you should get very good performance by setting each level to HIGH as you have already done. From the article here:
In the case of point data, it has been found in most, if not in all, cases that spatial indexes with all grid levels set to HIGH outperform other configurations. Since we are dealing with point data, the CELLS_PER_OBJECT setting is irrelevant and can be set to any legal value (1-8192) without effect.
I would highly recommend reading these articles if you have not already done so: