question

VijayKumar768 avatar image
0 Votes"
VijayKumar768 asked ErlandSommarskog commented

Files and Files groups in SQL server 2016

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
1. FG_ProductTable_Data
2. FG_ProductTable_Index
iv. AddressTable
1. FG_AddressTable_Data
2. FG_AddressTable_Index
v. CategoryTable
1. FG_CategoryTable_Data
2. 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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @VijayKumar768,

We have not received a response from you. Did the replies could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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)?

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Or, you know, just don't move the tables or indexes to a different filegroup, and just add files to the primary filegroup.

0 Votes 0 ·

these are the steps i followed in QA: this way it took 4 hrs. now please suggest me how to split below logix and execute in parallel?

i. Created new file group
ALTER DATABASE DB1
ADD FILEGROUP NEWfilegroup;
GO
ALTER DATABASE DB1
ADD FILE
(
NAME = Newfile_Customers_Data001,
FILENAME = 'K:\MSSQLSERVER\MP2\NewFile_Customers_Data001.ndf',
SIZE = 50MB,
MAXSIZE = 512MB,
FILEGROWTH = 250MB
) ,
ii. Moved data by executing below clustered index
CREATE UNIQUE CLUSTERED INDEX PK_Customers
ON Customers (CustomerPK)
WITH (DROP_EXISTING = ON )
ON [NEWfilegroup]

0 Votes 0 ·

How do you do index rebuilds in this environment? Or maybe you don't do index rebuilds.

I ask, because this is exactly that: an index rebuild. The onl difference is that the data is moved to a different filegroup.

As I mentioned earlier, you can use ONLINE=ON, if you are on Enterprise Edition.

I also mentioned some other alternatives, but they are more advanced and require coding. Which also means that the they require more testing and that things can go wrong.

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered VijayKumar768 commented

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.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Is it possible to genere script from existing File Groups and Files?

0 Votes 0 ·
DavidBrowne-msft avatar image
0 Votes"
DavidBrowne-msft answered DavidBrowne-msft edited

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good Suggestion.

But how to move data to file01 and index to File02

Any T-SQL Script?

0 Votes 0 ·

Index rebuilds will move things around, although it may take a couple of rebuilds before things have evened out.

0 Votes 0 ·

"But how to move data to file01 and index to File02" Why would you want to do that? That stopped being a useful idea decades ago.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered VijayKumar768 commented

Hi @VijayKumar768,

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.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Based on your suggestions we tested in QA and everything went fine.
Our QA is same as PROD configuration.

There is a table called customer which took around 4 hrs to move to different filegroup. the table contains around 600 M rows.

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.

0 Votes 0 ·