The File System

The performance difference between running SQL Server on the two major Windows NT and Windows 2000 file systems—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.

I 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. Also, 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 or to 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 partition the system boots from a FAT partition and format the other partitions with NTFS.)

Although it is physically possible to create SQL Server databases on NTFS compressed volumes, Microsoft does not support this, so I can't recommend that you ever consider it. Other reasons for avoiding compressed drives, in addition to Microsoft's lack of support, include performance degradation (over 50 percent in some conditions) and the inability to provide reliable transaction recovery of your databases. Transactional recovery requires sector-aligned write, and compressed volumes do not support this feature.

With previous versions of SQL Server, 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 2000 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 (about two percent) performance benefit. If the I/O system is not at capacity, you'll 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 also forgo benefits that a file system provides, such as basic file operations and utilities (copy, delete, rename, and dir) and important operations such as detecting and resolving bad disk sectors. 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.)