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:

"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

Hi Simon,

Thanks for your comments.

So, what I have in the Profiler:

RPC:Starting --> EXEC dbname.dbo.Myproc @Param
SP:CacheMiss --> Myproc
SP:ExecContextHit -->
SP:Starting --> EXEC dbname.dbo.Myproc @Param
....

The stored procedure that have the same profiling don't begin with
sp_*
but with Misc_* and return a recordset.

Regarding Monitoring memory, I have already checked it, before sending
a messsage to this group, and I didn't find any problem.
The server has 7Gb with 6Gb set to SQLServer (with AWE)
The SQL cache Memory is about 12Mb.

Then I don't know what happened.

Best,
Jeremy

> 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

esteban_calle@hotmail.com (J?r?my) wrote in message news:<6e7e2caf.0311120247.674d4cad@posting.google. com>...
> Hi Simon,
>
> Thanks for your comments.
>
> So, what I have in the Profiler:
>
> RPC:Starting --> EXEC dbname.dbo.Myproc @Param
> SP:CacheMiss --> Myproc
> SP:ExecContextHit -->
> SP:Starting --> EXEC dbname.dbo.Myproc @Param
> ...
>
> The stored procedure that have the same profiling don't begin with
> sp_*
> but with Misc_* and return a recordset.
>
> Regarding Monitoring memory, I have already checked it, before sending
> a messsage to this group, and I didn't find any problem.
> The server has 7Gb with 6Gb set to SQLServer (with AWE)
> The SQL cache Memory is about 12Mb.
>
> Then I don't know what happened.
>
> Best,
> Jeremy
>

<snip>

In that case, another possibility is that you are not calling the proc
with exactly the same name, ie. you have changed the case of the
procedure name:

create proc dbo.p
as
select 1
go

exec dbo.p -- SP:CacheInsert
exec dbo.p -- SP:ExecContextHit
exec dbo.P -- SP:CacheMiss, then SP:ExecContextHit

Changing the case of the database name or owner doesn't result in a
cache miss, but changing the case of the procedure name does.

Simon

Thanks for your answer Simon,
I will check that.