Hate to say it, but if you can't get on the box and check out task manager, perf mon and run various sql server system stuff like dbcc sqlperf, etc,...

Harley, are you actually OBSERVING high CPU utilization during these periods of poor performance? 

Since you have win2k3 sp1, are you by chance copying large files to slow drives?  One of...

Irfan, you make a good point about the optimization cost/time which I don't think has been mentioned as of yet.

However, the "loops . loops . loops" statement is a bit...

Can you simply rearrange your job schedule or the application run time so they don't conflict?

1) When things go to hell, quickly run sp_who2 active to get a list of all connections.  Check the BlkBy column for blocking.  DBCC inputbuffer(spid) will tell you who is...

Recommended by lots of sources in my experience and readings, although the number of joins as a target max varies from 4-8.  I don't think it is a matter of...

How much do you want to bet that the mechanism for this passing of data will be XML based??  HAH!!!  Who cares if TDS or some other binary mechanism is...

To my knowledge you cannot guarantee from one instant to the next that a spid that currently shows blocking other stuff will not be committed/rolledback and the spid reassigned to...

I don't believe you can GUARANTEE not killling a spid that originally shows up as deadlocked but then gets whacked by sql server and the spid allocated to another process...

There are a number of white papers on Performance Tuning topics that show up on a search of microsoft's site.

SQL Server Internals (for 2k and 2k5) by Kalen Delaney.  Ken...

1) if you are running 64 bit HP boxes, they have a VERY nasty bug in their integrated Lights Out stuff that will flush RAM. 

2) Win2003 server 64 bit...

My gut feel is that this is going to be a poor performer no matter what you do. 

1) Too many joins to be efficient

2) Use of both distinct and...

There are a vast array of things that can be causing occassional paging, including driver/os bugs (of which I ran into one of each recently!).  A bit tough to solve...

IMNSHO, option 2 will be the best performer over all.  Refinements include only sending enough Order Header info to the initial grid to allow the user to select the order...

Without seeing any code, table scripts, sample data and table row counts we really can't EFFECTIVELY help you.

Given that disclaimer, if I were you I would rework the entire sproc...