Introduction

If you have some tables that are taking over 10% of the table space in SQL Server (see this post for finding large tables), they are candidates for being moved to their own filegroup (see appendix below for ‘ how to create a new filegroup ’).

To move the table data to the new filegroup, we need to move the clustered indices to the new filegroup (since the clustered index is the table data).  The example below assumes a (large)table called FileAttachments with a clustered index on the ID column. A new filegroup (a SECONDARY filegroup) was created and named new_attachments_filegroup (see the Appendix below for how to create a new filegroup in SQL Server).

CREATE CLUSTERED INDEX CIX_FileAttachments

   ON dbo.FileAttachments(ID)

   WITH DROP_EXISTING

   ON [new_attachments_filegroup]

Once you execute this, if you right click the table’s properties—>Storage , it should show the newly created filegroup as its storage location.

The above might not work if all you have is a PRIMARY KEY constraint on the table (and the default clustered index that is created along with it). In order to deal with that, follow the steps below:

  1. Drop the existing primary key constraint on the table (right click the constraint, delete)
  2. Recreate it using ALTER table as shown below (column name is ID) 

ALTER TABLE dbo.FileAttachments ADD PRIMARY KEY(ID) ON [new_attachments_filegroup]

Summary

Moving large tables to their own filegroup (preferably on a separate drive) is an important step in improving your database performance. To move the table in two steps:

  • a) Create the new filegroup first.
  • b) Move the data to the new filegroup using the ‘create clustered index’ or ‘add primary key’ technique outlined in this post.

Appendix – Creating a new filegroup in SQL Server using Management Studio (from technet)

NOTE: Look at step 9 – setting initial size of the file. Set this to the known current size of the table you are trying to move (for e.g. if your table is 30GB, this initial size needs to be 3000 MB).

 

To add data or log files to a database

  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

  2. Expand Databases, right-click the database from which to add the files, and then click Properties.

  3. In the Database Properties dialog box, select the Files page.

  4. To add a data or transaction log file, click Add.

  5. In the Database files grid, enter a logical name for the file. The file name must be unique within the database.

  6. Select the file type, data or log.

  7. For a data file, select the filegroup in which the file should be included from the list, or select <new filegroup> to create a new filegroup. Transaction logs cannot be put in filegroups.

  8. Specify the initial size of the file. Make the data file as large as possible, based on the maximum amount of data you expect in the database.

  9. To specify how the file should grow, click (…) in the Autogrowth column. Select from the following options:

    1. To allow for the currently selected file to grow as more data space is required, select the Enable Autogrowth check box and then select from the following options:

    2. To specify that the file should grow by fixed increments, select In Megabytes and specify a value.

    3. To specify that the file should grow by a percentage of the current file size, select In Percent and specify a value.

  10. To specify the maximum file size limit, select from the following options:

    1. To specify the maximum size the file should be able to grow to, select Restricted File Growth (MB) and specify a value.

    2. To allow for the file to grow as much as needed, select Unrestricted File Growth.

    3. To prevent the file from growing, clear the Enable Autogrowth check box. The size of the file will not grow beyond the value specified in the Initial Size (MB) column.

  11. Specify the path for the file location. The specified path must exist before adding the file. Click OK.

Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.

Initial Consultation

Anuj Varma – who has written posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.

 Twitter Linkedin