Move clustered index to different filegroup in SQL Server

Move clustered index to different filegroup in SQL Server

Disk IO system always play vital role in performance of SQL Server. No matter how powerful processor you have and how much RAM you have, if your disk doesn’t support transaction as per your need, you won’t get benefit of processor and RAM.

Recently, we have attached very fast SAN to our production server to replace RAID-5 array which had four hard disk of 10,000RPM. I wanted to transfer all clustered index to our new SAN system for few of our highly transactional table in the first phase.

I want to share how we can efficiently move index to a different filegroup in different drive.

Let us create one sample database for demonstration and create one table with sample data and index in newly created database.

[sourcecode language=”sql”]

CREATE DATABASE ExtremeAdvice
GO

USE ExtremeAdvice
GO

IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
 DROP TABLE orders
END
GO

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO

–inserting 100000 fack rows into table
INSERT INTO orders (OrderDate, Amount, Refno)
SELECT TOP 100000
 DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
 ABS(a.object_id % 10),
 CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

–creating clustered index
CREATE CLUSTERED INDEX idx_Orders_refno ON orders(refno)
GO
[/sourcecode]

When you create database, by default there would be one default filegroup which is PRIMARY and default data file comes under PRIMARY filegroup.

Let us see where our index is located.

[sourcecode language=”sql”]
SELECT
obj.name AS TableName
, obj.type
, ind.name AS IndexName
, ind.index_id AS Index_ID
, files.name AS FileGroupName
FROM sys.indexes ind
INNER JOIN sys.filegroups files
ON ind.data_space_id = files.data_space_id
INNER JOIN sys.all_objects obj
ON ind.object_id = obj.object_id
WHERE ind.data_space_id = files.data_space_id
AND obj.type = ‘U’ — User Created Tables
GO
[/sourcecode]

Here is the screen capture for my database:

1FileGroup

Now, we shall create one more filegroup for our database and shall create one new datafile (.ndf) in new filegroup.

[sourcecode language=”sql”]
–add new filegroup
USE [master]
GO

ALTER DATABASE [ExtremeAdvice] ADD FILEGROUP [ExtremeAdviceIndex]
GO

–add new secondary data file to filegroup
USE [master]
GO

ALTER DATABASE [ExtremeAdvice]
ADD FILE (
NAME = N’ExtremeAdvice_Index’,
FILENAME = N’D:\DATA\ExtremeAdvice_Index.ndf’ ,
SIZE = 6144KB ,
FILEGROWTH = 1024KB )

TO FILEGROUP [ExtremeAdviceIndex]
GO
[/sourcecode]

Once you are done with new filegroup and data file. We shall CREATE INDEX with DROP_EXISTING = ON and we shall assign new filegroup while re-creating an index. Once you transfer clustered to different filegroup, all data will be transferred to new location with clustered index.

[sourcecode language=”sql”]
USE [ExtremeAdvice]
GO

CREATE CLUSTERED INDEX [idx_Orders_refno] ON [Orders]
(
[refno] ASC
)
WITH (
PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = ON,
ONLINE = ON,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON
) ON [ExtremeAdviceIndex]
GO
[/sourcecode]

Once, you are done with creating an index, you can confirm whether your index is moved to new location or not with following query.

[sourcecode language=”sql”]
SELECT
obj.name AS TableName
, obj.type
, ind.name AS IndexName
, ind.index_id AS Index_ID
, files.name AS FileGroupName
FROM sys.indexes ind
INNER JOIN sys.filegroups files
ON ind.data_space_id = files.data_space_id
INNER JOIN sys.all_objects obj
ON ind.object_id = obj.object_id
WHERE ind.data_space_id = files.data_space_id
AND obj.type = ‘U’ — User Created Tables
GO
[/sourcecode]

Here is the screen capture for my database.

2Filegroup

 

You can see that “idx_Orders_Refno” is  on “ExtremeAdviceIndex” FileGroup.

I have written some more articles about Index, if you want to refer, follow the links given:

  • Some basics about Index in SQL Server (Click Here)
  • Clustered Index in SQL Server (Click Here)
  • Nonclustered Index in SQL Server (Click Here)
  • Included Column Index with non clustered index in SQL Server 2005/2008/Denali (Click Here)
  • Filtered Index in SQL Server 2008/Denali (Click Here)
  • Cannot create index on view Msg 1940, Level 16, State 1, Line 1 (Click Here)
  • Calculate total size of clustered and non-clustered index for database in SQL Server 2008/2005 (Click Here)
  • Full Text Catalog and Full Text Index (Integrated Full Text Search – iFTS in SQL Server 2008 Part 2) (Click Here)
  • Do you know Index Statistics in SQL Server (Click Here)
  • Index Rebuild or Reorganize in SQL Server (Click Here)
  • Index Fillfactor in SQL Server (Click Here)
  • Find missing Index with DMVs in SQL Server 2005/2008/Denali (Click Here)
  • Find unused index in SQL Server 2005/2008/Denali (Click Here)
  • sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats in SQL Server 2005 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.