March 16, 2008 at 11:01 am
My original understanding is for best performance, one should put data (with clustered index) seperate from nonclustered index, so in my environment, I have the two in different file groups. Each file group has two files, and all four files (two for data, two for index) on 4 different LUNs. (the server is a 2CPU, dual core).
My delimma rises, when I try to implement partitioned tables in 2005. In order for me to easily swap in and out data from the partition, each range should be on ONE file group. Does that mean I have to combine the data and index for each data range into one file group?
Any insight is appreciated.
March 16, 2008 at 12:33 pm
yxl1108 (3/16/2008)
My original understanding is for best performance, one should put data (with clustered index) seperate from nonclustered index, so in my environment, I have the two in different file groups. Each file group has two files, and all four files (two for data, two for index) on 4 different LUNs. (the server is a 2CPU, dual core).
Good start. Now don't forget to make sure that those 4 LUNs are mapped to separate physical drives, otherwise you work so far wil be for nothing.
My delimma rises, when I try to implement partitioned tables in 2005. In order for me to easily swap in and out data from the partition, each range should be on ONE file group. Does that mean I have to combine the data and index for each data range into one file group?
Not according to BOL. However, if you only have two filegroups, you may be forced to choose between data/Index separation and partitioning.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply