Moving Tables Indexes to Separate file / file-groups, What Happens? 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 &lt; 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
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:


Drop NC indexes
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
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?