So I inherited a server that has serious write latency issues according to a script provided by glen berry, I also noticed that the tempdb files are spread across multiple drives.

What could be the benefits of having Tempdb.ndf files on different drives? is there any? also the highest latency comes from the drives that host those tempdb files

  • Scsimon, yes sir all the drives are the same, I was concerned because normally you see in sql server installations all the tempdb files in the same disk and thats the only porpuse for that disk and in this case ndf files and mdf had their own disk, so i was thinking what could be the reason to doing that – Andres Arango Jan 29 '19 at 21:45

When there are multiple data files in a database, all the writes to the database are striped across all files based on the proportion of free space that the file has to the total free space across all of the files. Now, each of the data files has its own set of allocation pages (called PFS, GAM, and SGAM pages) so as the writes move from file to file the page allocations occur from different allocation bitmap pages, spreading the work out across the files and reducing the contention on any individual page. This would be your benefit.

Again important factor is how many of them are there. As a general recommendation it should be based on logical processors you have on the server. So for 8 or less keep the number equal to processors. For more starting point should be 8 and then increase by one based on testing or contention issues.

Again for write latencies you might want to check with your storage team what's the threshold for the acceptance number in your environment. For faster temp db we in out environment keep them on SSDs and make sure log file is spiltted on different drive than data files.

Also as a side note those latencies number may or may not be a problem depending upon the nature of performance you are witnessing right now.

Plus I would suggest to check on trace flags 1117 and 1118 if you are on version of sql running 2014 or lower. From 2016 onwards it's enabled by default

  • Thanks KASQLDBA I would check those flags, the version of the sql server is 2008 R2 – Andres Arango Jan 29 '19 at 21:47
  • Just as an addition, in a shared storage scenario additional logical LUNs can also provide more physical write-paths to the storage. – George.Palacios Jan 30 '19 at 9:36

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.