Post reply

create indexes on same filegroup or different?

  • October 30, 2008 at 9:32 am

    #195390

    Hi,

    We want to move some hugely used tables to another filegroup.For that we have added one filegroup to the database and a .ndf file created in it.We have dropped the primary key constraint on these tables and recreated the primary keys and clustered indexes on the new filegroup.Now my doubt is ,should I move the non-clustered indexes also to the new filegroup?Which design gives more performance??Both clustered and non-clustered on same filegroup or different?

    FYI...The tables are read-write tables.

  • October 30, 2008 at 9:37 am

    #891913

    Is the new filegroup on the same disk as it was before, or did you move it to its own drive? I'd probably move the nonclustered indexes to the same filegroup, unless I could move those to another filegroup on another disk.

  • October 30, 2008 at 9:40 am

    #891918

    The filegroup is on different drive.We want to lessen the disk i/o and so we are doing this.

  • October 30, 2008 at 11:18 am

    #892001

    if pssible create one more drive and place indexes there, peformance will be faster...

  • October 31, 2008 at 11:15 am

    #892480

    We cannot add one more drive now.My question here is we have moved clustered index to different drive and non-clustered are sitting on the old drive.Do I need to move non-clustered indexes aswell to the new drive or not??For better performance which is the best placing of indexes?

  • October 31, 2008 at 11:41 am

    #892488

    no, as it ok

  • November 3, 2008 at 10:12 am

    #893109

    Thank You.....So,iam just moving the clustered index to different drive.We have index rebuild job every week....Will it cause any effect on the indexes after moving??

  • November 3, 2008 at 11:57 am

    #893166

    job will run as it is.

    post your performance improvement results

  • November 3, 2008 at 1:30 pm

    #893240

    Hi Bang,

    One more question here.I posted this in different thread but got no reply .

    If we add a .ndf file to a database,will it be logged?Will that change get reflected on secondary server aswell?We use log-shipping.

    Or should I re-configure log-shipping?

  • November 3, 2008 at 4:20 pm

    #893329

    I never attempted such case but I feel you have to reconfigure log shipping.

    I will suggest do a small test on sample db and then see.

    Post your results.

    u can contact me at bang725 at gmail

Viewing 10 posts - 1 through 9 (of 9 total)

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