4

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.

Improve this question

1 Answer 1

Reset to default
6

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.

Improve this answer
2
  • 2
    Just a note: mechanical disks will only perform sequential well when the entire drive is dedicated to a single database's log file. As soon as you have two there, it becomes a lot less sequential.
    – Aaron Bertrand
    Commented Jul 7, 2015 at 11:15
  • 1
    @AaronBertrand agreed. Thanks for pointing it out
    – spaghettidba
    Commented Jul 7, 2015 at 11:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.