Proc Plans question

  • 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.

  • 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!

  • 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."

  • 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