SQL Plan caching question

Question

SQL Plan caching question

CoetzeeW

Ten Centuries

Points: 1361
More actions
May 12, 2008 at 4:34 pm

#187663

Hozit guys
I have a question reagrding looking at certain event's in sql profiler.
Background:
Did a trace last week and found many sp:cachemiss in the profiler trace.
Most of the statements are procedures that the execute a insert or delete or select command.
When I reviewed the profiler indepth and I tested this on a sepearate machine with my own code I found that SQL server always does a sp:cachemiss then a sp:cachehit on the execution context(eventsubclass).
Now I feel there is not realy a problem here since the same plan is being used just a different execution context, but why the sp:cahcemiss first ?
What I found was:
sp:cachmiss will show "Usp_Insert 'Wynand'"
sp:cachehit (execution context with the same plan)
All the stored proc's USp_Inerts (variable) do this and the delete and select stored procedure.
The is nothing special about the insert proc (the delete is just as simple and the select proc)
Insert into table
values(@1,@2,@3)
can some one explain this ?
I even do a batch with 100 executions of the same procedure and still sp:cachemiss and the sp:cachehit
thanks
w

Viewing 5 posts - 1 through 4 (of 4 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Probably because the objects are not fully qualified. (dbo.usp...)

What's probably happening is that SQL's first looking for the object in the current user's default schema, not finding it, then looking in dbo schema.

It is recommended that all calls to objects (tables, procs, functons, views) are qualified wth the schema name.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

CoetzeeW Ten Centuries Points: 1361 More actions · Answer 2

Hi

I thought so , but at 1 AM in the morning I did not try this ...I will test thanks

CoetzeeW Ten Centuries Points: 1361 More actions · Answer 3

Hi

Ok something very weird on SQl 2005.

When I do the same on SQL 2000 with the qualified names then I only get execution context hits 🙂 .

But on SQL 2005 I get a cachemiss first then a execution context hit ...

found something on the net though

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

this is my trace information

234Exec dbo.USP_WYNAND 'monday','wynand','grehory' (cachemiss)

338NULL (execution context hit)

412Exec dbo.USP_WYNAND 'monday','wynand','grehory' (batch completed)

Now I found the following:

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

Still doesn't realy answer my question in case I am a idiot...I tried the obove and it works but it doesn't solve the issue of the sp:cachemiss first ..

Create a simple table , add some data and create a procedure passing a variable and see for yourself...

I see in the plan in cache but the whole create procedure statement is in cache....

if I do the following

Declare @NAME VARCHAR(10)

Set @NAME = 'Wynand'

SELECT day, NAME, SURNAME FROM TEST

WHERE NAME = @NAME

profiler stats

get a cachehit all the time ,I do not get a cachemiss ....so are some queries better off executing as an adhoc statement then a stored procedure ?

JKG SSCommitted Points: 1563 More actions · Answer 4

I was puzzled about this too, but I think what's happening is that the proc is being cached, but the statement calling the proc isn't.

I created a simple proc (ie create proc myproc as select * from t1). First time I call it (using exec myproc) I get SP:CacheMiss followed by SP:CacheInsert. This insert is the plan for the proc being added to the procedure cache.

When I run the proc again, I'll get a cache miss followed by a cache hit. The cache miss is for the statement 'exec myproc' - this is a trivial query so it doesn't get a plan in cache; there'd be no gain in adding it. The cache hit is for the proc - if you look in the ObjectName column, you should see the name of the proc there.

If I run a select statement followed by the exec - ie

select * from t1

exec myproc

I'll see the plan for this being cached (ie SP:CacheInsert); I'm guessing that this is a sufficiently complex query that there's value in saving the plan.

Happy to be corrected if I've got this wrong, but that's what seems to be happening (based on running a few simple tests and a fair bit of googling)!