Unanswered: tempdb on SSD or virtual RAM disc to increase temp-table performance
On a monthly bases we have a lot of temp calculations that run 4 days in total. We can't really change so many scripts and therefore i am searching for a config / hardware solution.
Its not a ETL process because we are creating bits of information out of at least 10 applications (therefore i would call it calculations or lots of update-statements). This combination of information is only 1 stage of a bigger process that saves the calculated data finally with a normal ETL process (SSIS) in a DWH. If this calculation stage fails (server crash), we can easily restart it.
The MSSQL server where this calculation is running has only 2 HDD-raid1. One raid1 for data, indices and tempdb and one for the log. This calculation produce so much I/O that the whole process takes days. The server has 180GB of rum and theoretically the whole data and result of this calculations could fit in 5GB memory only. That means we use the database/resources in the wrong way.
I think we have to reduce the I/O in the first place and also handle the I/O that is needed anyway in a second step. BTW the server is MSSQL 2014 SE.
Move the tempdb on a virtual rum disc (ca. 40GB). That means we move the main I/O on memory, right?
Use more temp tables to reduce server-logging and recovery time and use the new rum disc (is there really less logging?)
Put the logs, indices and important tables on a new SSD raid5
Put all other data on the old HDD-drives
Is this a good solution or would you suggest the standard solution d.h tempdb on the SSDs as well.