Sp:Cachemiss and no sp:recompile

[J?r?my]

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:

[Simon Hayes]

"J?r?my" <es***********@ hotmail.com> wrote in message
news:6e******** *************** ***@posting.goo gle.com...

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:ExecContextH it 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:ExecContext Hit). 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

[J?r?my]

Hi Simon,

Thanks for your comments.

So, what I have in the Profiler:

RPC:Starting --> EXEC dbname.dbo.Mypr oc @Param
SP:CacheMiss --> Myproc
SP:ExecContextH it -->
SP:Starting --> EXEC dbname.dbo.Mypr oc @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:ExecContextH it 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:ExecContext Hit). 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

[Simon Hayes]

es***********@h otmail.com (J?r?my) wrote in message news:<6e******* *************** ****@posting.go ogle.com>...

Hi Simon,

Thanks for your comments.

So, what I have in the Profiler:

RPC:Starting --> EXEC dbname.dbo.Mypr oc @Param
SP:CacheMiss --> Myproc
SP:ExecContextH it -->
SP:Starting --> EXEC dbname.dbo.Mypr oc @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:ExecContextH it
exec dbo.P -- SP:CacheMiss, then SP:ExecContextH it

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

[J?r?my]

Thanks for your answer Simon,
I will check that.