SP:CacheMiss events

Question

SP:CacheMiss events

8adger

Right there with Babe

Points: 768
More actions
May 3, 2005 at 4:44 am

#362522

Hi,
Whilst monitoring one of my severs I note a level of 48% for Cache Hit Ratio. Yes I do mean Cache Hit Ratio, not Buffer Cache Hit Ratio.
Anyway I set up a trace and noted there were a number of SP:CacheMiss events always for the same proc. When I looked into this further I noted there were no recompile events. Can someone explain this?
When I cleared the ruffer and reran the proc there was the CacheInsert but the UseCounts number ine the SysCacheObjects table does not increase when the proc is executed.
Can anyone shed any light on why this might happen.
Thanks in advance.
Jamie

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

These are caused by procs called without qualified names e.g exec myproc instead of exec dbo.myproc OR/AND having user stored procs starting sp_

I suffer both and it can and does cause all manner of problems - it's bad news big time.

I have a similar problem with hit ratio - do you have a lot of dynamic/embedded sql ? The only way forward is 64bit ( or re-write the apps ) < big grin >

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

8adger Right there with Babe Points: 768 More actions · Answer 2

Hi Colin,

Thanks for the reply. I did a bit of research before posting my original post and what you have said rings kind of true as the proc was called 'spCustRollupCalculate', which I thought might be a bit close to sp_ also it was also called without being properly qualified.

I however rewrote and renamed the proc and still I have the same issues. I have pasted the new proc and calling SQL below. Do you have any further thoughts?

CREATE

PROCEDURE dbo.prcCustRollupCalculate (@PfolID int, @MesrID int)

AS

BEGIN

SET NOCOUNT ON

SELECT MemberPfol.CustID, PortfolioProfile.Profile

FROM dbo.Portfolio WITH (NOLOCK)

INNER JOIN dbo.PortfolioMember WITH (NOLOCK)

ON (PortfolioMember.PortfolioID = Portfolio.PortfolioID)

AND (Portfolio.PortfolioID = @PfolID)

INNER JOIN dbo.PortfolioProfile WITH (NOLOCK)

ON (PortfolioMember.ID = PortfolioProfile.PortfolioID)

AND(PortfolioProfile.ClassID = @MesrID)

INNER JOIN dbo.Portfolio AS MemberPfol WITH (NOLOCK)

ON(PortfolioProfile.PortfolioID = MemberPfol.PortfolioID)

END

go

EXEC

dbo.prcCustRollupCalculate 71658,613

Thanks

Jamie.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I've read something interesting here on this site. There's a 3rd way to generate cache misses. You can either have a sp name sp_MyProc (cache miss because not found in master) or not specify the owner of the proc when executing. The third way is a little gotcha : the cached plan search is case sensitive. So if you call dbo.myproc (lower casing) it's not the same as calling dbo.MyProc (camel casing). I don't know if this is your problem but it might very well be.

8adger Right there with Babe Points: 768 More actions · Answer 4

Thanks for the suggestion. But the proc is called in exactly the same casing. I would love to get to the bottom of this.

Cheers

Jamie

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Here's the article... maybe I missed something :

http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp

and another one in the same line :

http://www.sqlservercentral.com/columnists/mmetcalf/hitsandmisses.asp

Tahir-191992 Mr or Mrs. 500 Points: 584 More actions · Answer 6

Hi all

Facing same problem as jamie

but mostly unable to get recompile event in trace even using FreeProcCache .

Still waiting for some more tips to resolve this issue.

Tahir

Kindest Regards,

Tahir

Tien Tran SSC Veteran Points: 222 More actions · Answer 7

I have same problem with cachemiss occurs with sp even though the sp does not start with sp_ and it is called the same everytime.

Putting an adhoc query right after calling the sp in the same batch as suggested at

http://www.sqlservercentral.com/blogs/michael_coles/archive/2006/08/09/the-great-sql-2005-sp-cachemiss-tery.aspx

and I don't get cachemiss

But the sp is called from within our web apps .... therefore I will get a cachemiss everytime...

I noticed that in the sql column in syscacheobjects contains the content of the sp. Is it true that SQL will look for the name of the SP first and generate a miss since I can't find in the cache?

Tien Tran SSC Veteran Points: 222 More actions · Answer 8

I've found something that may explain about the issue

http://sqlserverpedia.com/blog/sql-server-bloggers/spcachemiss-explained/