SQL Server Performance

Forcing an entire DB into ram

Discussion in 'SQL Server 2008 General DBA Questions' started by mbuckhurst, May 12, 2009.

  1. mbuckhurst New Member

    We've got a database server with lots of spare RAM, is it possible to force MS SQL to load an entire database into RAM, to aid performance, we're looking at doing this to solve a performance issue in the short term to give us enough time to re-visit and fix the code.
    thanks
    mike
  2. FrankKalis Moderator

    Welcome to the forum!
    There used to be a command DBCC PINTABLE which you could use to prevent the engine from flushing data pages from memory, however this won't have any effect any longer on SQL Server 2008 (if it hasn't been removed at all from that version).
    In most cases you'll find that the "hot pages" are in the buffer pool anyway, so what is your actual performance issue you are trying to solve by loading the db into RAM?
  3. moh_hassan20 New Member

    As you have enough RAM , if you want to keep your data in the bufferpool for long time you should:
    • Set min server memory = max server memory
    • Lock pages in memory using the group policy (for Enterprise Edition)
    Any select statement is cached in bufferpool
  4. deepakontheweb New Member

    if you have enough RAM1. I hope you are using Enterprise or 64bit version of Standard to use unlimited memory; and actual database data size is lesser then server RAM.2. set the 'max server memory' for SQL to use all memory minus -2GB for OS.3. read all tables data once, during non-production working hours; this will bring data pages from disk to memory4. keep checking 'page life expectancy' and 'buffer cache hit ratio'. During query processing..you will be having all data read from cache; not exactly similar to pintable but worth using memory.BTW, what are the server configurations, in term of resources??

Share This Page