Subscribe to this blog
Briefcase
Print
Someone was asking about using multiple data files recently to try and increase performance. I had answered that unless you had separate physical disks that it wouldn’t matter.
However then I remembered hearing something about threads and files for I/O. I tjhought this was a myth, but I wasn’t sure. I searched around, and then pinged Gail Shaw since I know she does a lot of internals type investigation.
She confirmed this is a myth and sent me this reference: SQL Server Urban Legends Discussed. It’s from the Microsoft Customer Service engineers and discusses the origin of the myth and how things work.
The bottom line is that SQL Server uses a thread for each unique disk drive, not files. In SQL 2000, you can fool the system if the disk drives are volumes on the same physical disk. However in SQL 2005 the system checks to see if these are the same physical disk.
There are some caveats, but if you hear this rumor, dispel it. Let people know that multiple file groups (or files) only help if you have different physical I/O paths and drives.
Posted by Patrick LeBlanc on 13 October 2009
This is good to know. Especially when you are in a discussion with your SAN administrator.
Posted by Steve Jones on 13 October 2009
I wonder how LUNs appear in SQL 2K5 and above. I would think each would be a separate physical drive, regardless of how they work on the SAN itself, but it would be interesting to get some testing done.
Posted by Anonymous on 16 October 2009
Pingback from My Weekly Bookmarks for October 16th | Brent Ozar - SQL Server DBA
Posted by Andrew Kelly on 18 October 2009
Just a note that you may want to refer to them as physical Arrays vs. disks.
Posted by Aaron Nelson on 20 October 2009
Steve,
Can you get us an answer on how it treats LUNs that happen do reside on the same physical array? That would be extremely good to know for me!!
Posted by AngryPets on 20 October 2009
Another problem that seems to be stemming from people putting DOWN this urban legend is more and more IT folks and even DBAs are starting to assume that adding more files can't/won't improve performance.
Which isn't correct. It can drastically improve performance - you just have to do it correctly.
Posted by Robert Frasca on 20 October 2009
Each LUN does appear as a disk drive in Windows. So, if you have a RAID 10 LUN with 12 70 GB disk drives you'd have one LUN (or logical disk drive) of 420 GB since half the drives are reserved for mirroring in RAID 10. This would appear with it's own drive letter.
This can get interesting when configuring a SAN because, from my point of view, I'd rather have alot of smaller spindles in my physical array for flexibility in configuring the LUN's as well as adding more controllers for distributing the I/O. The problem is that I've run into system administrators (or SAN administrators) who prefer larger drives to reduce the footprint of the SAN.
If I'm forced to build a LUN with 200 gb drives and since the minimum configuration for RAID 10 is 4 spindles, that means in an ideal world where I have my TempDB and my Log file on their own separate arrays, I have to create 400 GB LUN's each for my TempDB and Log storage arrays. That's 1.6 TB, much of which is wasted, and I haven't even started creating LUN's to distribute my files and partitions. Usually, I've ended up negotiating where the SAN guy gets several LUN's with big drives for backups etc and I get a bunch of smaller drives to slice and dice into what is hopefully a nice configuration of LUN's of various sizes to optimize file distribution.
This is one of the major reasons why I think the DBA needs to be involved in the purchasing decision as well as the configuration of large scale storage arrays. I was at one site where management had purchased a 12 TB SAN from a small vendor that could only be configured as RAID 3. Virtually useless from a SQL Server point of view. (The partiy disk is a major bottleneck for write operations.)
Posted by Steve Jones on 20 October 2009
Robert, well said. The way you build things on the SAN isn't any different than with local disks. The SAN has larger cache, and it might have better xfer rates, but the principles of building good disk systems still apply.
I might share the physicals behind my LUNs with some lightly used servers, not nothing that might get heavily accessed.
Posted by Mike Kruchten on 20 October 2009
More specifically the multiple threads applies ONLY to the initial creation of the data file, not to I/O related to normal system activity.
Posted by DEK on 22 October 2009
I find it interesting that SAN’s now seem to require a minimum of 4 drives to do RAID 10. Some years back I was creating RAID 10 LUN’s with 2 drives (an HP MSA-1000).
Posted by Steve Jones on 22 October 2009
You cannot do R10 with 2 drives. It's beyond the definition.
2 drives would be R1 (mirror) or R0 (stripe). R10, or R01 is a combination of R1+0 (or vice versa). To stripe, you need to drives. To mirror the stripe, you need 4.