question

munish avatar image
munish asked

Creation of Index in separate filegroup

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.
sql-server-2008sql-server-2012sql serversql server 2008 r2sql server 2008
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Just to clarify, the non-clustered index is just a pointer to the data within the clustered index (or heap if the case may be) and won't store actual data within the leaf of the non-clustered index.
1 Like 1 ·

1 Answer

·
rvsc49 avatar image
rvsc49 answered
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.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.