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
  • I doubt this is going to be a worthwhile approach. How may disks per volume and in what RAID configuration? Commented May 17, 2012 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. Commented May 17, 2012 at 12:48
  • Apologies Ally, I forgot to revisit this, will do so tomorrow. Commented May 29, 2012 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 Commented May 30, 2012 at 8:19
  • PS The question from programmers is here if you are interested programmers.stackexchange.com/questions/149097/… Commented May 30, 2012 at 8:20

1 Answer 1


Bad news. You said ETL process - that means heavy data loads, and inserts/updates/deletes have a single disk bottleneck: the transaction log file. You can add multiple transaction log files, but they're used sequentially, not load-balanced (except in some really edge-case scenarios.)

Instead, what you need is multiple databases. In data warehouses, you typically use a different source database than a destination database, like a staging database. That way, you can put its databases on different drives (and even different classes of drives). For example, some staging databases live on cheap local SSDs.

If you do find yourself storage-bottlenecked on data file reads or writes, you're better off solving that problem by adding memory (to cache the data and avoid reads altogether) or by striping the data across more drives, not less. By splitting the data files into smaller pieces, and giving them each fewer drives, you're usually making the problem worse.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.