MS SQL Server CPU Usage 100%

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,

i am using MS Sql Server 2005. In this server a database is there which have little bit large amount of data in this database structure more then 100 of tables are available. i already created Primary and Foreign keys to all my related tables.
now my problem is that when i run any query which has more conditions with near about approximate from 6 to 8 tables at that time the CPU Usage will go to 100%.

Please Need Help,
thanks in advance.

Posted 20-Apr-11 4:04am

MaulikDusara

Add a Solution

Comments

E.F. Nijboer 20-Apr-11 10:15am

It is good that your cpu is fully utilized because it would be annoying otherwise, but probably this query is running for quite some time? Or is it just a little peak in the line chart? because in that case there is nothing to worry about.

[no name] 20-Apr-11 10:21am

running for some time. need some sql query optimization tips and in my database there is no any special index created only primary and foreign keys are there so if i create index on my few tables which are frequently used, so it will affect the result?

willempipi 20-Apr-11 10:44am

What kind of query are you running and how much time does it take? I have here a database with a table which hold about 100000 records with about 10 foreign keys. When I load the entire table with all foreign keys joined to it, it takes 6 seconds to load 108496 records(about 150 columns). When i add a Where-statement it takes about 30-500 ms.

To get this speed we added primary keys, foreign keys and indexes.

[no name] 20-Apr-11 10:47am

i didn't add index on that tables, so need to set index?

willempipi 20-Apr-11 10:52am

Well, if the preformance is bad, that will be the solution. Take a look at E.F. Nijboer's answer, and read the links he has given, this will clear out the usage of indexes for you.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Accepted Answer · 2011-04-20T04:43:00

If you didn't create a clustered index then this is automatically created for the primary key. It could however boost performance to create an index yourself if your select is not based on the primary index but another column.
http://databases.about.com/od/sqlserver/a/indextuning.htm[^]

There is also a lot of info on performance boosting sql server you can dive into. Have a look at these links for example:
http://www.sql-server-performance.com/[^]

http://msdn.microsoft.com/en-us/library/ff647793.aspx[^]

Good luck!