Post reply

Sql 2005 - Can i tell DB to keep a table in memory

  • April 10, 2008 at 7:59 am

    #186378

    I'm a newbie here, so bear with me. Is there a way (what's the syntax) to tell SQL Server 2005. To keep some of my most used lookup tables in memory, or does the DB OS control it.

    Thanks for any Help.

  • April 10, 2008 at 8:22 am

    #801003

    The DBCC command to "PIN" a table in memory was dropped with SQL 2005. It is my understanding that there is no replacement.

    SQL 2005 will retain your table in memory if it really is used enough that your database will benefit from it and it does not cause a memory problem for anything else. I don't know if there was ever much gained by being able to tell the server to not flush the cache of pages from a particular table.

  • April 10, 2008 at 8:31 am

    #801007

    Michael Earl (4/10/2008)

    I don't know if there was ever much gained by being able to tell the server to not flush the cache of pages from a particular table.

    sure there was. inefficiency! 🙂

    ---------------------------------------
    elsasoft.org

  • April 10, 2008 at 8:47 am

    #801020

    Is there a way to tell the DB to reserve ? amout of memory for keeping tables in ram?

    I'm trying to get a handle of estimating how much ram to specify for a new DB Server that we need to order?

  • April 10, 2008 at 8:56 am

    #801026

    You can configure memory to be dynamic or static. If you use dynamic memory allocation, you can specify a min and max. You can configure the amount of memory available for non-SQL processes running within the SQL process (for example memory for ODBC drivers being used in linked servers). There are more, but I cannot remember all of them now.

    The memory available to SQL server gets allocated within SQL by the server for lots of things and you don't have much control over how it is allocated. The good news is that it does a pretty good job of dealing with it.

    As far as getting memory for a new server - this can depend on the database and table sizes, the workload being performed, the features running on the server (SSIS, SSAS, SSRS) and lots of other factors, so it is hard to say. It will also have a big impact if you choose to go the 64 bit route (which I would highly recommend).

    There is, however, no such thing as too much memory and memory is getting pretty cheap.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply