| Author |
Topic |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2010-01-04 : 14:18:44
|
| Would long running SPs cause high CPU usage ? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-04 : 15:20:01
|
| To diagnose , consider analysing your SPs execution plan - for example , concentrate on scans , and see if you have missing indices , which is a common causeJack Vamvas--------------------http://www.ITjobfeed.com |
 |
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2010-01-04 : 19:01:42
|
| Thanks for your response. In the stored procedure there is a JOIN in which a huge table is joined with few other tables. This is only a clustered index seek which takes 85% cost in the stored procedure. The scan density is very poor and duration for procedure execution is very high ( 25 seconds in production ). Will this shoot up the % processor usage ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2010-01-04 : 20:36:01
|
| When I do a profiling , the duration shows high values but the CPU value is NULL. Have anyone faced this earlier ? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2010-01-05 : 15:10:32
|
| SP:Completed & RPC:Completed. For most of the records the CPU column value is NULL though it has a high duration and reads. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-06 : 01:44:33
|
| set statistics io onset statistics time onexecute sppost results here.as Tara said, most common cause of high cpu is scanning |
|
|
|
|
Long running SPs and CPU ..