cacheRemove

I have a stored proc with several input parameters and it runs very efficient
(14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
when drastically changing the parameters. I run the same stored proc on my
production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up to
15 minutes. The CPU usage on the production server is very low. When we ran a
trace on production we found that it was removing the cache and then of
course the cache would be missing and it would recompile - sometimes up to 10
or 15 times through the execution of the store proc. It doesn't do the
recompiling on the dev server. We feel certain that there must be a
difference in the configuration of either SQL server between the servers or
Windows. We aren't sure where to start looking.It might be that the connection in which you are running the sp has
different enviorment settings than the dev connection. Run a profile trace
on both with the existing connection and connection Login to see if they are
the same. The sp can be poorly written and force recompilation as well. Do
you have temp tables in it? Have a look here:
http://support.microsoft.com/default.aspx?kbid=243586
--
Andrew J. Kelly SQL MVP
"James" <James@.discussions.microsoft.com> wrote in message
news:76AECD82-262E-428C-B0D9-E03A9FC49BCD@.microsoft.com...
>I have a stored proc with several input parameters and it runs very
>efficient
> (14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
> when drastically changing the parameters. I run the same stored proc on my
> production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up
> to
> 15 minutes. The CPU usage on the production server is very low. When we
> ran a
> trace on production we found that it was removing the cache and then of
> course the cache would be missing and it would recompile - sometimes up to
> 10
> or 15 times through the execution of the store proc. It doesn't do the
> recompiling on the dev server. We feel certain that there must be a
> difference in the configuration of either SQL server between the servers
> or
> Windows. We aren't sure where to start looking.