Archive for October 29th, 2012

SQL Server: How to find mapping of tables with filegroups October 29th, 2012

Vinod Kumar

One of the recommendations I give to customers coming to MTC is to do proper placement of tables to appropriate filegroups. The default behavior is – SQL Server would create a default PRIMARY filegroup and all the data would be placed into the .mdf file. And this is something I have seen at a lot of ISV’s forget to work on and configure.

Recently, when this recommendation was given, one of the DBA did ask me – “How do we know which table is in which filegroup? Is there an instant script for the same?”. Well, there are a number of ways to find this information and I am attaching the same for reference here.

Method 1: sp_help

The simplest and the old technique is to use our proven sp_help. The last result set has the filegroup this table dbo.t1 is associated with.

sp_help ‘[dbo].[t1]’

A typical output looks like below:


Method 2: sp_objectfilegroup

The limitation of the method 1 is that it gives in different result set and it is difficult to take this information for future use. Here is an undocumented procedure which can get you this information.

DECLARE @ObjectID INT = (Object_id(‘[dbo].[t1]’))
EXEC sys.sp_objectfilegroup @ObjectID

The output for the above call is:


Method 3: DMV’s

Since SQL Server 2005 we have DMVs that give us important information and interesting insights that we are not aware off. And this information is also hidden inside few DMVs like the query below.

SELECT AS Data_located_on_filegroup
FROM   sys.data_spaces d
               FROM   sys.indexes i
               WHERE  i.object_id = OBJECT_ID(‘[dbo].[t1]’)
                      AND i.index_id <= 1
                      AND d.data_space_id = i.data_space_id)


The output of this query would look like:


A small extension to the same query can be made in such a way that we can get all the tables and the filegroups these tables are mapped to. So feel free to use the same.

SELECT OBJECT_NAME (a.object_id) AS ObjectName,
       (SELECT name
        FROM   sys.data_spaces
        WHERE  data_space_id = a.data_space_id) AS FileGroupName
FROM   sys.indexes a
WHERE  index_id <= 1 

I just ran this on my AdventureWorks2012 DB to get the output as below.


Thanks for reading so far. Hope this query will help you plan placing your tables in the right filegroups.

Continue reading...