When should nonclustered indexes be stored on separate filegroups?

Question

I have heard that storing indexes on a different filegroup and drive increases performance in a database because the drive doesn't have to go back and forth between the index and the data to which the index refers. I have also heard that this is a myth.

When is it advisable to store nonclustered indexes on a separate filegroup and drive? What perfmon/profiler evidence would lead me to arrive at that conclusion? Does hardware play a role in the decision (whether a RAID/SAN is used over a single drive)?

Answer 1

The slowest part of a DB system is the disk drives. Eliminating bottlenecks at the disk level will improve performance. When data is being looked up and an index is used, the index is first looked up and then the corresponding data is fetched. If both the index and data are on the same disks, then there is some contention happening. Whereas, if the data were on a different (physical) disk, then there is faster IO happening, thereby increasing performance. The main part to note is that the data or index are on separate physical disks or LUNs.

You would use such a scenario if you need to get better performance out of your system, provided you have the disks. For your perfmon counters you could use Physical Disk – Avg. Disk sec/Read, Physical Disk – Avg. Disk sec/Write, Physical Disk – Disk Reads/sec, Physical Disk – Disk Writes/sec to have a before and after comparison of your changes.

Answer 2

It is certainly true that spreading your simultaneous I/O between different drives will increase performance - that is no myth. It is a myth is that doing it twice will improve performance again.

If you SAME, then splitting your array up into two partitions and putting indexes on one and tables on another is a waste of time.

Answer 3

Separating Indexes from data onto separate filegroups = performance improvement is highly debatable. The performance improvement "may" happen if you have the underlying hardware to support it but just by the fact that separating them to different filegroups doesn't give you perf boost. And it is also NOT easy to measure the perf boost because of this.

Ref: http://weblogs.sqlteam.com/dang/archive/2008/08/01/Are-you-a-DBA-Monkey.aspx

You should ask the question first. Why do you need to do this?

1. Are you looking to improve the performance of backups by NOT including the indexes?
2. Are you looking to improve the performance of reads & writes to these indexes?
3. Are you doing this for a better manageability of placement of the underlying objects?
4. Do you have large volumes of data that have varying needs for performance?
5. Are you looking to use SSD's for non-clustered indexes to improve performance etc...

I looked at this task to support the need for #5 in the above list and it seems like a good proposal to me although we haven't acted upon that yet.

Note this decision is NOT that easy to make and you need to figure out what you are trying to do and make sure you have the hardware to support. Don't make changes like this unless you have tested well and you see a significant boost in perf otherwise you might as well drop this idea. It's NOT worth it if you are expecting perf boost by simply separating the indexes onto separate filegroups.

Answer 4

I'll tell you my personal experience regarding this item. The non clustered indexes should be stored on a separate filegroup when the current disk drive is not big enough for the needed space :-). You can laugh about it.. but it happens.

So an emergency fix for us, when we were about to remain without free space on a data drive, was to create a nice script to recreate all non clustered indexes online on a new filegroup on a drive with free space. One would think it's easy and fast to buy new storage.. but it's not like that, really.

Regarding performance we didn't see anything out of the ordinary after the move. But it's a big SAN storage box where everything is held together :-).

Answer 5

In general; splitting data and indexes onto separate similarly performing disks can increase performance for substantial write operations to that table or large read operations that utilize that index. A similar methodology to some other I/O operations, like a partitioned table spread across multiple physical disks.

However it is also largely dependent on the storage. For example; if you have a server with a nice Fushion ioDrive (or something similar) and also has individual spinning disks. It might more beneficial to keep everything on the ioDrive (unless space is limited). There are also other things to take into consideration - RAID configuration, network storage configuration.

Do some bench marking either on a testing server with similar hardware or (only if a secondary server is not an option) during non-peak hours with a temporary data. The DBA-Monkey link by Sankar above is good food for thought.