SSD = solid state drive
We have a specific question about tempDB performance on server CRM01 (Marketing CRM server – running large queries):
On CRM01, tempDB data and log files reside on the solid state drive. The SSD total size is 400Gb.
Tempdb data files take up 300 Gb and the log around 100 Gb and may need increasing but there is no space left.
The question is theoretical. We do understand that:
– if tempdb log files grow too much we should split our processes so that data should be processed in smaller chunks, or we should look at using possibly temporary physical tables instead of tempdb.
- both data and log tempdb files should remain on the SSD to optimise performance
However, if we really had to take the decision of moving some of the tempdb files out of the SSD due to space constraint, which ones would you choose to move to a standard fast drive: the data or the log files?
what is it was not the tempDB, it was a normal DB.
of course then I could partition tables, and create different filegroups, but still would I keep the data or the log in the fastest disk?
lets say the utilization is 50% read and 50% updates and inserts.
@Moderator, I agree the second part of the question is not objective, but it provides insights and ideas.
Best Answer
In my opinion putting the tempdb in the SSD drive without looking at the performance stats is the wrong way to go. SSD drives have high cost/GB and are a premium resource that should be used in the best possible way.
You will probably get better performance moving the most used database files to the SSD, given that you already have broken your database into multiple files and isolated the most problematic objects in their separate filegroup.
That said, SSDs are great for random reads and writes compared to traditional disks, which is the pattern you're seeing mostly in data files. Log files are more subject to sequential reads and writes, which mechanical disks perform just as well as SSDs.
It's true that tempdb is a critical resource for the instance and should be placed in a fast drive, however you're probably going to see a bigger performance gain if you added more RAM (much cheaper than SSDs).
You mileage may vary, but a generic question deserves a generic answer.