asked on 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
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
Windows Server 2012Databases
Last Comment
Zberteoc
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
ASKER
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.
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.