SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Separating Out Indexes

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.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by GilaMonster on 16 October 2009

The other thing that people often miss is that splitting date out over multiple drives doesn't help at all when the data is sitting in the data cache

Posted by PaulHunter on 18 October 2009

Thanks for posting this.  I plan to show it to my SAN administrator in the morning.  I doesn't seem to matter how often I say tell them that 2 LUNs on the same physical hardware is still the same physical hardware.  They seem to think that "magic" happens when they carve up the SAN.

Posted by Steve Jones on 18 October 2009

I like magic SANs, wish I could get one.

It definitely matters a lot less if the indexes are in cache. You need to do some investigating to figure this out. that's another blog post.

Posted by BlackHawk on 19 October 2009

While it may be a minor point, the data cache does have to be initially populated so, the faster that occurs the better.

Also, inserts, updates and deletes require physical I/O on both the data and the indexes so split away!

Posted by Steve Jones on 19 October 2009

The thing about the cache is that it gets populated, and then repopulated, so if your indexes aren't used enough, they become stale and fall out of the cache. It's good to investigate if you are constantly pushing data in and out of cache that you need regularly. If so, you likely are low on memory.

Posted by Robert Frasca on 21 October 2009

If I'm carving out LUN's for SQL Server then I'm creating LUNS out of multiple physical devices and striping them together.  Carving up one device into multiple LUN's might be useful for a file server but I would never do that for SQL Server.  The whole point is to leverage multiple disk controllers for increased throughput.  Also, your fault tolerance capabilities take a hit because you can't hot-swap disk drives in the event of a physical device failure.

Leave a Comment

Please register or log in to leave a comment.