• You can find the longest running couple of procs in sys.dm_exec_procedure_stats, order by 1.0*total_elapsed_time/execution_count desc.

    Have a look at the query plan.

    i.e., find a test case, execute it with View actual plan.

    If you can then give us the actual executed plan ,I am sure there are massive amounts of SQL guru's who will gladly help.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]