SQL Server Index Design Recommendations

The estimated reading time for this post is 5 minutes

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.

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

 

 

Author: Hamid J. Fard

 

 

 

 

—

 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz