SQL Server consuming all my CPU

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'

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.

StackExchange Database Administrators Q#16862, answer score: 2