How SQL Server uses Indexes?
a. SQL Server can use indexes to perform seek and scan operations.
b. Indexes can be used to speed up the execution plan of a query by quickly finding records without performing Table Scan.
c. Part of query optimizer job is to determine if an index can be used to evaluate a predicate in a query.
d. Query Optimizer needs to determine if the index is covers the query that is if the Index contains all the columns requires by the query
e. SQL Server can also consider using more than one index and joining them to cover all the columns required by the query.
What are the point one should consider while checking the negative impacts of an Index?
a. Sometimes, SQL Server may also consider to join multiple Indexes and join them to cover all columns required by the query (Index Intersection). If it is not possible to cover all columns required by the query, it may need to access the base table, which could be Cluster Index or Heap, to obtain the remaining columns. This is called a Key Lookup operation. (It may be Key Lookup/ RID Lookup). However a Bookmark Lookup Requires a random I/O, which is a very expensive operation, its usages can be effective only for a relatively small number of records.
b. Although one or more Indexes can be used, it does not mean that they will finally be selected in an execution plan as this is a cost based decision.
c. While creating an Index make sure that you have verified that the Index is used in a plan and the query is performing better that is the primary reason to define the Index.
d. An Index that is not being used in any of the query will take the Valuable Disk Space and may be negatively impact the performance of Update Operations without providing and benefits.
e. It is possible that an Index, which was useful when it was originally created is no longer used by any query. This could be as a result of change in the database, the data or even the query itself. One can identify such indexes using DMVs (sys.dm_db_index_usages_stats).
