Files and Files groups in SQL server 2016

Vijay Kumar 2,016 Reputation points
2021-02-26T02:16:32.227+00:00

We having 5 file groups.

FileGroup01 --> Default FG
FileGroup02
FileGroup03
FileGroup04

So plan is,
Move top 5 largest tables from FileGroup01 and most active base tables in the default file group to new file groups on new drive (F:) to distribute IOPS more efficiently

For example:

i. CustomerTable

  1. FG CustomerTable_Data
  2. FG CustomerTable_Index

ii. SalesTable

  1. FG_SalesTable_Data
  2. FG_SalesTable_Index
    iii. ProductTable
  3. FG_ProductTable_Data
  4. FG_ProductTable_Index
    iv. AddressTable
  5. FG_AddressTable_Data
  6. FG_AddressTable_Index
    v. CategoryTable
  7. FG_CategoryTable_Data
  8. FG_CategoryTable_Index

In this case how to implement above plan technically, i mean how to write script for moving tabes to respective file groups and so on.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-03-25T22:38:01.697+00:00

    Is there any way we can cutdowm the time by dividing to multiple queries and so on? because we can't afford this much of down time in PROD the max is 1 Hr.

    How did you move them?

    For operating on big amounts of data, it is often a good idea to split up the work in batches to keep down the growth of the transaction log. But it is important that you follow the clustered index for the batching, else performance will be horrible. I would try with a batch size of 5 million rows, but it is a number that is subject to fine-tuning.

    But to be honest, I am not sure that this alone will cut down the execution time with a factor of four.

    A more aggressive idea is to insert the batches in parallel. Not all in parallel, but a few of them. This certainly will go as advanced.

    And even more advanced is to do a trickle load to the new table while keeping the old table live - so you need to take care of the updates.

    Then again, maybe you can get away with CREATE INDEX WITH (DROP_EXISTING = ON, ONLINE = ON)?

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,656 Reputation points
    2021-02-26T07:24:53.433+00:00

    i mean how to write script for moving tabes to respective file groups and so on.

    There are several technics, see for example Move SQL Server Tables to Different Filegroups

    You can define the filegroup for storage per

    • Table data
    • BLOB data in the data
    • Non clustered index data

    The data pages of the table align with clustered index, so both are always stored in the same filegroup. If you move the CI, the table moves as well.

  2. David Browne - msft 3,766 Reputation points
    2021-02-26T21:40:26.417+00:00

    Move top 5 largest tables from FileGroup01 and most active base tables in the default file group to new file groups on new drive (F:) to distribute IOPS more efficiently

    Putting different objects on different drives, putting non-clustered indexes on a different drives, or putting blob data on different drives is almost certian to give you very uneven resource utilization, with some drives under-utilized and others over-utilized.

    If you want to distribute IOPS efficiently don't move objects between filegroups. Just add files to the existing filegroup(s) on the new drive. If you want the IOPS spread evenly on two drives, have the file group(s) have the same number and size of files on both drives.

    If some of the files are initially more empty, SQL Server's "proportional fill" behavior will fill them up and eventually you will have your data spread evenly across all the files in the filegroup. If you want to speed this process up, you can rebuild some indexes after adding the new files.

  3. Erland Sommarskog 100.8K Reputation points MVP
    2021-02-26T23:08:03.757+00:00

    I think David's suggestion is the best, but if you still want to pursue your idea you can move an index with: 

    CREATE INDEX xxx ON tbl(col1, col2) ON New_filegroup WITH (DROP_EXISTING = ON)

    You can script the current index definition and edit those.

    Moving the table is just moving the clustered index.

    But adding files is probably a better idea.

    I think using multiple filegroups is more of interesting if you have archive data that you have on slower storage, or you have data that is not essential, so that you can bring the database online fasterin case of a disaster and restore that filegroup later.

    0 comments
  4. CathyJi-MSFT 21,081 Reputation points Microsoft Vendor
    2021-03-01T07:59:15.373+00:00

    Hi @Vijay Kumar ,

    We can move an existing index from its current filegroup to a different filegroup in SQL Server by using SQL Server Management Studio or Transact-SQL. But there are some limitations and restriction for this.

    •If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.
    •You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.

    Please refer to MS document Move an Existing Index to a Different Filegroup to get more.

    Please also check if this blog Moving Existing Table From Primary Filegroup to Different Filegroup could help you.

    If the response is helpful, please click "Accept Answer" and upvote it, thank you.