Proc Plans question

Question

Post reply

Proc Plans question

sqldba-294117

Hall of Fame

Points: 3486
More actions
October 23, 2007 at 12:57 am

#119055

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.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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?

[/url]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tommy Bollhofer SSChampion Points: 14940 More actions · Answer 2

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 @sqlscribe

sqldba-294117 Hall of Fame Points: 3486 More actions · Answer 3

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.