michael ham (3/8/2009)
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 ofSP: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.
It's recompiling because the schema has changed. This may be due to declaring temp tables partway through the procedure. At least, that's where I've run into this issue. See if moving all temp table declarations to the beginning of the proc stops the recompilations. At least then you'll only have to take the compilation hit once.