SQL Server consuming all my CPU

Question

I already have a question on gis.stackexchange.com assuming it may be geoserver the problem but as I go deeper into problem I found that I have a database bottleneck. My SQL server CPU usage is almost 100% when geoserver executes following queries :

SELECT "siteId","Vendor",CAST("SP_GEOMETRY".STSrid as VARCHAR) 
  + ':' + "SP_GEOMETRY".STAsText() as "SP_GEOMETRY" FROM "LiveData"."sites"
         WHERE ("SP_GEOMETRY".Filter(
    geometry::STGeomFromText('POLYGON 
    ((87.16003722182671 27.034666490477207, 
      87.16003722182671 28.328562656235395, 
      88.62121886224784 28.328562656235395, 
      88.62121886224784 27.034666490477207, 
      87.16003722182671 27.034666490477207))', 4326)) = 1 
AND ( UPPER("Vendor") LIKE 'A%'  OR  UPPER("Vendor") LIKE 'B%' ))

--

SELECT "Site_ID","Vendor",CAST("sp_geometry".STSrid as VARCHAR) 
  + ':' + "sp_geometry".STAsText() as "sp_geometry" 
FROM "LiveData"."sites"
WHERE ("sp_geometry".Filter(geometry::STGeomFromText('POLYGON ((
  82.89734190991959 26.99551618775358, 
  82.89734190991959 28.367238076413702,
  84.44641417532948 28.367238076413702, 
  84.44641417532948 26.99551618775358, 
  82.89734190991959 26.99551618775358))', 4326)) = 1 
AND ( UPPER("Vendor") LIKE 'A%'  OR 
 UPPER("Vendor") LIKE 'B%' ))

Now the problem is as I open multiple pages just 4-5 pages the web pages hangs as it has to retrieve data from database.

I have a view that is made spatial by selecting spatial column SP_GEOMETRY from base table:

    CREATE view [LiveData].[sites]
      As
     select
     live.Site_ID as siteId,live.Site_Name,live.Vendor,live.Status,live.MI_SQL_REC_NUM,
     live.MI_STYLE,live.MI_PRINX,live.SP_GEOMETRY
     from baseTble.spatialSites live ,LiveData.liveStatus stat 
where 
live.Site_ID  = stat.siteId and stat.Code in ('x123','y345') and stat.alias = 'Neyman'

Answer 1

I suggest you try replacing Filter() with STIntersects(). If the queries are auto-produced by the geoserver, you can test this directly in SQL-Server (so at least you know where the problem is):

WHERE
  ( "SP_GEOMETRY".STIntersects( 
      geometry::STGeomFromText(
        'POLYGON ((87.16003722182671 27.034666490477207, 
                   87.16003722182671 28.328562656235395, 
                   88.62121886224784 28.328562656235395, 
                   88.62121886224784 27.034666490477207, 
                   87.16003722182671 27.034666490477207))',
        4326)) = 1 
  AND
     ...
  )

---

(update)

This page: Filter: One Odd Duck suggests that Filter() is just a fast STIntersects() with the difference that it may return false positives.

It also suggests that the two functions have the exact same (and slow) performance when there is no usable index. So, the best to do is to create a spatial index (and then replace Filter() with STIntersects(), if you want).

The blog (by @Isaac at MSDN) SQL Server Spatial Indexing has 7 nice articles about spatial indexes.

Answer 2

I haven't worked much with geospatial types but the fact that it's CPU-intensive isn't all that surprising. If you want to avoid the CPU pegging out at 100% for the server I'd suggest taking a look at your MAXDOP settings at the instance level. The default value is 0 meaning SQL Server will use up all available processors (logical) when processing. If your query is pegging out all your CPUs and causing issue you may want to experiment tweaking your settings. Take a read of @paulrandal's post on MAXDOP: http://www.sqlskills.com/BLOGS/PAUL/post/MAXDOP-configuration-survey-results.aspx

Word of caution: Don't go changing this willy-nilly in production as it WILL affect performance and the last thing you want is to get yelled at/fired because "some guy on internet said it'd work!". Test your settings in development environment first and see if this works for you.