0

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?

  • 5
    Just always turn on 1117 and 1118. Until newer versions where you don't need to anymore because that's just the default behavior. If they had started that practice in 2014, it would be the default behavior there, too. – Aaron Bertrand Sep 9 '19 at 20:49
  • @AaronBertrand, thanks – BeginnerDBA Sep 10 '19 at 15:12
2

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?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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