Wednesday, March 28, 2012

Putting entire DB in memory?

I have a db that is about 1.2GB and have 4GB of memory on the server,
which means I can fit the entire db in memory. My question is, if you
have plenty of extra RAM does it always make sense to put the entire db
in memory? Or is there a threshold after which db performance actually
starts to suffer? Also, what do you have to do to fit the entire db in
memory? Is it done automatically by SQLServer?
muramasa
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1053785.html
The data and indexes will be put into memory as you access them for the
first time. They will stay there until you restart the server or run out of
memory (which will not be your case). It is always best to have some memory
left over for the OS and any other apps that may run on the server but sql
server will usually handle that dynamically on it's own.
Andrew J. Kelly SQL MVP
"muramasa" <muramasa.1ou893@.mail.webservertalk.com> wrote in message
news:muramasa.1ou893@.mail.webservertalk.com...
> I have a db that is about 1.2GB and have 4GB of memory on the server,
> which means I can fit the entire db in memory. My question is, if you
> have plenty of extra RAM does it always make sense to put the entire db
> in memory? Or is there a threshold after which db performance actually
> starts to suffer? Also, what do you have to do to fit the entire db in
> memory? Is it done automatically by SQLServer?
>
> --
> muramasa
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message1053785.html
>
|||Hi,
When ever you run a query or manipulate some thing automatically SQL Server
Engine will physically read the data from disk and
put it in Memory. Data will be kept in memory until you restart sql server
service/ rebot server /or if you your memory is fully utilized.
If memory is fully used automatically old piece of data will be moved back
to virtual memory and this is named as swapping.
Other approch to put data is into memory isusing DBCC PINTABLE, but this
approch is not recommended. Becuase this will keep
the data in memory all the time and will not leave room to new data. Best
recommedation is to leave the SQL Server to handle this
by itself.
Thanks
Hari
SQL Server MVP
"muramasa" <muramasa.1ou893@.mail.webservertalk.com> wrote in message
news:muramasa.1ou893@.mail.webservertalk.com...
> I have a db that is about 1.2GB and have 4GB of memory on the server,
> which means I can fit the entire db in memory. My question is, if you
> have plenty of extra RAM does it always make sense to put the entire db
> in memory? Or is there a threshold after which db performance actually
> starts to suffer? Also, what do you have to do to fit the entire db in
> memory? Is it done automatically by SQLServer?
>
> --
> muramasa
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message1053785.html
>

No comments:

Post a Comment