I have a services hosted on IIS port for submit some information and when it is calling by thousand of users using mobile app on same time, Server is going to stuck and not able to response for the request. At this time I observe in task manager that SQL took high utilization and memory approx. 70-75 % CPU and memory . Due to this we need to restart the SQL server daily in morning and evening .(I know this is bad idea for performance and statistics but server hang up)
I have made the API using .NET framework and SQL server 2012.
Any idea what i can do to handle this issue?
The following methods can usually be taken to solve this problem.
Optimize the code of your application, especially the frequent connection and closing of the database. Each new connection will consume cpu resources, so if there is an idle connection, it is better to actively close it instead of waiting for GC to recycle it. For data queries, use index queries as much as possible, especially when there is a lot of data. I think this applies to you, because you will have a large number of users and user data.
Due to the special design mechanism of SQL Server, 100% of the digital memory on the server will be consumed by default, which will cause performance degradation. You can set max server memory to limit the amount that SQL Server allocates to the buffer pool, which is usually the largest memory consumer.
Effects of min and max server memory
As Lex li said, moving the database to a separate computer is a good way. Especially when there is a lot of user data and data processing. IIS and SQL Server are on the same machine. The server not only has to process requests and responses to applications, but also allocate resources to SQL Server to process queries. It is easy to encounter performance bottlenecks.