Store procedure

Hi ,
Can anybody let me know if a store procedure is taking a high cpu time and high duration to execute , how can we fix this problem.
Thanks,
Rabani

Hi Rabani,
1. Try to select only those columns which are needed, so that some how the performance will be increased.
2. Debug the stored procedure in order to find out the query which is consuming lot of CPU time and try to design that statement in some other way.

Can you post your stored procedure, so that i will reply accordingly..

High CPU can be caused by Hash / Merge Joins, Compilation / Remcompilations which can be due to more records and may be related to lack of indexes as well. High Duration can be caused by Blocking, Doing more than necessary IO (again lack of indexes).
But Duration and CPU always are not interalted say for example if there is a blocking issues on server Duration will increase but not necessarily CPU.
So, first we need to look at
* Whether there are appropriate indexes supporting query.
* If there are indexes is the query taking advantage of the indexes.
Along with query , it is would better if you can share the execution plan to see if these are some of the causes. Or may be run the query to DTA and see for index recommendations.

Other reasons - very simplified:

-- high duration (i/o bound)
select col1 from veryLargeTable

-- high cpu (cpu bound)
select col1, <very complex calcultion> from smallTable where id = 1
-- both
select col1, <very complex calcultion> from veryLargeTable
In general, doing arithmetics in SQL makes it cpu bound, and if it's over large volumes of data and/or a large number of read/write ops, it may also become i/o bound.
/Kenneth