Category: Uncategorized

I saw a post recently where someone was asking how to separate out all indexes from the data into a separate NDF file. This was the same post that I wrote about recently with the thread myth.

The poster seemed confused on a couple points. The first was that he or she thought that they could separate out the clustered and non-clustered indexes from the data.  That’s alone makes me think that this user is not advanced enough to work with multiple filegroups if they don’t understand the table v clustered index structure.

The second thing was thinking a separate file improves performance if the indexes are moved. This can improve performance, but a couple things need to take place.

1. The indexes need to be heavily used at the same time as other data. You are trying to separate out items that are heavily used concurrently. If I need 1,000,000 rows from a structure (table or index) now and another 5,000,000 rows when that operation completes, I’m not helping things if I separate these items. If two users are actually accessing this data at the same time, or one user with a join, then separate files can help.
2. You need separate physical disks. A lot of people don’t think this through properly. It doesn’t mean two volumes or drive letters, and it doesn’t mean two LUNs on a SAN. It means separate physical storage devices. They can be HDDs, SSDs, or something else, but they need to be separate physical devices.

The user admitted this was something they heard, and would likely not separate things out. I think that’s a good move and should save them some headaches.

I’m out of the office today, in Austin, TX (yee-haw!) at the Stack Overflow Dev Days – Austin. There are a few more around the country coming up, and if you’re a developer, I’d go check it out, just for the speakers. I’m not sure you’ll learn a ton about how to be a better developer in any of these events, but I think you’ll be inspired.

That’s part of why I’m going. After all, the lineup for Austin has talks on

• python
• the iPhone
• FogBugz
• ASP.NET MVC
• jQuery
• Erlang/couchDB
• Code reviews

Of those, maybe 2 or 3 are applicable to my career. I’m not likely to pick up tips that I’ll use next week, though I may learn a thing or two that I could use in the future. There’s a better reason in my mind to go to an event like this, going to hear some well known and well respected speakers:

Being around smart people is exciting

It’s actually more than exciting; it’s inspiring to me. Coming away from an event like this I’m sure I’ll be excited again about my job, and I’ll have ideas that I can apply to my job. Or I’ll be thinking about how I might try something new in my job.

I’ll also be going to see how the format works. How they handle a one day event with multiple speakers to see if this is something that might work in the SQL Server world.

So I’ll be mostly out of touch today, and not responding to messages, but I’ll post some notes and updates when I get back and hopefully a few pictures.

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.

Congratulations to John Magnabosco for being awarded as a Microsoft MVP. I met John last year at the Indy Tech Fest, and I’m looking forward to going back next year. We’ve corresponded regularly, and John was one of the people I picked to do some guest editorials here at SQLServerCentral, so look for his thoughts here soon.