"J?r?my" <esteban_calle@hotmail.com> wrote in message
news:6e7e2caf.0311100245.4f0d6e63@posting.google.c om...
> Hello:
>
> The installation details:
>
> W2K SP4, SQL Server 2000 Ent with 1GB RAM. It is a Bi-P3.
>
> When I run the Profiler to trace Stored Procedure performance, I get a
> bunch of SP:CacheMiss for couple of stored procedure I invoke quite
> often in a web app.
> But I do not see SP:Recompile.
>
> Here are my questions:
>
> i) If the plan is not in the Cache, why am I not see SP: Recompile.
> Where else can it be tugged.
> ii) What are the other counters I need to monitor to see if I need more
> memory.
>
> Thanks in advance for any leads on this.
>
> Regards:

Are you also seeing SP:ExecContextHit or SP:CacheInsert right after the
SP:CacheMiss? If so, one possibility is that your stored proc is called
sp_Something but is in a user database. In that case, since the name beings
with sp_, MSSQL looks first in the master database but doesn't find the proc
(SP:CacheMiss), then it tries the user database and finds the proc
(SP:ExecContextHit). If that isn't the case, then perhaps you can give more
information about what the proc is doing, which database it's in, exactly
which SP events you see when you execute the procedure, and in what order?

Regarding memory, have a look at "Monitoring Memory Usage" in Books Online,
as well as this page:

http://www.sql-server-performance.co...ers_memory.asp

Simon