SQL Server Performance

SQL Server Cache Discussion

Discussion in 'Performance Tuning for DBAs' started by itbhushan, Jul 14, 2003.

  1. itbhushan New Member

    I have just gone thru the article relating to the sql servers data & procedure cache & would like to have a discussion & some contribution from other users. Data cache & the procedure cache holds the data pages or the recordset retrieved from the database & the compiled execution plans for all previously-run Transact-SQL statements respectively. Also in order to find out which are the sql queries using large amount of pages in the memory we use "select * from syscacheobjects".
    Also if we come to know that if a table is most frequently used then the same can be placed in the memory by using the "dbcc pintable" command thus reducing the physical io time between the disk & the memory, wanted to know will this table be there in the memory or will it wiped off on stopping the server ?.
    Kindly put in more indept explanation or view about this subject. In case if iam wrong in above statements, please point it out.


  2. vbkenya New Member

    ....wanted to know will this table be there in the memory or will it wiped off on stopping the server?....

    The 'cache' refers to RAM (volatile and dynamic). The table will definitely not be there when the server is stopped/restarted.

    Nathan H.O.
  3. gaurav_bindlish New Member

    I agree with Nathan. The table is loaded into the memory, first time any query is executed on it. This means, the data pages of the table will not be flushed out of the memory even when they are not being used. So if you want the table to be present all the time, make a startup stored procedure which will execute
    SELECT * FROM Table_Name
    This way you can be sure that the table is always present in the memory.

    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  4. satya Moderator

  5. bradmcgehee New Member

    Also, if you search the forum for the dbcc pintable command, you will find several previous threads on this topic.

    Brad M. McGehee, MVP

Share This Page