March 25, 2013 at 3:51 am
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?
March 25, 2013 at 9:14 am
1) Who cares how long it takes to create a database? How often is that done, and how many of THOSE times does it need to complete 15 seconds faster?? 😎
2) You should always use 64K NTFS cluster size for volumes that are used for SQL Server data/log files to my knowledge
3) The CREATE could be slower than the alter because you have a tlog that is being zeroed out in the CREATE but the ALTER doesn't affect the tlog so Instant File Initialization allows the 10GB data file to be created in a flash. Also the CREATE has to make lots of new structures and also copy over everything from MODEL. Just some guesses there.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply