[GIS] GeoServer with SQL server consuming all the CPU

geoserversldsql servertomcat

This question is long please bear me and my problem.
SQL Server Query profiler
The Sample Query from profiler that GeoServer is running that corresponds to SLD I am using and not the SQL SERVER view I am using is:

SELECT "siteId","Vendor",CAST("SP_GEOMETRY".STSrid as VARCHAR) + ':' 
+ "SP_GEOMETRY".STAsText() as "SP_GEOMETRY" FROM "Data"."sites" 
WHERE ("SP_GEOMETRY".Filter(geometry::STGeomFromText
('POLYGON ((80.12878722280502 28.280198584198256, 
    80.12878722280502 29.559126104578176, 81.58996886322707
 `29.559126104578176, 81.58996886322707 28.280198584198256,
 80.12878722280502 28.280198584198256))', 4326)) = 
1 AND ( UPPER("Vendor") LIKE 'A%'  OR  UPPER("Vendor") LIKE 'B%' ))`

I have used JavaScript as suggested by this to redraw pages at 5 minutes interval. It shows 100% CPU utilization when layer is refreshed.
IF I open multiple pages then page just gets locked.

enter image description here

I have Computer with following configuration used for GeoServer, SQL Server and Apache Tomcat. The person who was using it until I demonstrated my project was only me but when I ran this only with 5 person the page just hang(chrome browser) and same thing is happening when I open 5-6 window with same address. I am using GeoServer WMS layer with OpenLayers and Google Maps. The Google map loads very fast but my layer loads very slow 1/2 min. The zooming takes similar time. I have used JAI and all for GeoServer. Database is indexed and the query takes 0 ms when I do it from SSMS. The GeoServer is deployed inside Tomcat and data directory is outside the war. I have problem accessing only map pages and believe the memory requirement is more for maps but I am unable to find actual problem.
SQL Server is actually consuming most of the CPU and Tomcat is consuming most of the memory.

enter image description here

The task manager shows following usage:

enter image description here

The perfmon shows following usage:

enter image description here

Best Answer

This might be due to geometry format requested from SQL Server. If you allow for SQL Server to provide the native format this will be faster than using WKB generated by SQL server.

A way of avoiding this was introduced in Geoserver 2.3.1. Read the release documents for more information.

Related Question