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

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]

/****** 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]

USE [TimeMatters10]

/****** 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
ON [Alternate] --Instead of primary 
  • Can you post the exact scripts you used to rebuild the indexes, in particular the clustered? How big are the tables involved i.e. how much space did you expect to be consumed in the new FG? If you script the clustered index definition for the tables, where does it show they are located? Just wondering if the index naming is messed up, such that your sys.indexes query maybe showing PK_MyTable or CL_MyTable on the new FG but its actually a NC. – Mark Storey-Smith Aug 16 '11 at 16:00
  • @Mark I added the script above its the standard drop and create script ... I d / c clustered followed by NC 'd – bumble_bee_tuna Aug 16 '11 at 16:44

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?

  • Thanks Mark I'm using am querying sys.dm_io_virtual_file_stats ... Tried above same thing ... I'm on 2008R2 is there anything different ? My initial though was that maybe I had a misconception and the feature was just using the file group for new data applied to that index. However, from what your saying my assumption that rebuilding the Clus Index on the new File Group should actually move the physical data from the primary FG to the new filegroup ? – bumble_bee_tuna Aug 16 '11 at 19:13
  • Yes, rebuilding to the new FG will move the data as the clustered index is the data. I'm inclined to think both approaches to the rebuild are working and your just not seeing as much data on the new FG as you were expecting. Was there alot of wasted space and/or fragmentation on the original tables perhaps? – Mark Storey-Smith Aug 16 '11 at 19:17
  • Hi Mark, thank you for the help with this I believe it most likely that the objects were smaller then I expected and the low activity is suggesting that most of this data is actually cached in the buffer pool – bumble_bee_tuna Aug 16 '11 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.