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).

  • There are a lots of dmvs that can tell you more about the current state of your server.One of it is : SELECT * FROM sys.dm_os_waiting_tasks msdn.microsoft.com/en-us/library/ms188743.aspx sqlskills.com/blogs/paul/… – Sabin B May 12 '15 at 7:42
  • Is your application server using database pooling? I would guess that the CPU spike is the application pool logging on to the server. You can check this by starting a perfmon trace and checking the Object: - SQLServer:General Statistics - Counter: - Logins/sec Object: - SQLServer:General Statistics - Counter: - Logouts/sec – Spörri May 12 '15 at 9:11
  • I couldn't able to get you. Can you please elaborate your answer. – Jaya Ananthram May 15 '15 at 4:12

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.