We're getting a new external disk array for our SQL Server and can configure it as we see fit so we want to squeeze every possible bit of performance for our memory constrained server. Since the disks are blank, there's no need to consider hassle of change versus small benefits; we want to set it up with even the small benefits included. Some questions:
1. I've read that SQL Server is best on NTFS block sizes of 64K instead of the default 4K. Does this make a difference in the real world?
2. If #1 is true, does a 64K block size imply a RAID stripe level of 32K for a 3 disk RAID 5 and a 64K stripe on a RAID 1 for harmony between the RAID and NTFS?
3. Also if #1 is true, I know I need to use the 'BACKUP ... WITH BLOCKSIZE 64K' to move to the new disks. Then I want to move back some of the smaller, less heavily used tables to the internal disks, a RAID-5 formatted with 4K block size. If a new filegroup is made there, will 'ALTER TABLE ... ON <filegroup>' figure out that the files there are a different block size? Or do all the files in a given database need to all be on disks with the same NTFS block size?
4. Assuming we have correctly laid out 3 independent RAID arrays (using all the space in the new external array) for tempdb, logs, and data files, any other ideas on how to futher optimize? Alas, there are not enough spindles for indexes to be on their own. Should they share a drive with the data files or with the logs?
Thanks for any suggestions!
1. ?? good question.
2. I have seen measurable performance improvements when increasing the block size of a RAID stripe/mirror in UNIX systems. I would say that this if you are trying to squeeze every ounce of measurable performance out of your new system, that it would be worth it to spend some time playing with different block sizes and measuring results. Sure, this will require some time so it depends on if the amount of time it takes you to set up this test is worth the possible performance benefits. Just a suggestion, if you are going to go through testing this, set up your RAID sets with as few disks as possible to minimize your setup time.
3. & 4. ?? more good questions...
Update / correction:
We aren't going to use 'ALTER TABLE ... ON <filegroup>' because all the tables will be in the same new place and that's the wrong command and procedure anyway.
One of the senior DBAs in our organization (but not in our group) assures us this will work even with different sized NTFS blocks. He also agrees that the tweaks of block sizes and stripe sizes are measurable but only by enough to be worth doing in cases when new disks are being set up.
Yes, I'm sure you did, but I bet you weren't using MS SQL Server on said Unix system's RAID. You are definitely right that fiddling with the stripe sizes can make a difference on almost any server depending on its job versus another server.
SQL Server is going to do disk I/O in 64K chunks. Forget anything else you've read about NTFS, use 64K clusters. And try to keep everything except SQL files off the SQL disks. SQL Server's units of data storage are 8K pages in 64K extents. If there is some reason you can't use 64K clusters, use the largest you can down to 8K.
Beyond the cluster size, the I/O traffic is different for data and log files. They both should use 64K cluster size, but there is a difference in randomness, cache lifetime, and other characteristics. You will see better performance if you don't mix them on the same physical drives. In this context, indexes and tables are data. Anything in MDF or NDF files should be on separate disks from LDF files. Keeping indexes and tables on seperate drives may be a good idea, but that's a higher-level optimization.
For an external drive array, I would consult the vendor about stripe size. Make sure they understand that all I/O requests will be 64K, and they are not just giving a generic recommendation. If no guidance is available, trying to make the stripes work out to a multiple of 64K across all drives is probably not a bad idea.
Forget about "BACKUP ... WITH BLOCKSIZE 64K", that only applies to tape blocksize. The backup is written in pages and extents and will be restored that way, the underlying cluster size is irrelevant.
SQL Server doesn't really care how many different cluster sizes you use on different drives. SQL Server allocates data in 64K extents. It can create & drop files and make them grow & shrink, but it lets the operating system keep track of the physical disk layout (unless you're using raw partitions).
I've never had a server with enough drive letters to do everything on that list.
SQL 2005 considerations:
Thanks for the confirmations and additional advice!
We've already got #2,3,4,and 5 planned out. Well, #5, about the backups, we're going to stash those on a partition of what we expect will be unused space on the log files disk. So it will be a RAID-1 of 145GB of which we need not even half for logs so I was thinking logs will be on f:\ with 45GB and the remaining 100GB can be g:\ and we can stuff backups in there. Same for the spare space on the tempdb disks. Since backups only happen once a day at night and we rarely restore they should be OK like that.
"I've never had a server with enough drive letters to do everything on that list."
When the other side of the PowerVault in question is freed up (it's a hand-me-down from a couple of servers headed to SAN land) in a couple of months, I'll have 14 disks to fiddle with and will get pretty close to being able to do it all. It'll have to be on only 2 SCSI channels but as beggars we've been choosey enough, hah!