Split sqlserver database in 2012

Question

What is the process to split sqlserver database to multiple files. Our sqlserver database is 444 GB and need to split to improve performance issue

Answer 1

Hi SahaSaha-5270,

In addition, please also check:
https://www.mssqltips.com/sqlservertip/6183/splitting-a-sql-server-table-over-multiple-files/
https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/split-a-file-group-into-multiple-data-files/ba-p/371344

Best Regards,
Amelia

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If you simply want add more files to the primary file group, it would be sufficient to rebuild the indexes, I think. Possibly you may have to do that more than once.

If the idea is to create new filegroups, you can move an index with

CREATE [CLUSTERED] INDEX  ix ON tbl(col1, col2) WITH (DROP_EXISTING = ON) ON NewFileGroup

The definition of the index should be the same as the existing one.

However, you cannot use LOB data this way. The only option in that case is to create a new table and copy over.

Like Tom, I question that you will see much benefit. But, admittedly, some people consider it to be best practice to never have user tables in the PRIMARY file group, but place them elsewhere. Myself, being a simpleminded developer, thinks that life easier with only two files per database, data file and log file.

Answer 3

With modern SAN technology, you will rarely see a performance benefit from multiple files. Especially if it is using the same IO channel.

If you are going to do it, you create the files and then drop/recreate your clustered indexes.