Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 5:04am
Comments
E.F. Nijboer at 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.
mdusara84 at 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 at 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.
mdusara84 at 20-Apr-11 10:47am
   
i didn't add index on that tables, so need to set index?
willempipi at 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

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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!
  Permalink  
Comments
mdusara84 at 20-Apr-11 10:55am
   
good one.
thatraja at 20-Apr-11 13:53pm
   
Good links Nij. But I don't know who voted 3 for this :Doh:
My 5!
RaviRanjankr at 20-Apr-11 14:38pm
   
Nice Links! My 5 :)

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

  Print Answers RSS
0 OriginalGriff 7,130
1 DamithSL 5,089
2 Maciej Los 4,866
3 Sergey Alexandrovich Kryukov 4,647
4 Kornfeld Eliyahu Peter 4,409


Advertise | Privacy | Mobile
Web02 | 2.8.141223.1 | Last Updated 20 Apr 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100