Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
langthang
Starting Member
1 Post |
 Posted - 2012-11-20 : 02:37:51
|
| 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 m4with 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 m4so, 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 - 2012-11-21 : 03:58:43
|
| 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 stored2. Yes, it will work in parallel3. With Union All, it will retrieve first select in parallel and second select in parallel then merge the data with Union4. In Sql Server, it is always advisable and recommended to use multiple disk drives to store the data files for faster retrievalNote: 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. |
 |
|
|
|
|
|
|
|
Using partition table with multiple disk drives?