Moving Tables Indexes to Separate file / file-groups, What Happens?

Question

I'm trying to get a grasp on whats going on when you move objects to a separate filegroup / .ndf file.

*This is for a 3rd party app that sucks that I have no access to source, and is entirely Ad-Hoc OLTP workload.

My setup is datafile's on BB 4G cached raid 5 array. (Very fast write speeds / Slower then desired reads)

Logs on Mirrored Solid States (Fast Reads / Fast Enough Writes) array

tempdb on 2nd Mirrored Solid States (Fast Reads / Fast Enough Writes) array

My plan was to move objects with high reads / very low writes to a 3rd SSD array (Very Fast < 1ms Read). I moved a good amount of tables and indexes by dropping the Clustered Index and Rebuilding on the Secondary File Group. When I run

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables
order by f.name
GO

It Show the objects are indeed on the secondary filegroup. However when I look at the reads it show that the filegroup is not being used. Also when I look at the size:

SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total   Size in MB],
 size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In    MB], [file_id]
FROM sys.database_files;

It looks like very little of the .ndf datafile is being utilized. I tried rebuilding the indexes, same thing.

So long story short my question is what happens when you drop / rebuild on a secondary filegroup. Am I going about this the wrong way ?

Thanks in advance

Edit -- at Marks Request

USE [TimeMatters10]
GO

/****** Object:  Index [matter_k__2]    Script Date: 08/16/2011 12:41:07 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[lntmuser].   [matter]') AND name = N'matter_k__2')
ALTER TABLE [lntmuser].[matter] DROP CONSTRAINT [matter_k__2]
GO

USE [TimeMatters10]
GO

/****** Object:  Index [matter_k__2]    Script Date: 08/16/2011 12:41:07 ******/
ALTER TABLE [lntmuser].[matter] ADD  CONSTRAINT [matter_k__2] PRIMARY KEY CLUSTERED 
(
    [sysid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,      IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,    FILLFACTOR = 90) 
ON [Alternate] --Instead of primary 
GO

Answer 1

Your example should indeed work but it's not the recommended approach. By doing a drop/create on the CL followed by a drop/create in the NC, you're rebuilding the NC indexes 3 times.

• Drop CL = table converts from cluster to heap, which requires NC rebuild
• Create CL = table rebuilds from heap to cluster, which again rebuilds NC
• Drop/Create NC = rebuilds them again

Despite your approach not being the fastest way to do this, it should work. Can't explain what's going wrong but can suggest a different method. Can you try the following:

1. Drop NC indexes

2. Run the following to drop the PK/CL and rebuild on the new FG

   ALTER TABLE [lntmuser].[matter] DROP CONSTRAINT [matter_k__2] GO

   CREATE UNIQUE CLUSTERED INDEX CLIX_matter_sysid ON [lntmuser].[matter] (sysid ASC) -- Add appropriate fill factor option etc ON [Alternate] GO

   ALTER TABLE [lntmuser].[matter] ADD CONSTRAINT PK_Matter PRIMARY KEY (sysid) GO

3. Create the NC indexes

If that works, we can go back to working out why your first script failed.

Edit: Just in case... you mentioned checking reads for the new filegroup. What are you using to check this? Are you looking at sys.dm_io_virtual_file_stats and seeing no change as the tables are fully cached in the buffer pool?