Post reply

partition table,File group on Single hardDrive

  • 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...

  • December 22, 2010 at 2:57 am

    #1265399

    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

  • December 22, 2010 at 5:09 am

    #1265442

    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 🙂

  • December 22, 2010 at 5:53 am

    #1265456

    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)

  • December 22, 2010 at 6:04 am

    #1265461

    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.

  • December 22, 2010 at 7:06 am

    #1265498

    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.

  • December 22, 2010 at 11:05 am

    #1265626

    Thank you very much to both of you:-)

  • December 22, 2010 at 12:11 pm

    #1265651

    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

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

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