Does the 'Enable Advanced Performance' override FILE_FLAG_WRITETHROUGH on Win 2k3/SQL Server 2005

Question

SQL Server opens files with FILE_FLAG_WRITETHROUGH, which appears to force writes to the physical disk. In SQL Server-speak this is called 'Forced Unit Access (FUA)'; a white paper discussing this can be found here. Many SANs appear to honour this and this might explain slow performance of an ETL process I am developing on a server connected to an IBM shark.

On the dialogs in disk manager on Windows 2003 server one can select 'Enable Advanced Performance'. I can find indirect and apocryphal documentation that implies that this overrides forced writethrough behaviour.

Does anyone know for certain whether this is the case or what this option actually does?

Answer 1

This is not the case. SQL Server will always have write-through enabled on files it opens. This ensures that any writes are not buffered in the local Windows disk cache. Anything beyond that on the disk controller (RAID controller, etc.) or on the SAN will have its own cache (hopefully battery-backed and mirrored) and will have a separate cache policy regardless of how the files were opened under the OS. Don't ever enable write-back on a cache that has no battery back-up. If you lose power during writes, your database will become corrupt.