A number of SQL Servers here are setup with a disk cluster sizes of 4K, however for SQL Server , shouldn't it really be 64K? When I run transaction tests using HammerDB I get good results, however when I tested the performance of creating a database and extending it the results are not so good .
for example:
------------------
4K Cluster Size:
Tpm = 1,038,994.00
CREATE DATABASE 20GB = 15.1 seconds
ALTER DATABASE 10GB = .34 seconds
----------------
64K Cluster Size:
Tpm = 1,500,965.00
CREATE DATABASE 20GB = 31.7 seconds
ALTER DATABASE 10GB = .34 seconds
The above results are consistent for 1000 test cycles.
The Transactions Per Minute show a clear performance gain, but the CREATE DATABASE statement takes twice as long, why is that? I would expect it to be fast because its writing a contiguous block? And why is the ALTER DATABASE statement the same speed regardless?