Separating Out Indexes

Steve Jones, 2009-10-15

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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads