0

We have installed dedicated SSD drives for TempDB

  1. First question - should these drives be formatted with 64 Kb allocation size ? Currently it shows 4 Kb (Bytes per Cluster = 4096)

Does TempDB benefit from 64 Kb unit size, or it can be left with default 4 Kb ?

  1. Second question - we have data and log files on a separate "virtual" volumes that SAN admin sliced before, those volumes are made from SAN RAID array, and show 4 Kb allocation unit size

We can not change allocation unit size for those volumes at this point. So if we change TempDB SSD drive to 64 Kb, and data/log volumes stay as 4 Kb, will it cause any trouble for the SQL Server ?

-1

ABSOLUTELY format it 64k.. tempdb will be faster and less taxed than the other san datafiles. Factors can be many, but the main one being heavy tempdb usage. SQL Server allocates space in multiples of 64k, as does Win Server, by design.
Writing into a properly pre allocated datafile on its own 64k formatted drive is very significant to performance. 64k chunk would go from 16x4k data file reads to tempdb in 1x64k packet write as opposed to 16k packet writes. 4k allocation on the read san drive really isn't a big concern unless there's lots of fragmentation, it's the writing that will be impacted by 4k San. Thats the mathematical truth. However in theory, a properly raided San even at 4k may not be as noticable because consider the drive head multiples they're writing with.

Beyond that are architectural modification you can use such as having tempdb use multiple equal data partitions. But that is probably beyond scope.

That is my short answer, hope I didn't confuse the issue, by being short.

  • 1
    I'm afraid this simply isn't true - the 64k myth I mean. Check this post blog.purestorage.com/what-is-sql-servers-io-block-size – George.Palacios Mar 27 at 7:10
  • "Myth" is harsh. Sql uses windows OS memory manager code for high priority optimized read/write operations and threading it therefore can (and does) consider data block allocation sizes. Both in memory transfers and disk transfer including its formatting. Sql Server creates the thread pool and data pointers per Cpu for its writers and buffers. 64k is most common on high write transaction systems. Why write 8 times when you can do it once faster. – Daniel Ames Mar 27 at 16:15
  • The "myth" here is that ALL operations happen in chunks of that size - they don't. Depending on your workload, 64K may actually make less sense. (IE lots of bulk imports) – George.Palacios Mar 27 at 16:26
  • Yes, agreed 100% – Daniel Ames Mar 27 at 16:28
  • Ah okay - just wanted to make sure - to clarify this answer is completely correct for most OLTP use cases, BUT as always the more global answer is "it depends". – George.Palacios Mar 27 at 16:35

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.