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 |
|
aex
Yak Posting Veteran
60 Posts |
 Posted - 2009-11-09 : 03:54:44
|
| In sql server, we can partition a given table into multiple file groups, but I am not really sure the partition works especially the query time and insert time.Say I only have one disk in my server. Then, I create my database as following:1. db.mdf2. sf_1.ndf (file group 1)3. sf_2.ndf (file group 2)4. db.ldfThen, I create a table using partition function and partition scheme, which this table is 'splitted' into file group 1, and file group 2.My question is, if I insert data in the table using two different sessions at almost the same time, how sql server inserts the data? Is the insertion run concurrently or sequentially?aex |
|
|
krishnarajeesh
Yak Posting Veteran
67 Posts |
Posted - 2009-11-09 : 04:17:04
|
| Partitioning allows parallel operations into n partitions.Thanks,Krishnawww.SQLServer.in |
 |
|
|
aex
Yak Posting Veteran
60 Posts |
Posted - 2009-11-09 : 04:27:51
|
| Thanks krishna. So, do you mean the parallel operations are available even in one hard disk environment?I always thought that the parallel operations can be done only if the table partition is created across multiple hard disk, like,1. db.mdf -> C:2. sf_1.ndf (file group 1) -> D:3. sf_2.ndf (file group 2) -> E:4. db.ldf -> C:I would just like to clarify again to make sure how thing works.aex |
|
|
|
krishnarajeesh
Yak Posting Veteran
67 Posts |
Posted - 2009-11-09 : 12:12:12
|
| If you have multiple CPUs, SQL Server process operations parallely accross partitions, even if its with in same/one hard disk.Thanks,Krishnawww.SQLServer.in |
|
|
|
cvraghu
Posting Yak Master
187 Posts |
Posted - 2009-11-11 : 02:10:17
|
| BOL: Too many parallel threads might cause bottlenecks in the disk I/O subsystem. It is advantageous to get as much data spread across as many physical drives as possible in order to improve throughput through parallel data access using multiple files. To spread data evenly across all disks, first set up hardware-based disk striping, and then use filegroups to spread data across multiple hardware stripe sets if needed. http://technet.microsoft.com/en-us/library/cc917578.aspx |
|
|
|
|
|
|
|
|
How database table partitioning work?