Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have been assigned a task to create four file groups (one per volume) locate tables so that they are located in a manner that during an ETL process no filegroup is being read and written to at the same time.

I have stepped through all the packages, data flow tasks and procedures and views and noted which tables are being read from and to and in what sequence.

I now need write a query to determine the optimal positioning of the tables.

In terms of logic I have the following, broken it down into the following rules.

For each sequence source table and destination table can not be on the same volume. Tables can not exist on more than one volume.

I am interested to hear peoples opinion on whether this process is worthwhile but regardless I would appreciate some help resolving the query.

Below you can find the scripts to create the table and insert some test data.

Thanks in advance for any suggestions.

-- Create table
CREATE TABLE [dbo].[TestData]
    [TestDataID] [smallint] IDENTITY(1,1) NOT NULL,
    [Src] [nvarchar](50) NOT NULL,
    [Dest] [nvarchar](50) NOT NULL,
    [Seq] [int] NOT NULL,

-- Insert data
INSERT INTO TestData(Src, Dest, seq)
 SELECT 'A', 'B', 1
 SELECT 'A', 'C', 1
 SELECT 'C', 'D', 2
 SELECT 'A', 'D', 3
 SELECT 'B', 'D', 3
share|improve this question
I doubt this is going to be a worthwhile approach. How may disks per volume and in what RAID configuration? –  Mark Storey-Smith May 17 '12 at 11:59
Thanks for you time and input. Volumes are RAID 10 with 4 spindles. I am aware that the spindle count is quite low too. –  Ally Reilly May 17 '12 at 12:48
Apologies Ally, I forgot to revisit this, will do so tomorrow. –  Mark Storey-Smith May 29 '12 at 23:39
Hi Mark, don't worry too much. I asked the question on programmers too but more from the algorithm angle. I got quite a good idea of how to approach it. I think it may be one of the few times to use a cursor. In the end though I have pushed for the arrays to be rebuilt with more spindles and therefore there will only two filegroups and as such it has been easier to do manually. Thanks for your efforts –  Ally Reilly May 30 '12 at 8:19
PS The question from programmers is here if you are interested programmers.stackexchange.com/questions/149097/… –  Ally Reilly May 30 '12 at 8:20
show 3 more comments

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.