Click here to Skip to main content
14,733,333 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
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

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!
   
Comments
[no name] 20-Apr-11 10:55am
   
good one.
thatraja 20-Apr-11 13:53pm
   
Good links Nij. But I don't know who voted 3 for this :Doh:
My 5!
RaviRanjanKr 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)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900