SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Using partition table with multiple disk drives?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

langthang
Starting Member

Vietnam
1 Posts

Posted - 11/20/2012 :  02:37:51  Show Profile  Reply with Quote
let's assume that I have scenario as following:
- Table tbl1 for customer 1, table tbl2 for customer 2.
- tbl1 has one relative table named tbl1_detail. Same for tbl2.
- I partition tbl1 and tbl1_detail into 4 months. Same for tbl2.
- Clustered index and nonclustered index have been set.
- I use 4 disk drives. Each drive stores 1 partition.

                  drive 1 drive 2 drive 3 drive 4
tbl1              m1     m2     m3     m4
tbl1_detail   m1     m2     m3     m4
tbl2              m1     m2     m3     m4
tbl2_detail   m1     m2     m3     m4

with 2 tables tbl1 and tbl1_detail, if I query them with INNER JOIN, it just query on only 1 driver1, right? but I want other drives need to be used, it meant that different month will be in different disk drive, so, I change a little bit as follow:

drive 1 drive 2 drive 3 drive 4
tbl1 m1 m2 m3 m4
tbl1_detail m2 m3 m4 m1
tbl2 m2 m3 m4 m1
tbl2_detail m1 m2 m3 m4


so, my questions here are:

1. with tb1, when I query m1 (month 1) and m2 (month 2), how the SQL work with drive 1 and 2?

2. with query tbl1 inner join tbl1_detail in month 1, will the SQL work in parallel by querying data in drive 1 and drive 4 in the same time? OR SQL will query data at drive 1 first, and query data at drive 4 then?

3. with query tbl1 and tbl2 by UNION ALL for month 1, will the SQL work in parallel at drive 1 and drive 4? OR SQL will query data at drive 1 first, and query data at drive 4 then?

4. What I want here is how to improve querying data, show result faster by using all available disk drives. Do you have any idea about this? do we have any algorythm to set up partition on different disk drive?


Thank a lot.

srimami
Posting Yak Master

160 Posts

Posted - 11/21/2012 :  03:58:43  Show Profile  Reply with Quote
Needs clarification on the following:

1. How many file groups are you creating?
2. How many files for each file group?

Answers to your questions:

1. Partitioning is a logical term where it partitions on the boundary values we define. When you query m1 and m2, it will pick up from respective partitions/disks where data is stored
2. Yes, it will work in parallel
3. With Union All, it will retrieve first select in parallel and second select in parallel then merge the data with Union
4. In Sql Server, it is always advisable and recommended to use multiple disk drives to store the data files for faster retrieval

Note: You can use execution plan on how the Sql Server Engine fetches data when you query it.

Hope to be clear in the above.

Thanks,
Sri.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000