SQL Server - How to split single mdf file into multiple, and partition table data across them?

Question

In the hopes of improving SQL Server performance, I'd like to take my simple 1-file (1 mdf & 1 ldf) database and split up the mdf files (and maybe the ldf files) across multiple disk drives. I'm thinking that the most performant way to do this would be to partition the big tables across those multiple mdf files. (I'm planning just to use a partition function which is a modulus of the primary key (which is an int or bigint), like (MyTable.Id % 8), where 8 is the number of disks I have).

What's the fastest/best/correct way to do this? Like, what SQL commands or SSMS operations do I need to do to get me from where I am now to where I want to go.

Any other suggestions would be appreciated too. (Combining the drives via Storage Spaces just doesn't look like an option, because, for some reason, I'm just not getting good write IOPS performance when I do that.)

Answer 1

You don't have to involve partitioning (but could if you have another reason to). If you create a new filegroup containing the physical files you'd like and rebuild the index on the new filegroup, that should be sufficient to spread the data across those physical files.

The rebuild process will look something like this. Say the current index has the following definition:

create clustered index [CIX_foo] on dbo.foo (FooID) on [PRIMARY]; 

You could rebuild it like this:

create clustered index [CIX_foo] on dbo.foo (FooID) with (drop_existing = on) on [NewFileGroup]; 

Use whatever other options you'd normally use in creating that index (i.e. sort_in_tempdb, pad_index, online, resumable, etc). Also consider whether changing the compression setting of the existing index makes sense - you're rewriting the entire index so now would be the time!

Lastly, as a recommendation, I'd create at least two files per drive in the new filegroup. Why? If your SWAG of 8 files (and presumably 8 drives) is not sufficient for your use case, you'll have to go through the above index rebuild process again. But if you do, say two files per drive (for a total of sixteen), you can provision the new storage and merely do either a filesystem file copy (while the db is offline) or a restore specifying the new locations. Either of those should be faster than rebuilding the indexes.