Post reply

How to know when to run FreeProcCache

  • March 15, 2011 at 3:36 pm

    #1299144

    Actually freesystemcache('sql plans' ) removes adhoc plans.

    ( hit the post button by accident, see next one )

  • March 15, 2011 at 3:41 pm

    #1299146

    Ah, it's freeSystemcache('sql plans') I kept looking for options that can be used with freeProcCache.

    Now when I run the query below I get the output below. But the 12,000 single use plans I've been focusing on all show "Prepared" as the object type, not adhoc. Added Excel attachment since data below doesn't format )

    select objtype, count(*) as number_of_plans,

    sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,

    avg(usecounts)as avg_use_count

    from sys.dm_exec_cached_plans group by objtype

    objtypenumber_of_planssize_in_MBsavg_use_count

    UsrTab5032330

    Prepared 3677573984806

    View47039772

    Adhoc15992096804

    Check7123473

    Trigger209

    Proc1335366942

Viewing 2 posts - 31 through 31 (of 31 total)

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