CacheMiss follow by ExecContextHit

• Judy Why

  Hall of Fame

  Points: 3656

  More actions

  July 4, 2008 at 12:55 pm

  #71667

  Hi all,

  I am kind of stuck here to figure out why my SP got CacheMiss and no recompile.

  our company has a small Stored procedure in SQL Server 2000 Sp4 like below: this sp is being used by webservice applications.

  Create Procedure [dbo].[usp_get_webservice_orderid]

  @orderid varchar(50)=''

  AS

  SET NOCOUNT ON

  select order_ID from v_webservice_details where '{' + cast(ws_key as varchar(36)) + '}' = @orderid

  Go

  ws_key in the original table is unqieidentifier type.

  when I ran SQL Profiler, I noticed that all the applications which is calling usp_get_webservice_orderid

  got CacheMiss, then following ExecContextHit, no recompile.

  here is textdata: exec usp_get_webservice_orderid '932DE870-762D-4EA7-9191-FFB39A775751'

  when I ran this sp with same parameter in QA, I got Cachehit with/without user(dbo).

  -- exec usp_get_webservice_orderid '932DE870-762D-4EA7-9191-FFB39A775751'

  -- exec dbo.usp_get_webservice_orderid '932DE870-762D-4EA7-9191-FFB39A775751'

  I have checked master.dbo.syscacheobjects by running

  Select * from master.dbo.syscacheobjects where dbid=5 and sql like '%usp_get_webservice_orderid%'

  the result is "Cacheobjtype : Executable Plan"

  thanks.

• SQL ORACLE

  One Orange Chip

  Points: 27807

  More actions

  July 8, 2008 at 12:03 pm

  #839705

  How much memory is there in your server? Did you have any chance to read the articles in the following links?

  http://msdn.microsoft.com/en-us/library/ms189103.aspx

  http://bytes.com/forum/thread80203.html

• Judy Why

  Hall of Fame

  Points: 3656

  More actions

  July 10, 2008 at 12:59 pm

  #841063

  this is a dedicated server. ram is 8G. 4 CPU.

  Only this SP got cachemiss, others are ok.