Wednesday, 20 June 2012

The general guidelines for Index

We all know that the INDEX is a key factor of performance tuning. As proper index can increase the performance and bad implementation of index implementation can dramatically decreases the performance.

 In this article I am trying to gather some facts from different sources, which can provide us a general guide line to choose appropriate columns and index for performance boost up.

The general tendency that I found for implementation of Index is mentioned bellow.

The table objects contain one clustered index and maximum number of non clustered index.
Generally we builds the no-clustered index in each and every columns that is used in WHERE clause.
Is it the correct strategy to implement the index?
What happened, when INSERT, UPDATE, DELETE occurs in the table that have high number of index are there?

So, with proper knowledge of index we must follow some general guideline to implement the index in table objects.

The general guidelines for Index
Keep indexes lean. Try to build indexes on one or few columns at most. Wide indexes take longer to scan than narrow indexes.

Create the clustered index on every table. Try to create the clustered index on the column which is used most frequently for retrieving data and the column that does not have many duplicate values.

Create the clustered index on the column that will never be updated. Every time the clustered index key is updated, SQL Server must maintain not just the clustered index but also the non-clustered indexes since non-clustered indexes contain a pointer to the clustered index.

 By default, SQL Server creates the clustered index on the PRIMARY KEY. It is beneficial to have the clustered index on the primary key. But sometimes it is better to make other columns as clustered index. We need a judgment over here that we make the clustered index on primary key or choosing other columns for it.

SQL Server has to perform some maintenance when index keys are updated, so each additional index could impose a slight performance penalty on the performance of INSERT, UPDATE and DELETE statements. Nevertheless the cost of index maintenance could be far less than the performance improvement you'll reap for your SELECT statements. Therefore you can be fairly liberal with the number of non-clustered indexes.

Don't create multiple indexes on the same set of columns. Such indexes provide no additional benefit but use up extra disk space and might hinder performance of INSERT, UPDATE and DELETE statements.

Check the default fill factor level configured at the server level. If you don't specify the FILLFACTOR option with CREATE INDEX statement, every new index will be created using the default fill factor.

Use the built-in tools for analyzing and recommending indexes but do not fully depends on them.

Always look at the query plan to finding the reason of poor performance query.

Hope you like it.

Posted by: MR. JOYDEEP DAS