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