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,
CONSTRAINT PK_TestData PRIMARY KEY CLUSTERED (TestDataID)
)
GO

-- Insert data
INSERT INTO TestData(Src, Dest, seq)
 SELECT 'A', 'B', 1
 UNION ALL
 SELECT 'A', 'C', 1
 UNION ALL
 SELECT 'C', 'D', 2
 UNION ALL
 SELECT 'A', 'D', 3
 UNION ALL
 SELECT 'B', 'D', 3
GO
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

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.

share|improve this answer
add comment

Your Answer

 
discard

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

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