• The execution plan would be a good start, but I can imagine that this would be difficult to read with such a large stored proc.

    But why don't you want to use profiler? Just setup a server side tracing with a filter for this stored procedure and the stements within. Run profiler for just the time this stored procedure is being executed (or executed multiple times to get better analysis on the results). You could even do this on a testing environment.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **