Post reply

SP:CacheMiss events

  • 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

     

     

  • May 3, 2005 at 5:33 am

    #556491

    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/

  • May 3, 2005 at 6:07 am

    #556499

    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.

  • May 3, 2005 at 6:31 am

    #556506

    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.

  • May 3, 2005 at 6:36 am

    #556508

    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

  • May 3, 2005 at 6:42 am

    #556510

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

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

    and another one in the same line :

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

  • May 5, 2005 at 2:02 am

    #557001

    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

  • February 27, 2009 at 5:36 pm

    #951444

    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://qa.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?

  • February 27, 2009 at 5:47 pm

    #951446

    I've found something that may explain about the issue

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

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

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