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.