When creating a new disk partition for SQL Server on a SAN (RAID 5) do you use Diskpart with the Align= parm to keep from doing two physical I/O operations for each logical I/O?
Do you use Align=64?
All comments welcome...TIA
DeWayne
SQL Server tends to read and write in 64KB chunks (or multiples thereof), so building the disk system around 64KB blocks is optimal. You have to have really heavy I/O load for such efforts to have a measurable impact, though.
If you've got the disks to spare, consider using RAID 10 instead of 5. RAID 10 has much better write performance. (Of course, if your database is relatively unchanging, RAID 5 is more economical).
-Ryan / Kardax
|||I can't afford to use Raid 10 for all my SQL Servers. Also, I have a memory cache in the SAN that helps the Raid 5 write problems.
I am in the process of virtualizing some of my SQL Servers and I will be changing the disk subsystem (an IBM EXP SAN) to a new Pillar SAN. I wanted to setup the new SAN partitions correctly to begin with for performance reasons! I have been told that not aligning the partition "could" result in two physical IO's instead of one. I just thought that the double IO reason alone would be a "big hitter" in terms of disk performance for SQL/Server since disk performance is the weakest (read slowest) link in the performance chain.
Maybe I should just setup yet another "test" system and re-invent the wheel to find the answer.
Am I all wet on this or are the "Exchange" people the only ones that are doing anything about this IO performance issue?
Thanks for your comments,
DeWayne
|||Two I/Os is really not a big deal since they're almost guaranteed to be sequential. It's not a doubling of I/O, either... SQL Server reads and writes in big chunks, so 1 more I/O on top of 300 probably won't even be measurable in most cases.
I agree that a battery-backed write buffer on your SAN should mitigate RAID 5 write performance problems, as long as the buffer is big enough to handle the work.
The simplest way to improve SQL Server I/O speed is to simply put more RAM in the box. If it has enough RAM to store the whole database in memory, the only I/Os it'll do are for modifications--reads would always come straight from RAM. Even if the DB doesn't fit in RAM, more RAM enables it to more effectively cache the "hot" parts of the database.
-Ryan / Kardax
|||please take a look the blog, the I/O types are different for Data and log.,you many want to align data & log in different manner
http://blogs.msdn.com/sqlcat/archive/2005/11/17/493944.aspx
No comments:
Post a Comment