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.
| Author |
Topic |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2009-12-14 : 19:32:19
|
| In our production SQL Server 2000 , the processor usage is exceeding beyond 85% during peak hours ( stays there for few hours ). This happens only recently after we rolled out a module with several new functionalities.When we did a profiling, 1) We found one stored procedure that had CPU usage greater than 1000. This procedure was called 200 times in 15 minutes. The duration column value for this procedure is between 1000 and 9000.2) Another stored procedure that has been executed more than 1500 times in 15 minutes had CPU column value as NULL but the Duration column value was falling between 2000 and 23000. I have the below questions. 1) Which object is the root cause for the CPU shoot ?2) Why is the CPU column value NULL for a procedure that is taking a long time ?Note : The database is defragmented a week back.Any help would be greatly appreciated. Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2009-12-14 : 19:42:52
|
| Thanks tKizer , but would the procedures that run for a long time consume more CPU ? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2009-12-16 : 12:29:34
|
| How big are your tables?What do you see on the execution plan of those procedures? What's the most expensive part of your procedure?Do you have any of the following:Triggers, cursors, poor written queries, missing indexes, improper clustered indexes, heaps, functions on your joins (i.e. JOIN Table t ON UPPER(t.Field1)=UPPER(FieldA)...)...?-Igor |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-16 : 13:02:08
|
| Look at the execution plan. Most common cause of high CPU is missing indexes.Look for scans, see if index(es) need to be added, or query optimized to utilize an indexmaybe post the query and table definitions (with indexes) here |
|
|
|
|
|
|
|
|
High CPU usage - Inputs needed.