Re: HIT and MISS

[posted and mailed, please reply in mail]

kmounkhaty@xxxxxxxxx (smounkhaty@xxxxxxxxxx) 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, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: FOR XML EXPLICIT Over Multiple Stored Procedures
    ... I don't think this is easily possible in SQL Server 2000. ... > To call one stored procedure, eg. sp_Referrals, which in turn calls ... > two stored procedures that each return their own XML structure. ... > exec sp_GetReferenceData 'Titles' ...
    (microsoft.public.sqlserver.xml)
  • Re: sp_is_sqlagent_starting does not work
    ... temporary table and then check the existence of sqlserveragent in the ... INSERT serv EXEC master..xp_cmdshell 'NET START' ... SQL Server MVP ... > From a SQL stored procedure, is there a way to find out if the ...
    (microsoft.public.sqlserver.programming)
  • Re: SELECT TOP with variable not working
    ... > Columnist, SQL Server Professional ... > You can certainly execute the SQL with just the EXEC command inthe ... > stored procedure, but I prefer using sp_executesql, especially since I ...
    (microsoft.public.sqlserver.server)
  • Re: "EXEC" in SQL Server 2000 Views
    ... EXEC as part of a view definition, ... SQL Server MVP ... > the pass-through query. ... I have put the pass-through query in a stored procedure ...
    (microsoft.public.sqlserver.programming)
  • Re: MS SQL 2000 SP Query Plan
    ... EXEC alternate_name ... ... Data may or may not be in cache. ... elements can take up to 15 seconds to compile. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)