we have a stored procedure that is periodically causing performance issues. When the procedure is flagged for recompile, it takes 3 - 4 minutes to compile. in Profiler I see a pattern with the EventClass and EventSubClass of
SP:CacheHit --- 1 - Execution Context Hit
SP:CacheHite --- 2 - Compplan Hit
SP:Recompile --- 1 - Schema changed
Once this Compplan Hit sub class happens, the recompile takes forever. I also see CPU utilization on the server hit 40 - 50 while recompiling. This server only has one processor but it is hyperthreaded. Do you think I'm suffering from parallelism? If so, I know I can add an MAXDOP hint to the query but does that help serialize the recompile or will I need to set MAXDOP at the server level? And I should mention that the procedure is join heavy in a big way. There's also some UDFs thrown in for fun.