SQL Server Index Design Recommendations

Applicable: SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014
In this blog post, I would like to explain overview of SQL Server indexes and most common SQL Server index design recommendations.

What is an Index?

Index is an object in SQL Server Database that affects physical and logical I/O operations, therefore the query performance effected. There are two types of index, Clustered and Non-Clustered indexes. Clustered index changes the table structure from Heap to B-Tree and Non-Clustered index does not change the table structure at all and duplicate the selected index key data. Take note that index has several levels, such as root level, intermediate level and leaf level.

—The Benefit of Index

Clustered index improves modification performance by changing the table structure to B-Tree. Once clustered index is created, all the data are physically sorted and stored into the database file. Non-Clustered index improves the select query performance and reduce deadlock and blocking occurrence by duplicating data into different data pages. To improve performance of index creation, it is better to create clustered index before any non-clustered index on a table. take note that non-clustered index can be stored on different I/O path to provide better I/O performance.

Consider single column table with 27 rows:-

single 1

The Index Overhead

Database table with one or more non-clustered index needs more storage and memory, due to index existence Data Manipulation Queries (CRUD) take longer because of index structure. More processing time is required to maintain the index of constantly changing table, therefore clustered index has greater overhead than non-clustered index. SQL Server supplies some DMVs for monitoring index performance and detail information.

  •   sys.dm_db_index_operational_stats (Low-Level Information)
  •   sys.dm_db_index_usage_stats (Returns Statistics)

Index Design Recommendation

During index creation, examine the Where and Join criteria clause in every single query to create an index to cover most queries. It is highly recommended to use narrow indexes by examining the index key data type. Query performance is highly depends on index key column uniqueness to provide fastest search within the index. Try to pre-order the index key column to make data physically sorted.

Avoid wide data types such as Char, Varchar, NChar, NVarchar. Narrow index can accumulate more rows in 8KB index pages, therefore the storage space for database reduces which cause less I/O operations. The last recommendation is to consider the type of index carefully due to SQL Server behavior towards clustered and non-clustered indexes.

Clustered Index Design Recommendation

Try to create clustered index first in a table to avoid double index creations, as mentioned before keep the index key narrow to improve the search performance. Do not create clustered index on frequently updatable column and always create clustered index with preorder sort.

To maintain the clustered index, rebuild it in a single transaction and prevent too many concurrent inserts in sequential order to prevent deadlock and blocking.

Non-Clustered Index Design Recommendation

Non-Clustered index key can be wide and can be placed on frequently updatable columns due to this index structure. This index can be filtered to improve the select query performance and index keys can be used in Join or Where criteria clause. Non-Clustered index can be placed on low selectivity column as well. This index can store other columns as ‘Include Keys’ to reduce the I/O operations and mitigate the needs of base table during query execution.

Index Compression

Introduced in SQL Server 2008 and this feature is available only in Enterprise and Developer Editions. This can lead to serious Performance Improvement by reducing the row size and I/O operations, there will be an overhead of CPU and Memory due to compression and decompression operations, in most cases the overhead is minimal compare to the benefits. Take note that Non-Leaf pages in an index receive no compression under the page type.

Index on Partitioned Table

On partitioned table, different index will be created on every partition. Each index can be rebuilt or reorganized separately. Partitioning boosts up the CRUD operations performance by separating data physically within few filegroups and files.

[codesyntax lang=”tsql”]

/*
	Copyright (C) 2015 Fard Solutions Sdn Bhd, All rights reserved.
	Author: Hamid J. Fard
	Date: 2015 November 10.
	
	http://WWW.FARD-SOLUTIONS.COM
	
*/

Use master;
go

Create Database IndexedPartition
On Primary (Name='IndexedPartition',Filename='F:IndexedPartition.mdf'),
Filegroup FG1(Name='IP_F1',filename='F:IP_F1.ndf'),
Filegroup FG2(Name='IP_F2',Filename='F:IP_F2.ndf'),
Filegroup FG3(Name='IP_F3',Filename='F:IP_F3.ndf')
Log On (Name='IndexedPartition_Log',Filename='F:IndexedPartition_Log.ldf');

go

Use IndexedPartition;
go

Create Partition Function PF_IDRange(Bigint)
As Range Right For Values(1000,2000);

go

Create Partition Scheme PS_IDRange As Partition PF_IDRange
To (FG1,FG2,FG3);

go

Create Table Customers(	ID bigint not null Identity(1,1) Unique,
						Firstname varchar(20) not null Default 'Hamid',
						Lastname varchar(20) not null Default 'J. Fard')
On PS_IDRange(ID);

go

Select * from sys.partitions P
where (P.object_id = object_id(N'Customers'));

go

Insert Into Customers Default Values 
Go 10000

go

--Include Actual Execution Plan
Select $partition.PF_IDRange(ID) as Partition_Number, * From Customers;

go

--Include Actual Execution Plan
Select $partition.PF_IDRange(ID) as Partition_Number, * From Customers
where ($partition.PF_IDRange(ID) = 2);

go

Create Clustered Index CIX_ID on Customers(ID asc);

go

Select * from sys.partitions P
where (P.object_id = object_id(N'Customers'));

go

Select * from sys.dm_db_index_physical_stats(db_id(),Object_ID(N'Customers'),1,Null,'Detailed');

go

Update Customers Set Lastname = 'AKAKAKAKAKAKAKAK'

go

Select * from sys.dm_db_index_physical_stats(db_id(),Object_ID(N'Customers'),1,Null,'Detailed');

go

Alter Index CIX_ID on Customers
Rebuild Partition = 1;

go

Select * from sys.dm_db_index_physical_stats(db_id(),Object_ID(N'Customers'),1,Null,'Detailed');

go

Alter Index CIX_ID on Customers
Rebuild Partition = 2 with (Sort_In_Tempdb = on,Data_Compression = Page) ;

go

Select * from sys.dm_db_index_physical_stats(db_id(),Object_ID(N'Customers'),1,Null,'Detailed');

go

[/codesyntax]

 

 

Author: Hamid J. Fard

 

 

 

 

 

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan