Results 1 to 5 of 5

Thread: tables in cache

  1. #1
    Join Date
    Jan 2003
    Posts
    12

    Question Unanswered: tables in cache

    Hi Gurus

    I did dbcc pintable for a particular table. Is there are a way to find out whether it is really on cache as the manual says for dbcc pintable.

    Something like IsTableCache(table_name) would be helpful. Or is there any system tables that needs to read to get this info.

    I turned on "set statistics io on" - which explains whether the pages are logical or physical read. I am not able to find out this info if the sql call is made from application. I am using SQL Server 2000.

    Thanks in advance. Any help is much appreciated.

    Rachael

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's not what BOL says...it says, once the data page has been requested, then it will remain in memory....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Posts
    12
    Originally posted by Brett Kaiser
    That's not what BOL says...it says, once the data page has been requested, then it will remain in memory....
    Brett:

    Thas true. After pinning the table data pages will be cached only after the table is read.

    Is there a way to confirm that it is in cache ?.

    Racheal.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What are you pinning btw?

    BOL:

    Caution Although DBCC PINTABLE can provide performance improvements, it must be used with care. If a large table is pinned, it can start using a large portion of the buffer cache and not leave enough cache to service the other tables in the system adequately. If a table larger than the buffer cache is pinned, it can fill the entire buffer cache. A member of the sysadmin fixed server role must shut down SQL Server, restart SQL Server, and then unpin the table. Pinning too many tables can cause the same problems as pinning a table larger than the buffer cache.
    And I can't find anything...and it would have to be at the page level...

    I imagine that if you did SELECT * FROM myTable

    Would oput the whole thing in memory...

    I don't know, I would advise against doing that..

    Any one else ever pin a table?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    Try syscacheobjects to find out about the pinned table

    Originally posted by Brett Kaiser
    What are you pinning btw?

    BOL:



    And I can't find anything...and it would have to be at the page level...

    I imagine that if you did SELECT * FROM myTable

    Would oput the whole thing in memory...

    I don't know, I would advise against doing that..

    Any one else ever pin a table?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •