SQL Server Index Secrets Revealed

I just got out of a meeting room full of developers, managers, business analysts, and stakeholders regarding the upcoming projects. I represented the DBA team to answer any questions they had on database. The meeting went well. One of the  developers asked me to add Index to his production tables because his application was running slow. Most developers and IT folks have misconception regarding Index because they think adding index will speed up the query like magic. This is not always the case and I will discuss more about Index in next few paragraphs.

Sometimes, full table scan is much faster than the Index seek and the database engine knows it better. There are so many variables like data size, actual data, query, table structure, constraints, database server configurations, network, table stats, and so on, that impact the query performance. Well designed Index can reduce disk I/O and utilize fewer system resources improving query performance but at the same time adds some overhead  to database during Inserts. Deletes and Updates operations benefit  greatly with Index usage as well.

What is an Index? 
Index is a database object which contains an entry of each record that appears in the indexed column to provide faster access of data.  Indexes are used in a View or a Table. Knowing the concept of  Index in detail  is very crucial to database administrators, database developers, and anyone who likes to work with database. The proper use of Index makes your query and database perform faster. But it might hurt your query performance and add overhead to database if not used properly. With this article, you will walk away with learning the characteristics, types, maintenance, restrictions, proper index use, and the industries’ best practices on SQL Server Index.

When TO use Index?

1. Frequently searched column
2. If you are selecting 10% or less data
3. If recommended by execution plan or SQL tuning advisor
4. Query with Join and Where Clause

When NOT to use Index?

1. Frequently updated or deleted column
2. When selecting more than 10% of data
3. When the database engine ignores the index use

Note: It is hard to list everything here. The do’s and don'ts come with practice and experience.

Clustered vs Non Clustered Index

Clustered: An index that sort and store the data row in the table or view on their key values.You can have one clustered index per table as the data rows sort in only one order. A table with clustered index sort the data based on the column where there is clustered index. The table with clustered indexed is also called clustered table. A table without a clustered index is a heap table because  it is not sorted.  A clustered index is created while creating a primary key or can be created later after the table creation.

Non Clustered: A regular Index which most database system comes with. The non clustered Index contains key values where each key value entry has a pointer to the data work that contains the key value. You can have multiple non clustered Index as you are not sorting data on that record like in clustered Index.

Both clustered and non-clustered can be composite or non composite Index.

Picking the right Index type 
Picking the right index is necessary to get optimum performance of a query. If your column is unique and there is a pattern of increasing or decreasing of data, then clustered index is a way to go. For everything else you may use non clustered only if you truly need it. Lets consider you have an employee table that stores employee number along with their details. If you frequently search for employee detail with his employee number, a clustered index on employee number is the way to go because it is unique and has a pattern. This should be done while the code in stage. You can add the index later but it might be hard or you may need to take the table offline which may require more work than you think.

Since I come from Oracle background, I would like to compare everything with Oracle and if you are coming from different backgrounds, then here is a bonus for you. Oracle does not  have clustered Index, it  has local and global index which are SQL non clustered equivalent.

Check if your query is using Index?
How good is it if your query isn't using index? Adding an Index doesn't mean that your query is using it. You will need to ensure that your query is using it. Run a SQL Execution Plan on a query and check for Index Scan or Index Seek. If you see Table Scan instead, it means the Index is being ignored by database engine or your query is not written properly to use index.

Index Maintenance
Like tables and other db objects, Index needs proper maintenance to get optimum performance. Database administrator should periodically check the index fragmentation, size and usage, and take appropriate action based on the findings.

1. Index Size: The size of index should be smaller than the table itself. If it is greater, your query is performing worst. You are better off without index. Rebuilding index should fix it and if it does not, you don't need index at all.
2. Index Reorganize:  Frequent update or deletion in table cause the fragmentation. If fragmentation is below 40%, you are required to run index reorganize. Index nodes are reorganized without stats update. 
3. Index Rebuild: If the fragmentation is more than 40%, you will begin to notice slowness. You are required to perform index rebuild at this point. During this process, an index is dropped and recreated along with stats update.
4. Usage: Check to see how often indexes are used.  Drop the ones that are not in use. There are queries you can run to check the index usage.

A query that is utilizing index now may not  be using Index in few months if data or query changes. In situations like this, some DBA use hint/force commands to use index. This is not the smartest way and not recommended. If your database engine is ignoring an index use, there is a reason for that and you are required to research and find out why. The full table scan is cost effective during these situations and the database engine knows better than us. If you still want to utilize the Index, you might consider database re-factoring.

Working in a fast paced environment makes you automate everything. I have a job scheduled to run a query to find the index issue and I take appropriate action whenever there are red flags. Never ever automate a job to rebuild or fix index issues unless you know what you are doing. There are few other types of Index in SQL Server beside clustered and non-clustered Index.

Now, you know your Index well. Isn't this more complicated than the one liner definition that everyone knows! Happy Indexing!!