I am using
SQL Server 2014 in AWS and the AWS image configuration is 4 cores and 16GB RAM(
m3.xlarge). I am running the following simple query,
SELECT * FROM user_table WHERE user_id = '10'
user_table contains 1000k records,
user_id is primary key. When the above simple query is executed from my application through EJB hibernate, the CPU spikes to 10% for a moment and again it backs to normal.
So my use case is, 100 users will concurrently try to hit the application, so in a fraction of second 100 times the above query will try to execute in a fraction of second. So the CPU usage spikes to 100%. Once all the query execution is completed the CPU usage is back to normal at 1%.
- Why it so? whether I need to increase my AWS instance type?
- What should I have to do in-order to make SQL server to handle 100 or more concurrent hits without making high CPU usage? If my query is so complex then there might have a chance to get spike but my query is simple and straight forward.
- Is there is any bench mark metrics available for SQL server 2014?
Any solution to make support for concurrent hits with SQL server by low CPU usage consumption?
One more information my data file size is around
32.2GBand log file size is around
894mbfor my database.
My DB has the isolation level of
READ_COMMITTED_SNAPSHOT is set to ON. But when I tried by setting
READ_COMMITTED_SNAPSHOT to OFF, there is a difference of 20% performance improvement but not that much considerable performance improvements.
Similarly I faced another issue I posted over here. Consider this too for analysis(Some hint may arise from the question in the link).