Gets SP:CacheMiss everytime for a SP (SQL Server 2005)

• Manish Sinha

  Ten Centuries

  Points: 1023

  More actions

  November 11, 2008 at 3:14 pm

  #215160

  I am doing some analysis on my application database.The problem I am facing is that in SQL Server Profiler,I always get the SP:CacheMiss.

  My Test procedure is:

  Create Proc dbo.pr_testselect

  as

  Select 1

  And the execution statement is:

  Exec dbo.pr_testselect

  As you can see that I have kept in mind all the points that lead to reusing of the cached plan (like sp name not starting with 'sp', complete qualified name,case).Also, after the CacheMiss I get cacheInsert first time.Next onwards its CacheMiss and than CacheHit.

  If it misses it once (that means the sp was not cached), then how it gets back to hit.

  Thanks in advance for your help.

• Grant Fritchey

  SSC Guru

  Points: 398890

  More actions

  November 12, 2008 at 6:53 am

  #897478

  Check this article out

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

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Manish Sinha

  Ten Centuries

  Points: 1023

  More actions

  November 12, 2008 at 7:56 am

  #897549

  Thanks Grant!!!