We have installed dedicated SSD drives for TempDB
- 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 ?
- 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 ?
Best Answer
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.