performance - How can I speed up this Sql Server Spatial query? -

I have a simple SQL server spatial query (which I think):

Hold USA States some four-sided polygon in all (ie. viewport Google / Bing maps to a web page / bounding box)

  select Cast ( 2 as a TINYINT) as LocationType, a.Name FullName, StateId, a.Name, Boundary.STAsText () range, CentrePoint.STAsText (as aS) Centrepoint [dbo]. [States where one] @ BoundingBox.STIntersects (a.Boundary) = 1  

It takes 6 seconds to run: (

Here is the execution plan. ...

deleted

and filter operation statistics ...

deleted

Now, I'm sure how to debug it ... to know that I need a fine tune etc. Is there a spatial index? I believe ...

  / ****** object: index [SPATIAL_States_Boundary] script IV Date: 07/28/2010 18:03:17 ****** / SPATIA Create L. current index [SPATIAL_States_Boundary] [dbo]. [States] ([limit]) using the GEOGRAPHY_GRID (Grid = ( LEVEL_l = high, level_2 = high, level_3 = high, level_4 = high), CELLS_PER_OBJECT = 1024, PAD_INDEX = off, SORT_IN_TEMPDB = off, DROP_EXISTING = off, ALLOW_ROW_LOCKS =, ALLOW_PAGE_LOCKS =) [primary] GO  on < / Pre> 
 I need to provide some more information about  geography  that the data is back? like. Number of points, etc.? Or do I need to run  profiler  and give some figures from there? 
 
 Or my cell_s_object / grid set is wrong (I do not really know that I should set those values, TBH). 
 
 Can anyone help? Please confirm 
 
 UPDATE / EDIT: 
 
 Confirmation after the @boss given below that the local index is not being used because the primary key (cluster) is 50 strange lines With a non-cluster index on a table ... I tried to force the spatial index (for shift Co-en-giggles): - 
 
  select cast (2 As TINYINT), LocationType, a.Name FullName, StateId, a.Name, Boundary.STAsText () range, CentrePoint.STAsText (AS) As in Centrepoint [dbo]. [States] where a well (INDEX (SPATIAL_States_Boundary)) to @ BoundingBox.STIntersects (A.boundary) = 1  
 
 ... and guess .. query runs immediately. 
 
 WTF? Anyone else know why? I need to post a query plan for this, also explain why / what? 
 

It appears that you have an optimal plan to run a query, it will be difficult to improve . Here are some comments.

The question is scanning a clustered index on the PK_States index. The reason this is not using spatial indices is because the query optimizer thinks that it is better to use the clustered index instead of any other index. Why? Probably because there are some rows in the table of states (50 plus some other for Washington, DC, Puerto Rico, etc.).

SQL Server stores and retrieves data on 8KB pages The size of the row for the filter operation (see estimation size size) is 8052 bytes, which means that one line per page and the entire table There are about 50 pages in. The query plan estimates that it will process about 18 of those rows (see estimated number of rows). There is not a significant number of rows to process this. My explanation does not detect additional pages which are part of the table, but the issue is that this number is around 50 and there are no 50,000 pages.

Therefore, why the PK_State index uses the SPATIAL_States_Boundry index, the concept index, includes the actual data of the table, by definition. A non-compressed index indicates the page where the data exists, so there are more pages to retrieve. Therefore, the non-compiled index is useful only when there is a large amount of data.

There are things that you can do to reduce the number of processes of pages (e.g., use a cover index), but your current query is already well-suited and you are very Will not see more performance improvements.