CPU usage is 100% - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

CPU usage is 100%

Author

Topic

leoiser
Starting Member

20 Posts

Posted - 2007-05-07 : 06:48:05

hi all,
when I click web page (executing some stored procedure generally will take less than 2 minutes) the CPU usage is becoming 100% and taking a long time to run.I already posted a forum before (SQL server 2005 running slow ). I dont know these problems are related.If I restart the server then it will run as ususal.What should be problem.Server Windows 2003 ,SQL server 2005
Is it bcos of any memory lekage or any other reason..

Thanks
leo

Kristen
Test

22859 Posts

Posted - 2007-05-07 : 08:44:43

Sounds like the Statistics are updating and the Stored Procedure is not making a new Query Plan to reflect the changes (although I thought this issue was one of the things improved in SQL 2005)

Instead of restarting SQL Server try recompiling the SProc next time it runs slow, and see if that cures it (and if so maybe schedule the Recompile for once-a-day, or whatever.

Kristen

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-07 : 12:19:59

you can also drop/recreate the proc to clear any previous bad query plans. Also, have any of the tables being used in your proc been modified recently? has any maintenance been done recently?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

leoiser
Starting Member

20 Posts

Posted - 2007-05-08 : 01:25:54

HI,
I created few indexes few weeks back.is it affect the stored procedure performance?

Previously there was no maintenance plan.Now I run the "DBCC DBREINDEX " for all tables & restarted server, running faster.let me check for few days.

Thanks for the reply.

Leo

Kristen
Test

22859 Posts

Posted - 2007-05-08 : 02:07:13

"I created few indexes few weeks back"

That should make things faster. It is extremely unlikely that it will make things slower - except possibly deciding which query plan to use!

How did you decide which columns to index? Are all the new indexes being used?

Kristen

leoiser
Starting Member

20 Posts

Posted - 2007-05-08 : 23:49:12

hi kristen,
I use SQL profiler-->index tuning wizard to create indexes.
I check with my Stored procedure's SQL & order of the index is ok.after that the SP's run faster.

How did you decide which columns to index

I look for the columns which is often used in where conditions.

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 02:36:21

You should use ALTER INDEX in SQL Server 2005.
DBCC DBREINDEX is deprecated. See http://support.microsoft.com/kb/954734/

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-21 : 03:59:36

Bored Peso tracking down old posts?

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 04:16:22

Yes. I try to make updates to post when I come across them in other errands..

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-21 : 04:25:23

I noticed Kristen had answered and thought he was back but realised pretty fast that it was an old post. I also noticed after posting that this thread has been read over 800 times!

- Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources