Question on Plan Cache

I've got something awry in one of our servers. I'm getting SP:CacheMiss
like crazy in Profiler. The same procs repeatedly. The strange thing is
that these procs are in sys.syscacheobjects and the usagecount is growing
regularly so the cached plans are being used....why would I still be
getting CacheMiss' Some of these procs are only called from one specific
webservice the same way every time. The usage counts on some of these are
over 20k so the plan doesn't seem to be clearing it just doesn't seem to be
getting used every time...
Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET application instead of as a paramterized query? We are trying
to convert everything to parameterized as quickly as we can. But if this is
the cause, why is there a plan that *IS* getting used?
I'm really a bit stuck here and seeing a high number of SQL:Compiles that I
don't believe should be happening.
Thanks in advance!!Possible reason:
EXEC procname
Above is a batch submitted to SQL Server. This is parsed and a plan is gener
ated, but since it is a
dirt cheap plan it isn't cached. Note, I'm not talking about the proc plan,
I'm referring to the
text "EXEC procname". Since it isn't cached, this happens every time you cal
l the proc this way. I'm
tempted to test the difference between execution text and RPC, but since you
are about to do it,
just let us know. I wouldn't be surprised if cache handling is different wit
h RPC calls...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
> I've got something awry in one of our servers. I'm getting SP:CacheMiss l
ike crazy in Profiler.
> The same procs repeatedly. The strange thing is that these procs are in s
ys.syscacheobjects and
> the usagecount is growing regularly so the cached plans are being used...
.why would I still be
> getting CacheMiss' Some of these procs are only called from one specific
webservice the same way
> every time. The usage counts on some of these are over 20k so the plan do
esn't seem to be
> clearing it just doesn't seem to be getting used every time...
> Is it because the are being executed as "exec procname parm1, parm2,..."
from the .NET
> application instead of as a paramterized query? We are trying to convert
everything to
> parameterized as quickly as we can. But if this is the cause, why is ther
e a plan that *IS*
> getting used?
> I'm really a bit stuck here and seeing a high number of SQL:Compiles that
I don't believe should
> be happening.
> Thanks in advance!!
>|||Oh shoot...that is a simple explanation and makes total sense...
Thanks...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:704FC8B8-C95F-489A-A467-ED71F4223BD3@.microsoft.com...
> Possible reason:
> EXEC procname
> Above is a batch submitted to SQL Server. This is parsed and a plan is
> generated, but since it is a dirt cheap plan it isn't cached. Note, I'm
> not talking about the proc plan, I'm referring to the text "EXEC
> procname". Since it isn't cached, this happens every time you call the
> proc this way. I'm tempted to test the difference between execution text
> and RPC, but since you are about to do it, just let us know. I wouldn't be
> surprised if cache handling is different with RPC calls...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:uHAcoQqrHHA.3276@.TK2MSFTNGP04.phx.gbl...
>