June 23, 2010 at 7:16 am
[font="Verdana"]Placing Indexes on Separate File Group(s)
Is this design approach really helpful in gaining better query performance, e.g it may certain in case of Non-Clustered Indexes and when the disks for actual data and indexes are separate, means clearing I/O bottleneck!
But let your expertise to come... !
Thank you![/font]
June 23, 2010 at 7:22 am
Maybe.
Depends what is the current bottleneck. If it's not IO, separating the data from indexes may not help. Also depends on where you're putting the files. Same spindles or separate physical disk spindles.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2010 at 9:01 am
I have found that it is sometimes helpful, especially on tables which are very large and have a lot of activity. In these cases I partition the table itself, with data being placed on one physical drive and indexes on another. I also found, in the case of a SAN (Equalogic iSCSI) that I am able to benefit by carving out seperate logical drives, and placing the data portion on a RAID5/50 logical drive and the indexes on a logical raid 10 drive. (In these cases though the logical raid 50 drives were on seperate arrays from the logical raid 10.) Smaller tables did not benefit from partitioning the data and indexes from the primary.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
June 23, 2010 at 11:23 pm
sjimmo (6/23/2010)
In these cases I partition the table itself, with data being placed on one physical drive and indexes on another.
Table partition (aka Horizontal partition) is quite different concept right?
In table partition, data itself is stored on different disks. This feature was introduced in SQL 2005.
Whereas placing data and indexes on different disks was possible in previous versions also.
June 24, 2010 at 1:43 am
Suresh B. (6/23/2010)
Table partition (aka Horizontal partition) is quite different concept right?
Yes.
In table partition, data itself is stored on different disks. This feature was introduced in SQL 2005.
Whereas placing data and indexes on different disks was possible in previous versions also.
Horizontal partitioning was possible in earlier versions too, you just had to do it manually and have a view that unioned the tables. Term is partitioned view.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply