Our DBA has recommended to create indexes in different filegroups like table has created in primary filegroup by default and index will be created in other filegroup. Index may be cluster or non-cluster. Can someone explain the reason for the same, what is the advantage of creating indexes in different filegroup? As per my understanding,Unique Cluster index(PK) store the data in sorting form, in that case it will store the whole data in other file group. In case of Non-cluster Index, it will store the data into other filegroup instead of default.
By creating nonclustered indexes on a different filegroup, you can achieve performance gains if the filegroups are using different physical drives (luns) with their own controllers whereby Data and index information can then be read in parallel by the multiple disk heads.