Where Are Your Tables And Indexes Stored?

Recently, I started receiving multiple database filegroup growth alerts from my Idera SQL Diagnostic Manager. As this wasn't usual, I wanted to find out if this was unexpected data growth or was it developers creating tables without clustered indexes and or indexes in the primary filegroup.

If there are tables without clustered indexes, we will need to create them, and if there are clustered indexes or non-clustered indexes created in the primary filegroup, we will need to get them moved to my custom-created filegroup for data and or indexes.

First, I need to write a query to see all my databases and tables to see what was going on. Here is what I came up with.

SELECT
    db_name(),
    i.name AS IndexName,
    t.name AS TableName,
    t.type_desc TableType,
    i.type_desc AS IndexType,
    fg.name AS FilegroupName
FROM
    sys.indexes AS i
    INNER JOIN sys.tables AS t
	ON t.object_id = i.object_id
    INNER JOIN sys.filegroups AS fg
	ON fg.data_space_id = i.data_space_id
WHERE
    t.type_desc = 'USER_TABLE' --Only interested in user tables
    AND fg.data_space_id = 1 --Primary file group
    AND db_name() NOT IN ('msdb', 'master','tempdb','ssisdb','ReportServerTempDB','ReportServer')

Let me break this down quickly. I'm looking at the indexes table because that has the clustered, heap, or non-clustered index specification as well as the filegroup the data is stored in. Then we join to sys.tables for the table name, and then finally the filegroups table. We are ignoring the system databases (but you may want to check those in case a developer snuck in a table by accident) and SSIS, and Reporting databases. We are only looking at 'User Tables', which are in the 'PRIMARY' filegroup, not in the custom group I created for that which is 'FGData'. As you can see we have three results with one heap, one clustered and a non-clustered index, stored in the 'PRIMARY' filegroup.

We need to move the heap and clustered index to the filegroup we created called 'FGData' and the non-clustered index to the filegroup created we called 'FGIndexes'. The way we will handle clustered indexes is by dropping the current constraint and creating it again on the correct filegroup. The key is to specify the filegroup on creation as you see below in the highlighted text 'ON [FGData]'. I would recommend you run this in a transaction, that way you are blocking any other changes or inserts while you are moving the data.

BEGIN TRANSACTION
ALTER TABLE [dbo].[DemoUTF8] DROP CONSTRAINT [PK_DemoUTF8] WITH ( ONLINE = OFF )
GO
ALTER TABLE [dbo].[DemoUTF8] ADD CONSTRAINT [PK_DemoUTF8] PRIMARY KEY CLUSTERED 
(
    [DemoId] ASC
) WITH (SORT_IN_TEMPDB = ON, ONLINE = OFF) ON [FGData]
GO
IF @@ERROR == 0
    BEGIN
        COMMIT TRANSACTION
    END
ELSE
    BEGIN
        ROLLBACK
    END
GO

Next, we need to create a primary key clustered index on the heap table. Again, be sure to specify the filegroup.

ALTER TABLE [dbo].[HeapTable] ADD CONSTRAINT [PK_HeapTable] PRIMARY KEY CLUSTERED 
(
    [TestId] ASC
) WITH (SORT_IN_TEMPDB = ON, ONLINE = OFF) ON [FGData]
GO

Now you can see we only have the non-clustered index left.

Now we can script out the creation of the non-clustered index and change the drop_existing value to 'ON' and change the filegroup to 'FGIndexes'.

CREATE NONCLUSTERED INDEX [IX_DemoUTF8] ON [dbo].[DemoUTF8]
(
    [UTF8Column] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF) ON [FGIndexes]    
GO
    

If you don't want to attempt to manually do this, you could use RedGate SQL Compare, change the filegroups in your source control then point to your database and sync them.

Here is what it would look like when comparing and making those changes in RedGate SQL Compare.

Change Primary Key Clustered Index Filegroup, Change Non-Clustered Index Filegroup

Adding Primary Key Clustered Index to Heap Table

If you noticed I also used 'SORT_IN_TEMPDB = ON', I like to use this to minimize the growth of the filegroup during the rebuild. If you have multiple servers and databases you want to check, I'd recommend using a tool like RedGate Multi-Script to run the query once and look at everything. Below is a script I put together which uses a temp table and sp_MSforeachdb to accomplish that.

CREATE TABLE #ObjectsInPrimary
(
    DatabaseName VARCHAR(128) NOT NULL,
    IndexName sysname NULL,
    TableName sysname NOT NULL,
    TableType NVARCHAR(60) NULL,
    IndexType NVARCHAR(60) NULL,
    FileGroupName sysname NOT NULL
);

EXEC sys.sp_MSforeachdb @command1 = N'use ?;

INSERT INTO #ObjectsInPrimary
(
    DatabaseName, 
    IndexName, 
    TableName, 
    TableType, 
    IndexType, 
    FileGroupName
)
SELECT
    db_name() AS DatabaseName,
    i.name AS IndexName,
    t.name AS TableName,
    t.type_desc TableType,
    i.type_desc AS IndexType,
    fg.name AS FilegroupName
FROM
     sys.indexes AS i
     INNER JOIN sys.tables AS t
         
                        ON t.object_id = i.object_id
     INNER JOIN sys.filegroups AS fg
         ON fg.data_space_id = i.data_space_id
WHERE
    t.type_desc = ''USER_TABLE'' --Only interested in user tables
    AND fg.data_space_id = 1 --Primary file group
    AND db_name() NOT IN (''msdb'', ''master'',''tempdb'',''ssisdb'',''ReportServerTempDB'',''ReportServer'')'
  
SELECT
    DatabaseName,
    IndexName,
    TableName,
    TableType,
    IndexType,
    FileGroupName
FROM
    #ObjectsInPrimary;

DROP TABLE IF EXISTS #ObjectsInPrimary;

If you use RedGate Multi-Script, it will compile all of the servers' results into one. Here is how it looked for me.

You may ask why do we want to move database objects out of the primary filegroup? Well, there are a few reasons. It helps keep Data, Indexes, and Blob data separate, you can then also place each filegroup/file on separate drives which would allow for better performance on a SAN. You could also have multiple files for each filegroup and store each file on different drives on a SAN. It also allows you to add files easily if any particular area needs more room and you can also backup specific filegroups.