Thursday, March 13, 2014

Moving Tables and Index between filegroups in sql server

Moving Tables & Indexes between Filegroups

In my previous posts I discussed about Filegroups, we will see very brief summary of filegroup again in this post.

Filegroup - A filegroup is a logical structure to group objects in the database. We can create multiple filegroups in the database, the default filegroup is PRIMARY for any database. We can create filegroups for two main reasons 1) Performance and 2) Recovery
Performance - For suppose if a filegroup exists on D:\drive has very large table that is getting frequently accessed and you are receiving high IO, in that case we can move the large table to new drive where it has high IO handling capacity for better performance.

Recovery – Filegroups can be backed up and recovery individually, this can enable faster recovery in case of disaster. In disaster we can recover the primary filegroup first and make database accessible and then we can recover archived data filegroups which is not frequently used by users.

Sometimes we may need to move objects between filegroups to achieve performance or moving data to another drives or to archive the unused tables into different drive in a separate filegroup.
Select * from sys.filegroups -- Shows how many files groups are associated to the database

If there are more than one filegroup exists then we need to identify default database by seeing Is_default = 1 value.

Below query helps to identify the object name and associated file group to it.

SELECT 'Object Name' = OBJECT_NAME(IDX.object_id), 'Index Name' =,
 'FG Name' = FROM sys.indexes IDX INNER JOIN sys.filegroups FG ON IDX.data_space_id = FG.data_space_id

Now we will add new filegroup to database
Alter database KalyanDB add FileGroup SecondaryFG
Select * from sys.filegroups

ALTER DATABASE KalyanDB  ADD FILE (NAME = 'Secondary_File',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008\MSSQL\DATA\secondary.ndf')  TO FILEGROUP SECONDARYFG

Below query displays file group and associated physical file to it
select as filegroup, (case when fg.is_read_only =0 then 'Write Mode' else 'Read Mode' end),, f.physical_name from sys.filegroups fg, sys.database_files f
where f.data_space_id = fg.data_space_id

We will create two tables, One in default file group and other in secondary file group

CREATE TABLE Table1_PrimaryFG( [ProductID] [int] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL,  [ProductNumber] [nvarchar](25) NOT NULL ) ON PRIMARY

CREATE TABLE Table2_SecondaryFG([ProductID] [int] IDENTITY(1,1) NOT NULL,
[Name] [dbo].[Name] NOT NULL, [ProductNumber] [nvarchar](25) NOT NULL       
Run the below query to identify the table1 and table2 in which file group

SELECT 'Object Name' = OBJECT_NAME(IDX.object_id), 'Index Name' =,
'FG Name' = FROM sys.indexes IDX
INNER JOIN sys.filegroups FG ON IDX.data_space_id = FG.data_space_id
where OBJECT_NAME(idx.object_id) in ('Table2_SecondaryFG','Table1_PrimaryFG')
To achieve moving objects while creating clustered index on the table we can specify the filegroup so the table will automatically moved to the other filegroup.
Below create commands will move table to SecondaryFG file group

Create clustered index idx_table1 on Table1_PrimaryFG(ProductID) on SecondaryFG
Create clustered index idx_table2 on Table2_SecondaryFG(ProductID)

Below syntax is used to move table when already index is exists on the table.

CREATE CLUSTERED INDEX [idx_table1] ON [dbo].[Table1_PrimaryFG] ([ProductID] ASC )WITH (DROP_EXISTING = ON, ONLINE = ON) ON [PRIMARY] 


 Another query to identify objects with file group information.

  SELECT object_name(i.[object_id]) as Name_of_Object, as Index_Name,
  i.type_desc as Index_Type, as Name_of_Filegroup, a.type as Object_Type,
  f.type, f.type_desc FROM sys.filegroups as f  INNER JOIN sys.indexes as i 
  ON f.data_space_id = i.data_space_id INNER JOIN sys.all_objects as a  
  ON i.object_id =     a.object_id WHERE a.type ='U'
   -- User defined tables only
   AND object_name(i.[object_id]) in ('Table2_SecondaryFG','Table1_PrimaryFG')


No comments: