Changing NTFS block sizes
-
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!
-
I do not have any answers to your questions but a point to look out for - disk defragmentation software may be an issue if you deviate from the default blocksize. I ercently read it in a post on this web site.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
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).
General guidelines:
- All SQL drives should have a 64K cluster size. Period.
- You should have permissions set so only the SQL Server account has permissions on the database (MDF, NDF, LDF) drives. React violently if anyone suggests putting a file share on there since you've got all that space. Tell them to store their MP3 collection somewhere else.
- Put data (MDF and NDF) files on separate physical drives from LDF files. A partitioned RAID set with multiple drive letters is still only one set of physical drives. This also applies to LUNS in a SAN, even if they are assigned to different hosts (at least this is the advice I got for my EMC SAN, ymmv).
- Put tempdb MDF on an isolated drive if possible. The fastest drive available would be ideal. There are some good reasons for creating two data files (or more for multi-cpu servers) for tempdb in the PRIMARY file group, but they can be on the same drive unless you have an extra drive available. tempdb log can be placed with the other log files, again unless you have the luxury of having a separate drive for this purpose.
- If you're backing up to disk, the backups should not be on the same disks as the database files.
- If you still have multiple drive letters available for data files, feel free to separate databases or to create multiple filegroups per database and manually place tables & indexes. Multiple filegroups per database may give you better performance if you have the time and expertise to place things just right for your structure & workload, but it requires a lot more effort from the DBA to be done correctly.
- There is usually no good reason to have more than one LDF file per database, but separating the LDF files for active databases (if you've got drives to spare) is a good idea.
I've never had a server with enough drive letters to do everything on that list.
SQL 2005 considerations:
- tempdb may require more space, especially if you use snapshot isolation.
- The Enterprise version can use filegroups on separate drives for partitioning large tables.
-
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!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply