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