467,887 Members | 1,568 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,887 developers. It's quick & easy.

HIT and MISS

Hi Guru,

My profiler trace does not display SP:CACHEMISS event, even thought I
drop store proc, clear both data cache and buffer cache but still does
not work.

Every thing works fine like: cachehit,
cacheinsert,cacheremove,executecontexthit etc...

Is there any special option that I need to turn it on?

Please help,

Thanks,
Silaphet,

Nov 23 '05 #1
  • viewed: 1255
Share:
1 Reply
[posted and mailed, please reply in mail]

km********@yahoo.com (sm********@bremer.com) writes:
My profiler trace does not display SP:CACHEMISS event, even thought I
drop store proc, clear both data cache and buffer cache but still does
not work.

Every thing works fine like: cachehit,
cacheinsert,cacheremove,executecontexthit etc...

Is there any special option that I need to turn it on?


This is in fact a very common event in many systems - and far more common
that it have to be.

Create a stored procedure demo_sp as dbo, and grant exec rights to some
other non-priv user. Then log in as that user, and run the procedure
as "EXEC demo_sp". You will get a CacheMiss every time!

This is because SQL Server first looks up if the user owns a procedure
named demo_sp, in which case this is the one to be executed. There isn't
one, and whence the cache miss.

If you instead say "EXEC dbo.some_sp", you will not get the cache miss,
and this is good for overall performance of the system. Thus it is good
practice to specify "dbo." when you call stored procedures.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by John Bailo | last post: by
8 posts views Thread by ibm_97 | last post: by
18 posts views Thread by Parahat Melayev | last post: by
7 posts views Thread by [Yosi] | last post: by
41 posts views Thread by Mark R. Dawson | last post: by
reply views Thread by manikandan | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.