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.
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 d.name AS Data_located_on_filegroup
FROM sys.data_spaces d
WHERE EXISTS (SELECT 1
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,
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.
This entry was posted on Monday, October 29th, 2012 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.