Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Ideal cluster size for transactions per minute and create database? Performance issue. Expand / Collapse
Posted Monday, March 25, 2013 3:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 15, 2016 1:56 AM
Points: 42, Visits: 214
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?

Post #1434796
Posted Monday, March 25, 2013 9:14 AM



Group: General Forum Members
Last Login: Today @ 5:20 PM
Points: 5,530, Visits: 8,038
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.


Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1434970
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse