Move table to filegroup

Why?

Dealing with a database that has been around for some time may require some basic reconfiguration. In this case I had one (or more...) database that had some performance issues. One major cause was that the database only had one datafile (mdf) and the I/O was running serial. I could add some extra datafiles, but to benefit from more datafiles they should be of equal size. This would either require a massive extra amount of storage or a trade off.
I went for a solution with one new filegroup that had several datafiles for parallel I/O. To use the new filegroup I had to move data to the new filegroup. Usually by moving clustered indexes. The data was chosen with the developers and expert users on what was used most for central activities.

Prepare storage

Due to the IT infrastructure I work in I added some extra drives to the database server. On each drive I created a folder in the root to hold the datafiles.
To make the folders useful to the database service the service account was made owner of the folder. As we use Active Directory Managed Service Accounts (MSA) as service account for SQL Server services the service account name must be entered with a trailing dollar-sign.

Get index info

This statement I used to get some basic info about the indexes on a given table, so I could have an idea on what I was about to touch.
SELECT
  [tables].[name] AS [table_name],
  [schemas].[name] AS [schema_name],
  [indexes].[name] AS [index_name],
  [idx_phys_stats].[index_type_desc],
  [idx_phys_stats].[page_count]*8 AS [index_size_in_kb],
  ([idx_phys_stats].[page_count]*8.0)/(1024*1024) AS [index_size_in_mb]
FROM [master].[sys].[dm_db_index_physical_stats](DB_ID('BoPind'),OBJECT_ID('STOCKFLOW'),NULL,NULL,'LIMITED') AS [idx_phys_stats] -- filter by parameter values
  INNER JOIN [sys].[tables] ON [idx_phys_stats].[object_id] = [tables].[object_id]
  INNER JOIN [sys].[schemas] ON [tables].[schema_id] = [schemas].[schema_id]
  INNER JOIN [sys].[indexes] ON [idx_phys_stats].[index_id] = [indexes].[index_id] AND [idx_phys_stats].[object_id] = [indexes].[object_id];
The statement is inspired by the answer by Mikael Eriksson to the question „T-SQL query to get Index fragmentation information“ on stackoverflow (StackExchange).

Create Filegroup

ALTER DATABASE [my_database] ADD FILEGROUP [standard];
ALTER DATABASE [my_database] ADD FILE (
NAME = N'standard_0',
FILENAME = N'D:\MSSQL.Data\my_database.standard_0.mdf',
SIZE = 42GB,
FILEGROWTH = 1GB ) TO FILEGROUP [standard];
ALTER DATABASE [my_database] ADD FILE (
NAME = N'standard_1',
FILENAME = N'D:\MSSQL.Data\my_database.standard_1.ndf',
SIZE = 42GB,
FILEGROWTH = 1GB ) TO FILEGROUP [standard];
etcetera...

Setting the filegroup as default
ALTER DATABASE [my_database] MODIFY FILEGROUP [standard] DEFAULT;

The existence of the filegroup can be verified immidetaly with
SELECT * FROM [my_database].[sys].[filegroups];

Move table

The data are in a clustered index – if such is defined on the table. This article „Move an Existing Index to a Different Filegroup“ has a central remark about using SQL Server Management Studio GUI to move a table to anorther 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.

This makes sense as uniquenes is a central property of a primary key. Remember that a primary key does not require a clustered index and vice versa.

Move clustered index

Generating a initial script using Management Studio is a quick way to get a good start.
USE [my_database];
CREATE CLUSTERED INDEX [IX_1st_table] ON [dbo].[1st_table] (
  [1st_table_id] ASC,
  [1st_table_col2] ASC,
  [1st_table_col3] ASC
)WITH (DROP_EXISTING = ON, ONLINE = OFF) ON [standard];
The ONLINE = OFF part is to be sure not to collide with KB2969896 on installations not fully patched.

Move non-clustered index

Generate initial script using Management Studio...
USE [my_database];
CREATE NONCLUSTERED INDEX [ix_2nd_table] ON [dbo].[2nd_table] (
  [2nd_table_code] ASC
)
INCLUDE (
  [2nd_table_col2],
  [2nd_table_col3]
) WITH (DROP_EXISTING = ON, ONLINE = OFF) ON [standard];
This is also an example on moving a including index. But that does not make a signifikant difference.

Move unique (clustered) index

In this case I didn't get much help from Management Studio script generation. Most of the script is done by hand.
USE [my_database];
CREATE UNIQUE CLUSTERED INDEX [PK_3rd_table] ON [dbo].[3rd_table]([3rd_table_id])
WITH (DROP_EXISTING =  ON )
ON [standard];

Move Primary Key index

Also inn this case I didn't get much help from Management Studio script generation.
Please notice that the activity is by several statements. To ensure the integrity of the data the statements are put in a transaction.
BEGIN TRANSACTION [move_pk_idx] WITH MARK 'Change 42';
USE [my_database];
ALTER TABLE [dbo].[4th_table] DROP CONSTRAINT [PK_4th_table];
ALTER TABLE [dbo].[my_table] ADD  CONSTRAINT [PK_4th_table] PRIMARY KEY NONCLUSTERED ([4th_table_id] ASC)
WITH (ONLINE = OFF) ON [standard];
COMMIT TRANSACTION [move_pk_idx];
This solution can be used for other constrains like foreign key.
I was inspired by this discussion on StachExchange: „Move Primary Key to Filegroup (SQL Server 2012)“.

Discussion

When moving large amounts of data I wrapped the T-SQL statements in a timing structure like discribed in „Job history template using SQL Server Agent tokens“.