SQL Server 2000 Page size and the NTFS page size

Question

In the Oracle world, it's been gospel to build your database block size to be even multiples of the File system's block size. I assume this is still true but I'm not adverse to being told why technology has made this irrelevant.

But I've been told some SQL Server DBA's are going to upgrade the OS of a SS2000 installation to 64bit to get 64k pages in the FS.

1. Does SQL Server 2000 support changing the page size?

2. From what I've read it's fixed at 8k. Is that right?

3. If it is fixed at 8k, would there be any advantage to making the FS 64k?

I'm getting this information from a reliable source but none-the-less second hand.

EDIT: Thanks to SAMBO, I've read the links and found the specification for

"NTFS Allocation Unit Size" be set to 64Kb

I assume that term = Block Size...

So the conflict I have between 8k DB blocks and 64k FS blocks is in fact the recommended setup from MS.

Answer 1

The overall performance of the file-system can make a noticeable difference.

For example, I heard when Windows Server 2003 came out that SQL Server 2000 performance on that platform was improved significantly.

So it doesn't surprise me. I don't think the multiple factor is that big of a deal.

Answer 2

Make sure you read the Microsoft's Predeployment I/O Best Practices

It recommends using 64K allocation units for NTFS volumes.

Also, read SQL Server 2000 IO basics

Finally have a look at this post.

SQL Servers page size is in fact 8K, this is non-configurable. The advantage of having larger allocation unit on the OS is that perhaps you can get slightly better performance when SQL Server is fetching pages in to its cache.

From my experience, I doubt mucking around with these values will give you any noticeable performance improvements best case you would get a minuscule improvement.

Better spend your efforts doing stuff like isolating tempdb, ensuring raid1/0 array are used, having your transaction log live on a different array to the data file and optimizing queries.