I added all SP and batch/statement event in the profiler and had a few problems/questions (i worked with batch statements on a studio session):
1. I can see a "CacheInsert" event - is this also a compilation event? - because i did not see a compilation event.
2. There was no "Duration" or "EndDate" on the CacheInsert - I want to know how long it took to compile.
3. from what I see in CacheInsert the entire batch set of statements are inserted (I assue as a key). This if I understand right means that:
a. select * from mytable1; select * from mytable2
(dbcc dropProcCache not used! - (a) is cached)
b. select * from mytable1; select * from mytable3
(b) will be cacheInserted "fully" - by this I mean that a full compilation (+ cache insert) will occur for (b) despite the fact that we already have "select * from mytable1" in the cache??? If this is true - is this an effiecient way of compiling?
View Complete Post