Using Proper indexes to Optimize SQL Performance is very important when it comes to development of a Application/Reports.
Indexes can improve performance of a query to a very high level by using what is known as “Index Seek” instead of a ” Table Scan”. In Index seek the optimizer directly says that there is a usefull index on this table which can be beneficial in retrieving data, so instead of going through all the table to find the data using Table Scan, the query directly uses the index for retrieving data using a index.
But, on a downside Indexes effect the performance of table where a lot inserts,Updates, deletes happen because now SQL Server has to do these inserts,Updates, deletes in multiple locations like table itself plus indexes built on the effective columns. One more thing to keep in mind is non-clustered indexes can make the database size bigger since it creates another object which stores the values, On contrary Clustered indexes won’t effect database size since the data itself is stored as per the Clustered Index and thus only one clustered index is allowed per table.
Firstly, we need to what is Heap?
Heaps are tables that have no clustered index.
The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. The data pages are not linked in a linked list.
Now, its time to know what are Clustered tables? Clustered tables are tables that have a clustered index.
The data rows are stored in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values. The pages in each level of the index, including the data pages in the leaf level, are linked in a doubly-linked list. However, navigation from one level to another is performed by using key values.
Indexed views have the same storage structure as clustered tables.
Next, lets go to what is a Nonclustered index, they have the same B-tree structure as clustered indexes, except for the following significant differences:
1.The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.
2.The leaf layer of a nonclustered index is made up of index pages instead of data pages.
Nonclustered indexes can be defined on a table or view with a clustered index or a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value.
The row locators in nonclustered index rows are either a pointer to a row or are a clustered index key for a row, as described in the following:
If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).
If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row. If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier. This four-byte value is not visible to users. It is only added when required to make the clustered key unique for use in nonclustered indexes. SQL Server retrieves the data row by searching the clustered index using the clustered index key stored in the leaf row of the nonclustered index.
1. If table is static and is queried heavily (Ex:
Department table in ERP/HR application) then indexes on the appropriate fields is highly beneficial.
2. If table is highly dynamic and not a lot of querying is done on a daily basis (Ex:
Sales tables used for tracking Sales) then indexes will slow down the writes.
To check space used by tables with indexes we can use the following statement
sp_msforeachtable @command1=”exec sp_spaceused ‘?'”
The above query lists all tables in database with thier relevant storage including indexes.
If above two cases are the boundary cases, then to build indexes or not to build indexes on a table depends on which case above does the table in contention comes closest to.
If not leave it to the judgement of Database Tuning Advisor(DTA).
We can even use the missing indexes DMV to find out which indexes can give a performance gain. To find out more about missing indexes Go to
Bart Duncan’s SQL Weblog