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

Question

Post reply

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

Saravanan T

SSCrazy

Points: 2506
More actions
January 18, 2010 at 4:58 am

#141814

Is it possible to keep my database in RAM????
Regards,
Saravanan

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

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

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]

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

homebrew01

SSC Guru

Points: 55548

January 18, 2010 at 7:10 am

#1104918

Like pin a database ??

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

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/

Saravanan T SSCrazy Points: 2506 More actions · Answer 5

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 7

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

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

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 9

OK, thanks. I did not know that it was depreciated in 2005/08.

a-brbeck SSC-Addicted Points: 404 More actions · Answer 10

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

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

Saravanan T SSCrazy Points: 2506 More actions · Answer 12

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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

Saravanan T SSCrazy Points: 2506 More actions · Answer 14

Saravanan T

SSCrazy

Points: 2506

January 22, 2010 at 2:05 am

#1107162

Thks gail.

Regards,
Saravanan