Designing good set of Index

Introduction

We all know about the different type of index but the main question is who we decide which columns need the indexing and in which condition is not an easy task. Here in this article I am trying to understand it. For this I am taking some reference from MSDN. Hope it will be helpful

Disadvantage of Indexes

We must remember that, Index improves the Performance of SELECT statement only and decrease the Performance of INSERT/UPDATE/DELETE and MERGE statement because all indexes must adjusted appropriately when the data changes in the table. So, using too many index in the table is not good.

Guideline to Design Index

1.    We must try to create index on a few columns within a table not all the columns.

2.    Index in a small table that have few records is not wise idea as the Query optimizer longer to traverse the index searching for data than to perform a simple table scan.

3.    Indexing in a View (Materialized View) significantly improve the performance when the view contains Aggregate function or Join multiple table or in both the cases. In this situation the Query optimizer not use the internal query of the Views, the use the View as whole. For designing Indexed Views please look at the MSDN reference

https://technet.microsoft.com/en-us/library/ms187864(v=sql.105).aspx

4.    Create nonclustered indexes on all columns that are frequently used in predicates and join conditions in queries.
5.    Covering Index is an important factor starts from Microsoft SQL Server 2008. Covering indexes can improve query performance because all the data needed to meet the requirements of the query exists within the index itself. That is, only the index pages, and not the data pages of the table or clustered index, are required to retrieve the requested data; therefore, reducing overall disk I/O. 
But we must consider the volume of data in the table as the cover index also increases the volume of index table. If the volume is to heavy, we must follow the olfaction style with composite index. 

6.    If we are going to Update a single rows, we must use a single query not use multiple query to update the same rows.

7.    We must evolutes the columns used within the query very carefully

Exact Match to Specific Values

SELECT BusinessEntityID, JobTitle

FROM HumanResources.Employee

WHERE BusinessEntityID = 228;

            Nonclustered or clustered index on the BusinessEntityIDcolumn.

            

Exact match to a value in an IN (x,y,z) list

          SELECT BusinessEntityID, JobTitle

FROM HumanResources.Employee

WHERE BusinessEntityID IN (288,30,15);

            Nonclustered or clustered index on the BusinessEntityIDcolumn.

           
Range of values

           SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID BETWEEN 1 and 5;

            OR

            WHERE ProductModelID >= 1 AND ProductModelID <= 5;

            Clustered or nonclustered index on the ProductModelIDcolumn.

        
Join between tables

           SELECT a.ProductAssemblyID, b.Name, a.PerAssemblyQty

FROM   Production.BillOfMaterials AS a

             JOIN Production.Product AS b

           ON a.ProductAssemblyID = b.ProductID

      WHERE b.ProductID = 900;

            Nonclustered or clustered index on
            the ProductID andProductAssemblyID columns.

        
LIKE comparison

          SELECT CountryRegionCode, Name

FROM Person.CountryRegion

WHERE Name LIKE N'D%'

            Nonclustered or clustered index on the Name column.

            

Sorted or aggregated

           SELECT a.WorkOrderID, b.ProductID, a.OrderQty, a.DueDate

FROM Production.WorkOrder AS a

JOIN Production.WorkOrderRouting AS b

ON a.WorkOrderID = b.WorkOrderID

ORDER BY a.WorkOrderID;

            Nonclustered or clustered index on the sorted or aggregated column.

For sort columns, consider specifying the ASC or DESC order of the column.

            

PRIMARY KEY or UNIQUE constraint

            INSERT INTO Production.UnitMeasure

(UnitMeasureCode, Name, ModifiedDate)

VALUES ('OZ1', 'OuncesTest', GetDate());

Clustered or nonclustered index on the column or columns defined in the constraint.

            

UPDATE or DELETE operation in a PRIMARY KEY/FOREIGN KEY relationship

            Nonclustered or clustered index on the foreign key column.

            

Column is in the select list but not in the predicate.

           SELECT Title, Revision, FileName

FROM Production.Document

WHERE Title LIKE N'%Maintenance%' AND Revision >= 0;

            Nonclustered index withFileName specified in the INCLUDE clause.

8.    Keep the length of the index key short for clustered indexes. Additionally, clustered indexes benefit from being created on unique or nonnull columns.

9.    Columns that are of the ntext, text, image, varchar(max), nvarchar(max), and varbinary(max) data types cannot be specified as index key columns. However,varchar(max), nvarchar(max), varbinary(max), and xml data types can participate in a nonclustered index as nonkey index columns.

10.  In case of XML columns we can used the XML index.

11.  Examine column uniqueness. A unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that makes the index more useful.

12.  Consider a Filtered index in a column with frequently NULL values and Distinct range of values increase the performance by Filtered index.

13.  Consider the order of the columns if the index will contain multiple columns. The column that is used in the WHERE clause in an equal to (=), greater than (>), less than (<), or BETWEEN search condition, or participates in a join, should be placed first. Additional columns should be ordered based on their level of distinctness, that is, from the most distinct to the least distinct.

           

Hope you like it.

Posted by: MR. JOYDEEP DAS