So far running the optimize unknown hint has resolved the issue, things have been running ok.
The way the code was written was not the best, for now until it...

Indexes are generally rebuilt once a week, we have a several hour window on the weekend.  We try to get through as many as we can in the time frame,...

We have SentryOne montioring, the proc goes from sub second to 30 sec plus, we are capturing anything over 5sec.  None of the failed procs captured did it capture the...

Interesting, that is true this does seem like it follows the pattern of parameter sniffing.
Funny thing is the stored proc was far worse before I forced it to...

We inherited the data structure and as much as we would like not to use nolock they are not going to redo the data structure and the code.
The table...