Cluster size for Navision - MSSQL 2005

mihai.valcea

Hi all,
.. now I'm posting to correct topic :?

In documents related to navision performance, for mssql 2000 the cluster size of subsystems is adviced to be 8 kb.

1. Is this cluster size changed for mssql 2005 ?
2. The cluster size of 8kb (for 2000) is for all all subsystems or only for DB (database susbs cluster size is the same with transaction log cluster=8kb)?

Thanks,
Mihai

colin_leversuch-roberts

the best size for the transaction log is 64kb or larger, as it only does sequntial io. Ent edition can read 1024kb on read ahead, you may do better using 64kb across the board. In due course I will be testing these different sizes vs performance. Note that some ntfs defrag tools can't cope with larger block size.

stryk

Unfortunately, this is a common misunderstanding: The smallest unit to read/write data for SQL Server is a Page with 8kB. But this refers only to logical operations within the caches - the RAM.

When physically reading or writing from/to disk the SQL Server accesses extents at 64kB. That's why formatting disks on 64kB blocks could be charming ... but: in my experience the impact is not that drastical, if the disk-subsystem is set up well, you don't feel quite a difference ... or any other experiences?

Best regards,
Jörg

colin_leversuch-roberts

random io still occurs in 8kb blocks for disk writes, disk controllers/SANS use intelligence in an attempt to turn random io to sequential io, internal and external fragmenation can alter how this all works.
You are correct to say for actions in the cache this is irrelevent, this is why more memory can aid performance as it negates physical io.