Is it possible to keep my database in RAM????

  • Is it possible to keep my database in RAM????

    Regards,
    Saravanan

  • Yes , have a database thats total size less than available ram on the machine.

    Can you clarify your question further ?



    Clear Sky SQL
    My Blog[/url]

  • Like pin a database ??

  • Saravanan T (1/18/2010)


    Is it possible to keep my database in RAM????

    Having performance issues, Saravan? Can you go into some more detail please?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Saravanan T (1/18/2010)


    Is it possible to keep my database in RAM????

    I agree with Chris Morris.You need to give more info.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • I have a RAM of 32 GB. We are maintaining around 80 databases. But the priority is for only 3 databases. I want all the pages in RAM for those 3 databases instead of all the 80 databases.

    The database size is just 3 GB. Hope u got my question??? I feel its possible to keep all the pages of a particular database in a RAM!!

    Regards,
    Saravanan

  • If those databases are used frequently then the data pages that are often used will be in cache.

    There's no way to prioritise memory usage between databases on the same instance. Only way to do that is to split the databases over two instances and configure the memory so that the one that has the priority databases has more memory.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Couldn't one just 'pin' the tables in the specific database and that would hold them in memory? I have never done this but I have heard of it.

  • Markus (1/20/2010)


    Couldn't one just 'pin' the tables in the specific database and that would hold them in memory? I have never done this but I have heard of it.

    No. DBCC PinTable is a No-operation in SQL 2005/2008. The command's there, it does nothing.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • OK, thanks. I did not know that it was depreciated in 2005/08.

  • Yes, pinning tables was done prior to SQL Server 2005. I think the reason it was depricated is that it was decided that SQL Server should have complete control over how memory is managed.

    In my mind, I'm think if a few small databases are stealing significant resources from the higher priority databases they should probably be put on seperate servers.

    If you just want to manage the RAM given to a few high priority databases as opposed to the lesser priority databases, you could run seperate instances of SQL Server on the same machine and set limits on how much RAM each instance is allowed.

    If I understood you correctly, the server has 32GB of RAM and the largest databases are about 3GB in size and you suspect memory pressure problems. The first thing that comes to my mind there is to ask whether the server is 64 bit or at least using PAE? If the server (hardware, OS, and SQL Server) is not 64 bit (or you're not making good use of PAE) SQL Server will only see about 1.75GB of RAM no matter what you do or how much more than 4GB of RAM you have.

  • Yet another vote to separate the databases. Separate Instances at the very least as Gail suggested, and if possible move them to separate machines altogether.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • My server is Win 2008 Standard edition 64- bit. Iam not getting any memory issues. But still i want to increase the performance. All the database are frequently used , i know it will be in cache as gail said, But i don't want less priority databases data's in cache. Is there any way without splitting the instances. Is there any .Net code to keep databases in cache.???

    Regards,
    Saravanan

  • Saravanan T (1/21/2010)


    But still i want to increase the performance.

    Then look at tuning your queries and indexes. That's usually the largest return on time spent

    Is there any way without splitting the instances.

    No

    Is there any .Net code to keep databases in cache.???

    No, and CLR code that messes with SQL's memory is classified unsafe, and usually is. Mess up and you can crash SQL or even corrupt your data

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thks gail.

    Regards,
    Saravanan

Viewing 15 posts - 1 through 15 (of 19 total)

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