partition table,File group on Single hardDrive

Question

partition table,File group on Single hardDrive

fawwad

SSC Eights!

Points: 965
More actions
December 21, 2010 at 9:48 pm

#232189

Hi All,
I was reading about partition tables in sql server 2008.
there are few questions in my mind.
1)Do we get any benefit if we have Multiple FILEGROUPS on same Hard drive with different partitions (c,d,e,f) ??? i dont think so, since Disk Spindle is same!!! please correct me
2) Ok next What if i create Partition table and distribute data in different file groups this is how it suppose to work but again on SAME SINGLE hard disk, ANY ADVANTAGE ?? (performance!!!)
please clear my confusion...

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

endo64 Ten Centuries Points: 1012 More actions · Answer 1

Hi,

Multiple files gains some performance since SQL Server uses multiple threads to read data. If those file are on different physical drives then you gain some more performance.

If your database is very large and very busy, multiple files can be used to increase performance. Here is one example of how you might use multiple files. Let's say you have a single table with 10 million rows that is heavily queried. If the table is in a single file, such as a single database file, then SQL Server would only use one thread to perform a read of the rows in the table. But if the table were divided into three physical files, then SQL Server would use three threads (one per physical file) to read the table, which potentially could be faster. In addition, if each file were on its own separate physical disk or disk array, the performance gain would even be greater.

Here is the article http://www.sql-server-performance.com/tips/filegroups_p1.aspx

grahamc SSCertifiable Points: 6762 More actions · Answer 2

There can be gains on partitioning the data, if appropriate. Ideally you should be selecting small sets of data and if the partition is appropriate then you will be selecting from a smaller subset of the total data (at least most of the time).

Hopefully that makes sense 🙂

fawwad SSC Eights! Points: 965 More actions · Answer 3

thanks to both of ou for replying on the post.

Yes i do agree, there is improvement with Separate physical hard drives

but my concern was what in case of SINGLE HARD DRIIVE !!!

is it still meaningful to have multiple filegroups ??

or similarly if we go for Partition table option on (single hard drive)

endo64 Ten Centuries Points: 1012 More actions · Answer 4

Hi, as I said in my first post, it is still worth because SQL Server creates a thread to read data for each file even those files are on same drive.

If you have 3 files on same drive SQL Server uses 3 threads to fetch data which potentially faster than using 1 thread and 1 file. Of course it depends on your query.

grahamc SSCertifiable Points: 6762 More actions · Answer 5

and as I said (even on a single disk) with partition, SQL Server has look at much smaller subset(s) of data in order to retrieve the row(s) in question.

So the answer Yes, the real question is how much benefit is there VS the amount of work/time/effort to setup/maintain.

fawwad SSC Eights! Points: 965 More actions · Answer 6

fawwad

SSC Eights!

Points: 965

December 22, 2010 at 11:05 am

#1265626

Thank you very much to both of you:-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

endo64 (12/22/2010)
Hi,
Multiple files gains some performance since SQL Server uses multiple threads to read data.

It is a myth that SQL uses one thread per data file to read data.

http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

Multiple files on the same set of physical drives are unlikely to gain anything. If you're just looking for the partition switch (rolling window) scenario for partitioned tables, they can all be in the same filegroup. If you're partitioning for performance, you pretty much need separate physical drives (and an IO-bound process) to get anything substantial.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass