6

I am just about to start on an excercise to reorganise a partitioned table and I was looking for best practises or recommendations.

Let us say I have a billion row table and 8 logical disks available to me for this table.

The question I have is, is it better to create a partition scheme which divides the data into 8 filegroups and has the indexes storage aligned with the data...

Or would it be better to create 4 file groups for data and 4 for indexes (not aligned), then place each of the filegroups onto one logical disk?

Any suggestion or comments would be welcome.

|improve this question

migrated from stackoverflow.com Dec 12 '11 at 17:22

This question came from our site for professional and enthusiast programmers.

  • Just to be clear my 8 logical disks would be RAID 1+0 arrays with an unknown(as yet) number of spindles in each logical disk. – SPE109 Dec 12 '11 at 19:43
6

First and foremost: those logical disks better be backed by at least 8 different physical disks. If you're asking about load balancing 8 logical disks created on the same physical storage (the same spindles) then you're wasting time.

The best (and simplest!) option is to create a single filegroup with 8 files (equal in size and pregrown), each on a spindle, and then place the table and the indexes in this filegroup. SQL Server will balance the data equally among the files.

Partitioning is a feature for ETL switch in and switch out. It should not be used for performance, as the best you can hope for is equal performance with the original table. For performance use a well designed clustered index, one that matches the typical load.

If your data is really known upfront and the index usage characteristics are very well understood then you may try to balance them explicitly on their own filegroups. But trying to wrestle manual control over this is more likely to cause harm than benefit. The simpler option of single file group with 8 files balances IO better than manual explicit control 99% of the times.

|improve this answer
  • 4
    There are some other advantages that shouldn't be overlooked. For example, you can put your historic data in a dedicated partition, and use compression on just that partition without harming the write performance of your current data stored in another partition. But if you're just trying to spread I/O evenly across disks, then this probably isn't the way to do it. – db2 Dec 12 '11 at 17:34
  • 3
    I see. Individual partition maintenance/compression is a good reason to partition. I would still go with 1 single filegroup, the partition scheme with all partitions in this filegroup, and 8 files in the filegroup spread across the 8 logical disks. Ie. let SQL server load balance the IO and the table/index layout equally across all the disks. If you want to fine tune at higher grain (specific partition on specific disk) then a lot more data is needed and only you have that data. – Remus Rusanu Dec 12 '11 at 20:07
  • 1
    @Mark: ease of management, automatic load balancing of IO and space allocation in the files. I'm not a big fan of doing micro-management manually (clustered index goes here, index goes there etc) but if you must do it I say 'a lot more data is required' as in 'more info is needed to make a call'. – Remus Rusanu Dec 13 '11 at 1:04
  • 1
    @RemusRusanu Thanks, not something I've looked at before, I'll give it a whirl. – Mark Storey-Smith Dec 13 '11 at 9:17
  • 1
    @SPE109: separate file groups will also allow for a piece-meal restore: msdn.microsoft.com/en-us/library/ms177425.aspx – Remus Rusanu Dec 13 '11 at 17:13
2

If you want to spread your partitions over different physical volumes, you can still stick with 8 volumes. You can stagger the index partitions so that an index partition resides on a different physical volume to its corresponding data partition, e.g.

  • Partition 1: Data goes on volume 1, Index goes on volume 5.
  • Partition 2: Data goes on volume 2, Index goes on volume 6.

[ . . . ]

  • Partition 5: Data goes on volume 5, Index goes on volume 1.

. . . and so on. For a query using a small number of partitions you will at least get the benefit of spreading your data and index disk I/O.

Depending on the nature of your I/O you may be better off having all of the partitions on a single large RAID-10 as mentioned elsewhere. Of course if your controller won't let you do this (e.g. an IBM Shark) or you have multiple controllers then you will have to use multiple volumes. If you have the option I'd suggest benchmarking it before the system gets to production.

Another point to note is that some systems (DS8000s come to mind) put their physical volumes into a pool and abstract away a lot of control you have over physical disk layout. For 1B rows you might want to look into direct attach storage if this is an option.

|improve this answer
  • Thanks. That's quite an interesting idea. When users are reporting on a date range it is unlikely they would be pulling data from all filegroups in the partition and so disk load would be spread nicely. I will give this some thought. Cheers. – SPE109 Dec 13 '11 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.