Post reply

Indexes and File Groups

  • January 7, 2009 at 7:46 am

    #198068

    All of my databases have an additional file group called 'Indexes' which (as expected) is used for the indexes we create. The file group resides on a different physical drive than the primary file group. When we create new indexes, we include the 'ON [Indexes]' option, but my question is whether I'm gaining or losing performance by placing the primary keys on the Indexes file group. Would we be better of by leaving the primary keys on the Primary file group?

    Any advice would be greatly appreciated!

  • January 7, 2009 at 7:59 am

    #922548

    It depends.

    If the Primary keys are created with a clustered index, then don't place them on the index filegroup because the clustered index contains all the data.

    If you create PK's with a non-clustered index, then you probably will get some advantage when placing them on the index filegroup.

    One main thing to keep in mind is also that the two filegroups are using different physical disks. Sounds obvious, but I've seen data and index files on the same disk.

    [font="Verdana"]Markus Bohse[/font]

  • January 7, 2009 at 8:04 am

    #922555

    Thanks so much for your advice, Markus. Most of our PKs are created with a clustered index, so we'll leave those on the primary file group. I appreciate your input!

  • January 7, 2009 at 9:16 am

    #922647

    Remember that having file groups on different physical disks is all about reducing resource conflicts. A blanket assumption that it is best to put indexes on one disk and data on another is not necessarily the best approach.

    You really want to figure out what operations cause you to be waiting for read or write heads and separate them so they are no longer waiting for hardware resources. This can be a pretty complicated job - even determining what is causing conflicts can be pretty tricky. By putting non-clustered indexes on one disk and clustered indexes and heaps on another disk, you have essentially ensured that every query that uses an index (that is not covering) will access both sets of disks. Although this may be a good thing in some situations, it is very possible to be a performance burdon as well.

    If you really need the hghest level of performance, make sure you spend some time testing.

  • January 7, 2009 at 9:27 am

    #922663

    That's great advice Michael - thank you!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply