Which SSD type is optimal for TempDB on new SQL hardware?

All we need is an easy explanation of the problem, so here it is.

Looking to purchase new bare metal SQL cluster hardware and run tempdb off local node instead of SAN.

Dell classifies their SSDs as:

  • read intensive
  • write intensive
  • mixed use

Which SSD type is optimal for TempDB hosted directly on each cluster node (R.I., W.I. or M.U.)?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I think those SSD are designed for this scenario: a load balancer that re-route the traffic and divide it into read and write

Which SSD type is optimal for TempDB on new SQL hardware?

  • Write intensive is for master server where a lot of write happens
  • Read intensive: is for replica server where a lot of read happens
  • Mixed used is for everything else

Method 2

To answer this question, you really have to know your planned workload on the servers.

What will be the actual scripts/queries that are going to be run on primary / secondary servers ? What will be the business processes ? How they will use TempDB ?

Primary server may benefit from Write-intensive (a lot of DML operations on millions of rows in temp tables, but very few rows returned/read from as the result), but it is also possible that on Primary you load data into tempDB once and read it many times with subsequent statements in a query, so in that case it would benefit from Read-intensive SSD…

If you don’t know planned workload, go with mixed, and nobody’s hurt

Method 3

TempDb is write-heavy. Often a temp table or a spool is written and then only read once. What this means for the Dell options, I can’t say.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply