SQL Server Move index to a new filegroup
Recreating Primary key index
Recreating Primary key index
Today I had the task of moving the primary key index to a new file group. But before I explain the procedure, it is interesting to note the necessity to do this.
During the planning phase, we had recommended that the table data and the index data be placed on two different LUNs. This would give the best IO performance. But unfortunately the vendor made a slight error in giving us the DDL for the tables and indexes.
The CREATE table command also added the primary key constraints and specified the filegroup reserved for indexes for the primary key. I guess they thought since our recommendation was to have all indexes on a separate filegroup, they would create the primary key index also on the index filegroup.
But as most of the DBAs know the primary key index is nothing but the table itself. So when the primary key was created on the index filegroup it moved the data from the data filegroup to the index filegroup. So essentially we ended with both the data and the index on the same filegroup. The data filegroup was unused after this change.
But unfortunately the non clustered indexes had been created and the data was inserted.
Now if I tried to drop and recreate the primary key index then the non-clustered indexes would be rebuilt twice. So I had drop all indexes and then recreate them and this way I could avoid rebuilding the non clustered indexes twice.
Following is the summary of commands that I ran to achieve this.
1) Script out the CREATE statements for the table including the CREATE statements for the indexes. (Always have a backup plan, if it helps take a backup of the database.
2) Drop all the non clustered indexes.
DROP INDEX [AK_Department_Name] ON [HumanResources].[Department]
WITH ( ONLINE = OFF )
3) Drop the primary key index.
ALTER TABLE [HumanResources].[Department] DROP CONSTRAINT [PK_Department_DepartmentID]
4) Create the new primary key index
ALTER TABLE [HumanResources].[Department] ADD CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED
(
[DepartmentID] 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) ON [PRIMARY]
Do not forget to mention the new filegroup for the primary key index.
5) Create all the remaining non clustered indexes and specify the index filegroup.
CREATE NONCLUSTERED INDEX [AK_Department_Name] ON [HumanResources].[Department]
(
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [INDEXES]
Actually it is not the Primary Key specification that holds the table as the index data. It is specifying CLUSTERED that does so. It is possible to create a nonclustered PK and a clustered index on a different column (such as a sequential identity) for performance and space management.
ReplyDeleteYou are correct, it is indeed the clustered index and not the primary key. But in most cases they are same but to move the table you need to move the clustered index.
Delete