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.

Clustered Index in SQL Server

As per the promise I have made in “Some basics about Index in SQL Server” article, I am coming up with each Indexes of SQL Server and first and most important is “Clustered Index”. 
Understanding of Clustered Index:
As I told in previous article too that Index is the key of performance, good and managed Index could boost up your speed of retrieving of data from table.
Clustered Index contains actual data of the table in leaf level pages in logically sorted order (to understand root and leaf level page, click here). Since it is logically sorted, it doesn’t need to go for all data present there in Index. 
For example if you are looking for the phone number of “Ritesh Shah” in telephone directory, you can move to a page which has phone number of the person whose first name starts with “RI” and once all instance of “RI” over in directory, you don’t need to move on as there is NO CHANCE, you will get “Ritesh” anywhere in directory except those particular pages.
BTW, if you don’t have clustered index on your table, than your table would be called “HEAP”, which wouldn’t not have logically sorted data so if you are searching for “Ritesh Shah”, you can to check complete table as you never know, where you will find “Ritesh Shah”, just one method, go each and every row of table and check for matching criteria.
Like any other columns, you can define clustered index in more than one field too and all the columns covered up under the index, called key column.
While choosing a prime candidate for Clustered Index column in your table, you have to select the columns which meet few of the general criteria defined below. (you can say following criteria as a best practice while choosing index candidate)
–> You key column or combination of key columns should be unique and not null. If your You key column or combination of key columns are not unique than SQL Server has to add one more hidden column of 4-byte INT to make it unique. However, you can’t see that hidden column neither can query it directly; it would be purely for SQL Server’s internal use.
–> It should be short as wide key value would increase the depth of Clustered Index and will reduce the performance a bit and also increase the size of non-clustered index as it is being there as a reference in all non-clustered index.
–> Select less changing or no changing fields for you clustered index as Key value indicates the location of page where actual data resides, if you change this key value, row has to be deleted from that page and has to move to another appropriate page which reduces the performance and increase unnecessary overhead to IO.
Generally whenever you make Primary Key in any of your table, SQL Server itself create clustered index on it but if you want to keep clustered index on any other column(s) due to high selectivity on those column(s), you can do it.
Have you got bored of so long theory? Let us do some practical and check it out?
–create one database which you can delete after running this example
create database SQLHub
GO
USE SQLHub
GO
–if orders table is already there. you can delete it than create new one with name “Orders”
IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
      DROP TABLEorders
END
GO
–creating table
CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–inserting 100000 fack rows into table (BTW, thank to Jacob Sebastian, copy in INSERT script from one of his demo to generate big 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, however it is not meeting the criteria I have
–given in article, but this is ok for example
CREATE CLUSTERED INDEXidx_refno ON orders(refno)
GO
–run both of the following query with execution plan and see the results in execution plan
–you can see execution plan with the following steps
–first select both of the below given query
–Press Ctrl+M
–press F5
SELECT TOP 10 * from orders whererefno=4
SELECT TOP 10 * from orders whereOrderDate = ‘2010-02-01 00:04:00.000’
GO
–if you wish, you can uncomment below code and delete SQLHub database
–use master
–go
–drop database sqlhub
If you execute both the query with “Actual Execution Plan”, you will see first query is having Index Seek and second query is having Index Scan. 
Seek and Scan is really interesting topic which I will cover later but just keep in mind that, Seek is good, Scan is bad as it will check all records of the index.
if you want to refer all other articles related to index, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Clustered Index in SQL Server

When I was novice with SQL Server and mainly working with .NET applications, Index seemed to me as alien of the planet of MARS. I was always confused about Index concept and always making mistake in choosing clustered and non-clustered index for particular field. When SQL Server and database technologies attract me, I had started to understanding Index concept in details few year back and this article is coming from my own experience and learning.

Though Clustered Index is not a concept of Relational Database, it is very useful for performance improvement in highly volume transaction processing system.
Basically Clustered Index is a kind of data structure that provide fast data access. There are two types of index available in SQL Server 1.)  Clustered Index 2.) Non-Clustered Index. Today we are going to talk about 1st Option which is Clustered Index.

You can have maximum one clustered index per page because clustered Index stores sorted physical data in leaf level and this is obvious that you can sort data in one direction only so that you can have maximum one Clustered Index in database table. However, you can define clustered index on one or more than one field but try to keep as less columns as possible in clustered index.

When you create Primary Key, unique index is automatically getting created on column(s) which is Clustered Index by default; however, you can make it non-clustered while generating Primary Key.

While choosing the field for Clustered Index, you should consider few things in your mind. Column shouldn’t be low-cardinality. Low-cardinality means, it is the column which contain very less distinct value. For example, if you consider Grade filed (I guess there should be four grade A, B, C, D), you maximum having 4 kind of value in column.

Column should be highly selective in your WHERE clause, GROUP BY clause, JOIN and ORDER BY clause so that you can get maximum benefit out of it.

If you would like to read some more articles about Index in this blog, go through below given link

can’t create Index on view

Calculate the size of clustered and non-clustered index

DBCC SHOWCONTIG and DBCC INDEXDEFRAG

sys.dm_db_index_usage_stats and sys.dm_db_index_operational_sats

For getting the details about structure of Clustered Index, please Click Here
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah