title: SQL Server 2012 CPU usage spikes to 100%
body: <p>I am using <code>SQL Server 2014 in AWS</code> and the AWS image configuration is 4 cores and 16GB RAM(<code>m3.xlarge</code>). I am running the following simple query,</p>
  
  <pre><code>SELECT * FROM user_table WHERE user_id = '10'
  </code></pre>
  
  <p><code>user_table</code> contains 1000k records, <code>user_id</code> 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. </p>
  
  <p>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%. </p>
  
  <ul>
  <li>Why it so? whether I need to increase my AWS instance type? </li>
  <li>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. </li>
  <li>Is there is any bench mark metrics available for SQL server 2014? </li>
  <li>Any solution to make support for concurrent hits with SQL server by low CPU usage consumption?</li>
  </ul>
  
  <p><strong>Edit 1:</strong></p>
  
  <ul>
  <li><p>One more information my data file size is around <code>32.2GB</code> and log
  file size is around <code>894mb</code> for my database.</p></li>
  <li><p>My DB has the isolation level of <code>READ_COMMITTED_SNAPSHOT is set to ON</code>.
  But when I tried by setting <code>READ_COMMITTED_SNAPSHOT to OFF</code>, there is a difference of 20% performance improvement but not that much considerable performance improvements.</p></li>
  </ul>