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 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.

Dean Cochrane (3/9/2009)

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.

michael ham (3/9/2009)

---

Well I know it will recompile based on the table activity. I can't understand the 3 minute recompile though. I did catch other recompile events in my trace but only when this sequence above is encountered do we have problems. We are using a temp table and I think we can safely move to a table variable. So I might try that and see if it helps. Thanks.