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 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.
The task manager shows following usage:
The perfmon shows following usage: