Gail, looks like you hit the nail on the head...

In looking through the reporting procedure I did notice a TVF being called (although it really looks harmless and has a solid execution plan) and noticed some execution plans in the cache that had a very high size_in_bytes value. Looking through one of those plans I noticed a parameter table with about 3.5 million records that was involved in (at least I think it was ) a hash match with data from another index seek operation.

So parameter sniffing could be a culprit - perhaps an bad plan got generated and reused from a small account versus large account and it impacted the rest of the report runs. As when our reporting kicked off this morning it was flying through the reports, but came to a crawl when it hit this specific report....and while some still ran rather quickly, the majority of them took 40+ minutes to execute

Another thing I noticed as extremely high CPU. These same reports were killing the CPU...

As a quick fix (I will change later today) I added WITH RECOMPILE for the problematic procedure, restarted the process, and everything's running fine - after our reports complete in a couple hours I will drill down to the statements inside the proc, and perhaps add an OPTION(RECOMPILE) to the problematic statement or an OPTIMIZE FOR and see if I can correct it

The CPU went down to miliseconds per execution...

Fun Time :hehe: