Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2006
    Posts
    5

    Unanswered: SQL Server 2005 memory management

    Hi all,

    I needed to load some tables in memory on startup because of performance reasons.
    I'm using "select * from <table>", but there are few questions:

    1. How to pin already selected data in memory ? (DBCC PINTABLE doesn't work for 2005)

    2. How to put index data in memory ? (do you read document(s) for advance memory management - index data caching ?)

    3. How to pin index data in memory ? (otherwise sound very bad - table data in fast memory, index data - in slow disks)



    Thanks in advance:

    Siol En

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    frequently accessed index and data pages will already be in memory. it's my understanding they took this out because it was a bad hack because of this. if you need more memory perhaps you ought to see how much your box has, see if sql server is using all it can and perhaps investigate AWE. And tell you the truth when I played with pintable a couple of times, I did not see a great benefit.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - you can't pin them but you could force them into the cache by reading the data on startup.

  4. #4
    Join Date
    Nov 2005
    Posts
    122
    But there is no way to guarantee that it will stay there. The Lazy Writer will free up buffer cache pages based on a reference counter on each page.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes that is correct. However, if the data is frequently used it will of course likely remain in the cache. If it isn't frequently used do you really want it in there?

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    maybe the better question here is what is wrong with your code\design\indices etc... that is making you address a performance problem in this matter.

    if you are having a performance issue, I suggest you read the directions in Pat's sticky above about posting code, ddl etc...
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Oct 2006
    Posts
    5

    Siol En

    Hi all,
    There is no problem with code (design could be optimized, but for this release it is freezed). I should take huge load (1000-2000 hit/sec) and I needed best performance. I can't predict which data will be used, that's why I needed some tables (hugest) and indexes to be permanent in memory. Otherwise my performance decreased more than 5 times. And that will affect my application performance.

Posting Permissions

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