Category Archives: Clustered Index

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.


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
 

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.

 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
 

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.

 --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
 

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.

 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
 

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

 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
 

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.