October 23, 2007 at 12:57 am
As we know that plans over 8k are not cache in buffer pool & are cached outside buffer pool, which is called multi-page allocation or MemToLeave.
I am curious to know how i can identify through performance counter or DMV about "proc plans over 8K go to the multi-page allocation(MemToLeave)".
Is there better way to find out. Any feedback is appreciated & thanking in advance.
October 23, 2007 at 5:15 am
dunno if this helps; I've used this query in the past:
select db_name(dbid),object_name(x.objid),* from (
select * from syscacheobjects) x
in an article here on SSC, i read that the columns pagesused and /or sqlbytes you might infer how big an item is?
Lowell
October 23, 2007 at 7:57 am
Jasper Smith once posted this a while back -
"We use a combination of VMSTAT.EXE to get the total free and xp_memory_size
(comes with SQL Litespeed) to get the max contiguous block. We use these to
populate 2 of the user settable SQL counters so that we can monitor the
levels and raise alerts if the max contiguous block falls below 5MB which
allows us enough time to schedule a restart of the instance."
Tommy
Follow @sqlscribeOctober 23, 2007 at 11:42 am
Syscacheobjects queries buffer pool & not out side buffer pool. where MemToLeave is the region outside buffer pool. Syscacheobjects doesn't gets all information about 37 regions within Buffer Pool.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply