SQL Server Performance

Store procedure reside in cache

Discussion in 'Performance Tuning for DBAs' started by xiaozhjs, Dec 10, 2003.

  1. xiaozhjs New Member

    Hi,

    Can SQL Server 2000 have ability to reside some store procedure frequently executed
    into cache?

    Thanks!

    Roy
    xiaozhjs, Dec 10, 2003
    #2

  2. gaurav_bindlish New Member

    SQL Server does cache the execution plan of all the stored procedures that are executed and once the memory becomes full, the least used are removed from the cache.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
    gaurav_bindlish, Dec 10, 2003
    #2

  3. satya Moderator

    ... and you can get information from DBCC PROCCACHE.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
    satya, Dec 10, 2003
    #2

  4. bradmcgehee New Member

    Keep in mind that in order for SQL Server to reuse cached plans, that when called, it must be from the same object owner, otherwise a the stored procedure will be recompiled again. Seehttp://www.sql-server-performance.com/stored_procedures.asp for more details.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
    bradmcgehee, Dec 11, 2003
    #2

  5. gaurav_bindlish New Member

    Just to clarify on Brad's point, the procedure should have the same owner. If there is another procedure with different owner and that is executed, new plan will be generated.

    I hope I am correct on this Brad!

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
    gaurav_bindlish, Dec 11, 2003
    #2

Share This Page