Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
annied
Starting Member
1 Post |
 Posted - 2009-10-05 : 23:17:32
|
| Hi all, I apologize for the possibly vague and open ended question.I've been reading as much as I can find on technet and elsewhere but keep going in circles.Can someone please help me understand a few things. I'm new to DBs but not new to SANs. I believe MS SQL Server writes data in 64k blocks (please correct if I'm wrong). If this is true, what would that mean on the SAN storage side? Meaning, what would changing the step size in my raid 5 do?Can anyone walk me through how an I/O would go from DB to SAN disk?For example, if SAN disk block size is 16k, would it break down the 64k write it gets from the DB into 16K blocks and then write that out? ANd where does the raid 5 step size come into play?thanks for any info you could provide to set me on the right track here. I think I'm missing some of the basics here.... |
|
|
Larry Van Brocklin
Starting Member
8 Posts |
Posted - 2009-10-07 : 15:58:05
|
| http://msdn.microsoft.com/en-us/library/dd758814.aspxThis website will answer a lot of your questions (and more). Be aware from the start that the 'industry' has a lot of different names for the same thing - which makes the answers a little confusing.Example: The terms partition alignment, disk alignment, volume alignment, and sector alignment all refer to the same thing.You are asking a question that is actually in the middleground between SQL and the SAN. You are talking about drives, defined by Windows and lives on the SAN.SQL does write 64k files which are written to drives, which are hosted on the SAN.The drives are defined by File Allocation Units (aka Clusters), which is the smallest unit of disk that can be allocated to a file.By Windows default these are 4K in size (4096 Bytes); these should be reformatted by your OS admin to a size of 64k. This way, each File Allocation Unit will hold one whole file - no extra IO!There is just one more thing you should do, and it will return some nice IO performance results - align your partitions! By NOT accepting the (again) Windows default of 31.5 KB (32,256 Bytes) for the 'Starting Offset' and replacing it with a minimum of 64k.Because there is a Master Boot Record on the first File Allocation Unit, and that takes up space (31.5K); and since that record is necessary, Windows offsets where you can start writing your data. Again, by default it sets it to 31.5k. However, you can change this to offset it so you start writing your data onto an EMPTY File Allocation Unit.Here is one analogy that might work for you.Starting with Partition Alignment and working backwards...Suppose you have ten red buckets, each holding 64 oz. of water. Your job is to empty each red bucket into a blue bucket, also 64 oz. Partition alignment is the equivilent of starting with an empty blue bucket to receive the water. If you start with one that is partially full, you will have to stop when it is full and then empty the rest into the next blue bucket (extra work = extra IO). But then, when you start to pour your next bucket, it will again be into a bucket that is already partially full - from the last bucket (more IO), and so on, and so on...Partition alignment makes sure that you are starting with an empty bucket for each file and do not have to cross a physical boundary and end up with extra IO. The File Allocation Unit is (by default) 4k and should be set to a minimum of 64k (1MB would be better). By default, your system is using 4 oz. 'blue buckets' to receive the files. The performance gains are hardly measurable by using 64k File Allocation Unit sizes rather than the 4k default. However, the partition alignment correction in my testing showed an increase of 20%; so, if you align your partitions - then you should change the File Allocation Unit Size also. Both are done by the OS Admin. Both processes are destructive for any data on the drives, so make sure there is nothing on them you want to keep. This configuration should only be used for the data drive, log drive and tempdb drives. Errorlogs, backups, etc. would offer no measurable gains and only take up additional disk space.I know this is a long explanation, and probably more than you were expecting. But, these are the mechanics of writing data from a (SQL) database to the SAN.Re: RAID - this is beyond my limited knowledge. Your SAN admin should be able to help you with that.Good luck! |
 |
|
|
|
|
|
|
|
block size and raid step size