Move table to filegroup


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.
  [tables].[name] AS [table_name],
  [schemas].[name] AS [schema_name],
  [indexes].[name] AS [index_name],
  [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];
NAME = N'standard_0',
FILENAME = N'D:\MSSQL.Data\my_database.standard_0.mdf',
SIZE = 42GB,
NAME = N'standard_1',
FILENAME = N'D:\MSSQL.Data\my_database.standard_1.ndf',
SIZE = 42GB,


Setting the filegroup as 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

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

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])
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];

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)“.


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“.


Code Region in Management Studio

In PowerShell ISE I am used to put the code in collapsable/expandable blocks with #region, but that is not direct possible in SQL Server Management Studio.

But this entry in stackoverflow gives a workaround: "sql server #region".
The precise details on how to configure Management Studio is given by Buck Woody in the elder blog post "Code Collapse and Expand Feature in SQL Server Management Studio 2008".
Actually Buck gives the whole workaround, but my search gave me stackoverflow first...

A short example:
BEGIN -- Region Title
END -- Region Title

If you put a space or two last in the region title the region collapse in a visual pleasant way.
I repeat the region title at the end of the block. Sometimes there are more than one screen full in one block, and this way I am reminded about the context of the END-line.

And it works fine in Management Studio 2014, also with SQL Prompt installed.