Post reply

Procedure Cache

  • October 5, 2011 at 9:06 am

    #146358

    Hi All,

    I am having 2 databases in one server, Database1 and Database2. In both these databases i am having one common procedure. when ever i am compiling a procedure, the execution plan will saved in the procedure cache. Now my doubt is if the Procedure cache is commom for all the databases in the server or not..?

    If it common, If i restore one database, the procedure cache will effect all the database procedure cache or not...?

    Please give me suggestions

    Thanks in Advance..

  • October 5, 2011 at 9:14 am

    #1390751

    The plan cache is shared between all databases, but procedures in different databases are considered different objects and will have separate plans.

    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

  • October 5, 2011 at 9:24 am

    #1390760

    Thank u ...

    Means for entire server we will have single shared procedure cache, Plans in the cache are database dependent... If we will restore any database that will not effect to another database procedures right....

    thank u

    Su

  • October 5, 2011 at 9:35 am

    #1390769

    Yes and yes. Restoring a database clears the plan cache.

    p.s. It's called the plan cache, not the procedure cache because it caches a lot more than just stored procedures.

    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 4 posts - 1 through 3 (of 3 total)

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