Post reply

allocating memory for execution plan cache

  • December 4, 2009 at 12:39 am

    #218195

    Hi Folks,

    I was just wondering if you could allocate memory to the execution plan cache. My understanding is the server option min/max server memory is for buffer cache. My parsing of queries take a while so I am interested in configuring this better.

    ta

  • December 4, 2009 at 12:47 am

    #1087518

    The max/min settings are for the buffer pool. The buffer pool consists of the data cache, the plan cache and some other much smaller caches. The allocation of the buffer pool among those caches is controlled by SQL and is not configurable.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 4, 2009 at 3:31 pm

    #1088069

    thanks.. so how can I decrease the parsing time of my queries if I cannot configure plan cache memory - would decreasing my buffer cache allow more room for plan cache?

    I am also getting what I would consider a low plan cache hit ratio (round 85%)

  • December 4, 2009 at 10:35 pm

    #1088113

    bodhilove (12/4/2009)

    would decreasing my buffer cache allow more room for plan cache?

    As I said, the buffer cache (usually called the buffer pool) is divided into the data cache and the plan cache (and a few more smaller caches). The plan cache therefore is part of the buffer cache. Reduce the buffer cache and you reduce the size of both the data and the plan cache

    I am also getting what I would consider a low plan cache hit ratio (round 85%)

    Let me guess, lots of ad-hoc, non-parameterised SQL?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 6, 2009 at 4:06 pm

    #1088301

    Haha.. yes they are using sp_executesql quite a bit.

  • December 7, 2009 at 12:47 am

    #1088392

    Then you need to look at the cause of the problem, not the symptoms of the problem. Move as much as possible from dynamic SQL to normal stored procedures. Where dynamic is needed, ensure that it's parameterised.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 7, 2009 at 7:51 am

    #1088538

    I will be there are other reasons cache hit ratio is poor. I also bet there are other performance-related problems here too.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • December 14, 2009 at 2:46 am

    #1091818

    If you have a moment, run the following and post the results please.

    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

    It would also be very useful to know how much memory is dedicated to SQL Server, whether it is 32-bit or 64-bit, whether it is Standard or Enterprise edition, and what version and edition of Windows it is running on.

    You might like to look into Forced Parameterization too.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 8 posts - 1 through 7 (of 7 total)

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