SQL Server Performance Forum – Threads Archive
Is it possibel to put all database into memory
My problems is that our system is running slow – some consulents tells my it is possibel to put all database in memory and making it stay there, so my performance will be much better, my database is 8 GB, but with SQL 2005 and Windows Enterprise it must be possibel to put all database into ram – but how ?[?]I don’t agree with consuntants. That sound like: You need more and more hardware.
If you have performance problems, you have a lot of work to improve performance before begin to think in put all database in memory. Question you have to find out: 1) How is the database, log files, tempdb stored? Differents drives?
2) Run Profiler and find out slow queries and try to optimize them.
3) What kind of maintenance plan do you have each day? Read the following link to get help: http://www.sql-server-performance.com/sql_server_performance_audit.asp BTW:Welcome to the forum. Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
i think it is time to reconsider the value of your consultants,
a nitwit could give such advice
SQL Server put data from db in database cache as much as available ram allows. However, log and data have to be stored on the disk for every change to be able to maintain db consistency. These operations are done asynchronously. So mssql server itself takes care about "having db in memory" as much as it is reasonable. To improve the system performance you have to find better consultants who actually know what they are talking about (agree with joe). For more information refer to link posted by Luis.
not often we see Joe be quite so explicit <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />yes very true though a consultant giving such advice should be marched out of the building…<br /><br />Cheers<br />Twan
On the lighter side, that consultant might have worked for Oracle [8D][<img src=’/community/emoticons/emotion-5.gif’ alt=’
![Wink ;) ;)](styles/default/xenforo/smilies/wink.png)
Actually you *can* pin tables into memory. SQL Server offers such a functionality. However, you’ll find that only a small percentage of all your data is really frequently needed. SQL Server does a really great job keeping "hot pages" in the buffer pool anyway. So there is no need to pin everything into memory. On the opposite, this might prove contraproductive in many cases. Like the others already said, tell your consultant to reconsider his advise (…and probably get a copy of "Inside SQL Server 2000"). [<img src=’/community/emoticons/emotion-5.gif’ alt=’
![Wink ;) ;)](styles/default/xenforo/smilies/wink.png)
… rather ask the consultant have a thorough lookup at that book [<img src=’/community/emoticons/emotion-1.gif’ alt=’
![Smile :) :)](styles/default/xenforo/smilies/smile.png)
]]>