September 29, 2008 at 9:53 am
Hi all,
Just wanted to check something;
I have done the follow;
1. Built a test procedure which just selects a row of data from table
2. I then clear the procedure cache
3. I then start profiler running, it's watching for cache inserts, hits and misses for my user
4. I run the procedure (with owner specified), I get a cache miss, which is understandable as the cache is empty
5. I then get a cache insert, all good so far.
At this point I can also see a record in syscacheobjects, it's type is a proc and it's a compiled plan.
Now I would expect to see a cache hit if I run the procedure again (there is no one else on the server, so there is no need for SQL to empty the cache or anything, data is not changing or anything).
I then execute the procedure again (with owner specified) and I get two rows on my profile, the first a miss, now this is against an object type of 'AQ' which I assume is adhoc, then I get a hit against an object type of 'Proc'.
My question is in how the hit and miss is defined, i.e Is profiler reporting, "oh you missed one plan, but you got another" - hence the two records
or is it saying "no, you categorically missed the cache" or "good shot, you hit the cache", in which case why did I get two records.
Hope this makes sense.
Jackal
September 29, 2008 at 11:38 am
If you execute your procedure for 3rd time and so on, does it keep inserting new row in object cache or reuses one of the existing ones? And which one of them ?
September 30, 2008 at 2:10 am
Hi,
I've attached a screen shot of my profiler results.
No matter how many times I call it I get a miss and a hit, the miss is on the adhoc and the hit on the procedure.
The first one (red) I understand, it cant find a plan, so it misses and then inserts, then when I re-execute the procedure, it misses on the adhoc plan and appears to hit on the proc plan, then repeats no matter how many times I execute it.
Hope this makes sense.
Jackal
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply