CODEWITHSUNDEEP
sql-serverhibernateamazon-web-servicessql-server-2012
Jaya Ananthram
Jaya Ananthram

Reputation: 3463

SQL Server 2012 CPU usage spikes to 100%

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

Edit 1:

Upvotes: 7

Views: 3692

Answers (7)

user4622594
user4622594

Reputation:

if you have a look at your execution plan (in SQL Server Management Studio activate the button "Include Actual Execution Plan") the ONLY things to see when running your query should be:

select 0% <----- clustered index seek (clustered) 100%

If not: Something is wrong with the index(es) on this table. If user_id is unique there should be a unique clustered index on it.

Give it a try ;)

Upvotes: 0

Punter015
Punter015

Reputation: 1796

You can take the below approach:

  1. Index the table on the user_id column if most of your queries are based on the user_id column as the where clause.
  2. As you said, the CPU spikes up every time that query runs it means that CPU cycles are spent on generating a plan for the query which shouldn't be the case as the next time the query runs it should reuse the plans. Check for PARAMETRIZATION!
  3. Do consider if PARTITIONING is an option to apply on the table.
  4. For the hypothesis what would happen if 100 users connect to the instance concurrently executing the very same query i would suggest you to run the SQLQueryStress tool and observe the behaviour of your instance with the help of sp_AskBrent. The results of those would help you to determine the underlying problem or root cause.

Upvotes: 1

level_zebra
level_zebra

Reputation: 1533

if user_id is a BIGINT, should the query not be 

SELECT * FROM user_table WHERE user_id = 10

data conversion can be costly depending on how many times the query is run

Upvotes: 0

JESTIN6699
JESTIN6699

Reputation: 49

Did you indexed your database? If not please index it. Indexing make huge difference in data access time. I thing the lag is not the problem of hibernate. You just index the database and try the query.

Upvotes: 0

smurtagh
smurtagh

Reputation: 529

Did you run SQL Profiler to make sure no other queries are causing the CPU spike?

Upvotes: 0

TonyM
TonyM

Reputation: 186

I would create a clustered index on the key, since everything is stored on a Heap until you define one. Which could result in the High CPU usage for searching (even if it is in memory)

Link to MSDN Article

Specifically

If a table is a heap and does not have any nonclustered indexes, then the entire table must be examined (a table scan) to find any row. This can be acceptable when the table is tiny, such as a list of the 12 regional offices of a company.

Caveat :

Warning Creating or dropping clustered indexes requires rewriting the entire table. If the table has nonclustered indexes, all the nonclustered indexes must all be recreated whenever the clustered index is changed. Therefore, changing from a heap to a clustered index structure or back can take a lot of time and require disk space for reordering data in tempdb.

This SQL should do the trick (Once you've got a nice backed up somewhere)

CREATE CLUSTERED INDEX IDX_UserID on user_table(User_ID)

Normal index should work fine as well, but you should always have a clustered index to sort the data sanely, and then any other high usage indexes.

Upvotes: 4

Jens Schauder
Jens Schauder

Reputation: 81970

It is hard (read impossible) to tell for sure with so little data, but for me this sounds perfect: 100%CPU means sql-server is not limited at all by IO but uses only CPU to perform the query, so it probably finds everything it needs in memory, and it also is able to utilize all CPUs so no bottleneck there either.

So as long as performance is sufficient, there is no need to worry. Of course things might get more interesting once more queries hit the system. One thing I would expect is to things go out of the database cache and therefore CPU load dropping, while IO increasing and performance dropping.

Upvotes: 2

Related Questions