May 26, 2011 6:41 am

The other day I encountered a fun issue. When running a stored procedure it would take about 30-50 seconds. However, if I ran DBCC FREEPROCCACHE before running the stored procedure with the exact same parameters, it would run in about 4 seconds. This seemed odd because everything I knew about FREEPROCCACHE said performance would temporarily tank because all the plans have to be recreated. After some digging, I found that inside this stored procedure it called 3 others. All of which modify temporary tables that already exist. Two of which take only a few parameters. You see, those temp tables are how the sprocs get most of their information about what’s suppose to happen. So much so, that one of the sprocs only takes a single bit for it’s parameter. However, the kind of information that might be passed in through those temp tables has the ability to change drastically. So much so that using a bad execution plan caused it to run 6 to 10 times slower. Worse yet, even after running it numerous times it didn’t ever opt to make a new execution plan. Both of the inner stored procedures now have WITH RECOMPILE assigned in their definition to prevent similar issues else where. Interestingly enough executing the outer sproc with recompile on didn’t seem to change anything. I believe this is because the inner sprocs maintained their bad execution plans, even though the outer one was told to recompile. In other words, recompile isn’t a recursive action, which seems correct; if it were it would be too easy for it to fall into a loop. In all cases it would likely over step what you intended.

NOTE: WITH RECOMPILE will cause the sproc to never save an execution plan. Instead a new one is created at every execution. Generally, there is no need to do this, but if the execution plan should vary when the sproc’s parameters don’t, it’s probably a good idea to look into.

Tags: , ,