June 18, 2010 at 5:03 am
Hi all
I just wondered if people would cast an eye over what I am doing in regards to a migration from an existing infrastructure to a SAN based one. I will summarise as best I can.
I have been collecting statistical information using sys.dm_io_virtual_file_stats in order to find the peak number of reads and writes per second. I am then going to feed these into some formulas to determine that correct number of disks and the RAID config required for each application being migrated, so that IO does not exceed %85 of the average disk IO in the SAN.
I have also been monitoring the SQL Buffer: Cache Hit Ratio to ensure that the IOs are not skewed, in my case I was averaging 99% which is good, although I have not figure out what to do should this have been lower.
Once I have done all this I am going to to the sizing which from data I have collected about each database over the last month or so to predict estimated size of disk to not exceed %85 any time soon.
One thing I am really weak on is disk partition alignment. I sort of understand that I need to offset the partition such that a whole integer is written taking into account the size of data being written. However I don't know how to find out information to correctly do this task i.e. data sizes being written ( I may have completely misunderstood this). Also does the offset apply the the volume as a whole or do you do it per disk in the RAID array?
All comments are welcome
Many thanks
June 18, 2010 at 8:56 am
Hey Kwisatz78,
There a multitude of ways to try to prove what your I/O requirements are going to be with a new solution, but I've prescribed to the application of IOPs and disk queue length. I determine what IOPs my system currently needs and then push the SAN admins to get me enough spindles to support like 10 times that. I then look at my average disk queue length and determine the number of existing spindles and then plan for future growth of 2 to 3 times the I/O requests I have now and that gives me an idea of my opening bid to the SAN admins 😀 Between the two and the cost of the disks and SAN configs, we usually end up with something in the middle. At least that's my experience.
For the disk alignment issues, you need to be sure that the starting offsets for all disks that your SQL data files will be on are evenly divisible by 32k (32768 bytes) at a minimum (better is to just ask for 64k or 1MB IMO, but that's not as critical as just making sure you're at an even 32k alignment to begin with).
With that out of the way, the easiest way I've found to check this is a VBS script we wrote that does all the work for us :-P. But if I do it manually, I open a command prompt and run the following:
wmic partition get name, index, size, startingoffset
What you want to see is that the startingoffset column is evenly divisible by 32768 for each drive (only exception is the C:\ drive if you don't intend on storing SQL Data there and it's a server OS prior to 2008 (2008 OS automatically aligns all drives with diskpart to a correct offset to avoid the issue altogether).
HtH,
-Patrick
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply