Enabling write caching for TempDb data drives

Question

Recently, we had issues with slowness for some queries where TempDb latencies on their respective drives had spikes.

We already have 8 tempdb files and trace flags 1117 and 1118 are not enabled. We are planning to enable but do not know as of now what issues might occur due to them.

However our storage team has suggested something called enabling write caching for TempDb drives. We have no clue how this will effect overall performance.

Can someone provide any insight if enabling this is really a good idea for SQL server TempDb files?

Answer 1

Trace flag 1117 deals with uniform auto-growth across multiple data files(https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/), while 1118 deals with mixed versus dedicated extents. These trace flags are not limited to use on tempdb, so it will affect your entire instance.

I'd look at this article as well.

As far as write caching goes, dependent on the configuration of your storage array, it will probably delay the writes to disk briefly in cache until they commit to disk (ymmv though, I've seen issues with power outages affecting cache, thus losing the writes before they were committed to disk).

Have you examined the queries to know how they're using tempdb, and if they can be tuned to use it more efficiently?

I'd also look at how your storage layer is architected for your databases. Do you have tempdb isolated on separate storage than your user database files?

Is the storage latency behavior repeatable each time the query is run?