SQL Server 2012 CPU usage spikes to 100%

Question

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.2GB and log file size is around 894mb for 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).

Answer 1

What is the exact type of the user_id column?

It is very likely not a VARCHAR column therefore the rules of data type precedence dictate that the comparison must be done by casting the user_id to the type of the '10' literal, thus negating the possibility of an index seek and forcing a full scan. This is a very common mistake.

I recommend reading How Data Access Code Affects Database Performance (do a mental translation for Java - Hibernate) and How to analyse SQL Server performance.