Table partitioning and performance

Question

Hi friends,
   I'm working on understanding how beneficial is partitioning in my case. I only one physical disk for data. i have my primary filegroup on it. In many of the articles i read they are creating multiple file groups.

1. Do i get any performance benefits by creating partitions on multiple file groups on a single physical disk
2. Alternatively can i create multiple files on single file group. is this kind of partitioning give any benefit of performance

thanks 

Answer 1

I am not sure what exactly you are looking for...

But what I understand partitioning generally used for Table and it's of two type horizontal & verticle.

Now come to your question. What you are looking for is depend on many factors like
1. What will be the database size?
2. What kind of Physical single disk are you using like storage SAN disk?
3. Are you using RAID? If yes what is RAID configuration?

Let me assuem that you are using normal Physical disk and db size is around 10 GB.

As far as you are suing single disk I don't think it will reduce I/O performance.

HTH

Answer 2

Generally partition table can be used to reduce the IO subsystem, it can be applied if u have huge data in a table.
Before applying the partition table u can just monitor the IO by using perfmon.

Raj

---

**Vote it if it is helpful. **Mark as answer if it solves your problem. Raj_79

Answer 3

No and no. Having several files or filegrups where the data in the end resides on the same disk doesn't buy you anything from an I/O pattern perspective. There can be other benefits to partitioning, though, like better parallelization of plans - but that was not your question...

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 4

1. Do i get any performance benefits by creating partitions on multiple file groups on a single physical disk
2. Alternatively can i create multiple files on single file group. is this kind of partitioning give any benefit of performance

Can you share more details with us about your HW/SW platform? Transaction volume? No of users?

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 5

hi guys,
   let me eloborate my question. i have 5 volumes (C:, D:, E:, F:, G:) on my production computer. data goes on volume D: and log goes on volume F:
   when i check the hardware i see that they are all mapped to 3 'HP LOGICAL VOLUME SCSI Disk Device'. so 5 volumes are mapped to 3 disk drives. 3 disk drives have same bus number and LUN numbers but different Target Id numbers. I dont know what kind of setup it is (SAN, RAID or whatever).
   Im not able to figure out if my C: volume is mapped to one disk drive or 3 disk drives. when i click on properties for any of these volumes i see all the 3 disk drives. My computer is running on Windows 2008 with SQL Server 2008. I can use only C: volume for my data. How beneficial to create partitions on a table where all the partitions go on C: volume.