Post reply

SQL Server Cache removed

  • August 6, 2011 at 3:36 am

    #254940

    Hi,

    1. Adhoc Cache - This is set of SQL batch contain insert,update and delete statement in one batch, when executing this type batch case of space sensitive transaction also case insensitive transaction.

    2. Prepard Cache - Prepared query submitted through ODBC or OLE DB, this functionality implemented by sp_executesql.

    3. Procedure Cache - stored procedure, storing query execution plan and minimize to create new execution plan.

    which Situation these cache should be removed By using DBCC freeproccache and DBCC freesystemcache?

    Thanks

  • August 6, 2011 at 3:40 am

    #1364746

    Why do you think you are experiencing memory pressure?

    How many plans are being used only once, and for how much ram?

  • August 6, 2011 at 3:48 am

    #1364748

    Generally never, unless you have very specific problems and know exactly what you're doing.

    The only time you'd consider that is with lots and lots of ad-hoc plans causing problems other than memory pressure (SQL can handle that one fairly well itself). Past SQL 2005 SP2, that should be a rare occurrence.

    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

  • August 6, 2011 at 4:06 am

    #1364750

    There is no memory issues.

    total server RAM 4GB.

    I have checked through SQL Server cache manager Tool. It showing as blow output one of the stored Procedure. this one SP using around 1 MB.

    Usecount - 12189

    size_in_bytes - 1253376

    cache Object - Compiled Plan

    Object type - Proc

    and Total Proc cahe is very low.. 264 MB.

    SELECT SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)'

    FROM sys.dm_exec_cached_plans;

    Please tell me, Just I want to know these cache plan really affects memory issues.

    Thanks

  • August 6, 2011 at 4:24 am

    #1364752

    Leave it alone. You have no memory issues and the cache is small. Why do you want to mess around?

    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

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

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