There should be some considerations regarding hardware selection/configuration and OS configuration like:
- Hard disk – SCSI is better for reliability and performance
- RAID level – RAID 10 is recommended even though at least 50% space is redundant depending on configuration
- Storage layout – all data in one volume may not be the right choice if load is high
- Cluster size – 64KB and above to reduce fragmentation, also defragment periodically
- Compression – cannot be enabled if cluster size is 4KB or less, may not be effective for all kinds of data, comes at the expense of CPU
- Anti-virus configuration – AV can need to be setup properly so that it does not delete FILESTREAM files
- Disable 8.3 naming for NTFS – if number of files are high then NTFS has to work harder to get a unique name
- Turn off last access time update – since files are not accessed by explorer rather SQL Server turning off this feature is OK and it saves a lot of time
- Security – SQL server need to be configured to use integrated security (Win32 file APIs to work that uses Windows token associated with the client’s thread accessing files)
Sharing Experiences 