September 28, 2005 at 9:50 am
I have inherited a stored procedure with several simple selects using 4 INNER JOINs at a maximum. Each select stores its result in a variable.
At the end of the procedure these variables are returned in a single record using select @var1, @var2 ...@var99
The procedure is used to retrieve data on a particular account in a call centre so it is being hit by several hundred people almost all the time.
In the live environment a trace on the SP:Recompile event shows that this procedure recompiles every 5 seconds.
In my test environment, short of using sp_recompile I cannot get the procedure to exhibit this behaviour.
I would appreciate any pointers as to why I am seeing this recompile activity.
I am tempted to break down the single procedure into a master procedure calling several child procedures but without concrete proof that this is the cause I am reluctant to commit to doing this.
September 28, 2005 at 9:58 am
Check the calling applications to make sure they are not calling using "with recompile".
Also make sure that all the calls made are using the same format >>
With and without the dbo. use 2 different plans, also dbo.MyProc and dbo.myproc will use 2 different plans because the plans are case sensitive.
Check those articles for more details :
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply