Load SQL DB into Memory

burny1 used Ask the Experts™
Hi There

We have a SQL table with around 150 mil lines. to execute a query takes a very long time. Having a look at the resources, ram sits at 25% and CPU less than 10% while disk is at 100%.

Is there a way to load the entire table into RAM and work directory from there?
I assume this will also shorten the duration it takes to execute a query.

Do you perhaps have a link on a procedure where i can set this it up guide me in the correct direction.

Thanks in advance
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
SQL Server manages that on its own.

When you have SQL Server 2014 then you can use memory optimized tables. But this has it traps, especially as it requires new management skills.

But I guess you just need to do some basic query optimizing and indexing. btw, your indices and statistics are uptodate?
Make sure that the SQL server's memory is not limited by the max setting.  Normally you should grant SQL server with 85% of its host memory, providing that you don`t have any critical software running on it, which you should`t in the first place.

SQL Server does exactly what you asked. It will use all the memory that has been allowed to its disposal and load it with the data from the disk. It NEVER works directly with the disk. However, not always it will be able to have a whole table loaded if the table size surpasses the memory size so it will have to go back and forth to the disk for I/O as it needs and THAT is the bottleneck. To prevent that you have to make sure you have the right indexes, that they are regularly optimized by fragmentation processes and that your queries are written in an efficient manner.  These are basically the essentials of any relational database platform.
Walter RitzelSenior Software Engineer

Let's try a different approach: can you share your SQL query, and the complete table structure, with indexes? Most probably there are small optimizations that we can apply to your query.


Not exactly the answer i was looking for but put me on the correct track. I created the SQL indexing which seems to prefer a little better. What was happening is reading the database took 100% of HDD resources. once the SQL table was cached though, it took a fraction of the time to complete the query.
What you describe is a normal and expected behavior. You have to check the response time, latency, at wrttes and reads of the HDD. Writes latency should stay under 50ms, under 20 is optimal and Reads under 200ms, 100ms optimal. Now it is normal to see spikes for short intervals but if you see constant spikes or if the latency soars over 500, 1000 even regularly then maybe you should look at the some physical or software issues with your storage system. Sometimes a switch reboot will solve the problem, or if you are in a VM environment host reboot.

If you R/W speed is under normal parameters then the only thing left is to optimize your queries and indexes. Most of the cases that is the issue.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial