Load SQL DB into Memory

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
burny1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocCommented:
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 EngineerCommented:
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.
burny1Author Commented:
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.
ZberteocCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2012

From novice to tech pro — start learning today.