Need a urgent help.
I am developing an application which is supposed to have 2000 concurrent users. It is a .Net based application with Sql server backend.
I have a application server:
Windows server, 20 Core CPU, 12 GB RAM.
Database server: 10 core cpu, 10 GB RAM, sql server web edition with 10 core license.
The problem is:
Initially the application response time for 100 concurrent user is 4 sec.
When above 700 concurrent user logs in, application response time degrades to 45 sec though the CPU and RAM usage for both App and database server is less than 50% utilization. The sql profiler shows that the Stored Procedure which initially took milli second to execute when less users took 4sec when more users logged in.
I am unable to identify the bottle next. It is not CPU and RAM because the utilization is less than 50%. So where is the problem.
I am trying to optimize the Stored Procedure and Coding but it will take more time and I am running out of time. Hence can anyone suggest:
- Any specific settings in SQL or IIS server setting which is capping the server performance
- SQL web edition license needs to be upgraded for more core from currentb10 core license
- SQL server license needs to be upgraded from web to standard or enterprise edition.
Or any other suggestion.
Does any one know a SQL server performance Tunner expert or any person who could guide me.