Mechanism to Seperate Indexes from Actual Data pages

This is helpful while planning to Seperate Indexes from Actual data pages and moving Indexes to a New data File on to a New Disk.

1) Create New Filegroup for Indexes "Index_FG"
2) Create New Secondary Data file pointing to a new or existing disk and assign the .ndf file to the new Filegroup
3) Identify the Indexes that are residing on the Primary FileGroup (Most of the cases Data pages reside on the Primary FileGroup by Default)

SELECT DB_name() as DB,OBJECT_NAME(k.id) AS TableName,i.name AS IndexName,c.name AS ColumnName, i.reserved,i.groupid, groupname,c.colid,k.indid AS IndexIDFROM sys.sysindexes ijoin sys.sysfilegroups g ON i.groupid = g.groupidjoin sys.sysindexkeys k ON i.id = k.idand k.indid = i.indidjoin sys.syscolumns c ON k.id = c.idand k.colid = c.colidwhere i.groupid NOT IN ('5', '3')ORDER BY OBJECT_NAME(k.id),i.name, i.indid, k.keyno

4) Identify the Tables and their FileGroups

select OBJECT_NAME(object_id) as ObjectName, d.name AS FileGroup from sys.data_spaces d left join sys.indexes i on i.data_space_id = d.data_space_idwhere i.index_id<2
/**** The above query returns all the system tables as well. If you want only user tables then use below *****/
SELECT OBJECT_NAME(t.object_id) AS ObjectName, d.name AS FileGroup FROM sys.data_spaces d JOIN sys.indexes i on i.data_space_id = d.data_space_idJOIN sys.tables t on t.object_id = i.object_idWHERE i.index_id<2AND t.type = 'U'


5) Find the Indexes and thier Size. may be in some cases you may only need to Seperate the Large sized Indexes. use below Query to find the Indexes and their Size

SELECT .name AS IndexName, SUM(s.used_page_count) * 8 AS IndexSizeKB FROM sys.dm_db_partition_stats AS s JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.[object_id] = object_id('dbo.Clientinvoice') GROUP BY i.name ORDER BY i.name

6) Rebuild the Indexes by Pointing to the New FileGroup.


...Happy SQLing
Thanks for Visiting and Sharing your Views