The File System

The performance difference between running SQL Server on the two major file systems of Windows NT NTFS and FAT is minimal. This isn't surprising, because SQL Server manages the structures within its database files without much use of the operating system.

We recommend NTFS as the file system of choice for SQL Server. It's more robust than FAT, and it's better able to recover fully and quickly from an ungraceful system shutdown. The integrity of the file system is vital to the integrity of your data, so this fact alone should drive most SQL Server users to NTFS. And the security features of NTFS are vital if security is important to you. Unlike FAT, NTFS allows security and auditing on all files; these are important ways to protect your data when SQL Server is not running. (SQL Server provides its own robust security.) When SQL Server is not running, the operating system is your prime protection. If you have a legitimate need to dual-boot your system to MS-DOS, Windows 95, or Windows 98 or if you need to use some disk utilities that work only with FAT, you might choose FAT. (You can, of course, make the boot partition FAT and format the other partitions with NTFS.)

You can safely use NTFS file compression, but you will see a substantial decrease in performance perhaps as much as 50 percent if your system is I/O intensive . So typically, file compression is not an appropriate choice for SQL Server.

With previous versions, you could run SQL Server on a new partition that was not formatted with any file system. Such a partition is commonly referred to as a raw partition . Although you cannot run SQL Server 7 from a raw partition, you still have the option of creating database files on raw partitions. Storing data on raw partitions is common for UNIX database products, and users who have UNIX DBMS backgrounds sometimes do this with SQL Server as well. Under heavy workloads, raw partitions can provide a small (a couple of percent) performance benefit. If the I/O system is not at capacity, you will probably notice no performance difference at all.

For most installations, using raw partitions is not appropriate. Even if you gain some performance benefits by forgoing a file system, you forgo benefits such as basic file operations and utilities (copy, delete, rename, dir) and important operations such as detecting and resolving bad disk sectors that a file system provides. If there's no file system to perform bad sector operations, you must be sure that your hardware will take care of this. (This type of hardware is available but costly.)

Running on raw partitions was common in UNIX environments because the UNIX file system could not always be counted on to write through its cache and hence could not be trusted (for the same reasons discussed earlier with write-back caching controllers). The UNIX file system was also sometimes avoided because it caused a significant performance penalty in SQL Server. Neither case is true with Windows NT. SQL Server opens its files with the FILE_FLAG_WRITE_THROUGH flag set in the Win32 API call to CreateFile. This flag instructs Windows NT to acknowledge the operation as completed only when hardware reports that the bits are actually on disk.