Query optimization or Indexing sound difficult and scarcely if you have not worked on it. However, basics are pretty easy with the help of Execution Plan! I’ll show you the example using Microsoft’s sample database AdventureWorks.
Estimated Execution Plan is found here (or Ctrl + L). When you write a query and click “Display Estimated Execution Plan”, you can see how your query is executed.
In the query below, you see “Cluster Index Scan”. It means DB engine scanned the whole table using Cluster Index. Although an index is used, Scan is generally bad since DB engine needs to check each record to find what you are querying.
(In this case, cluster index is by ProductID. This means records in Product table are physically ordered by ProductID.)
If you hover over “Cluster Index Scan (Clustered), you see DB engine needed to read 40,504 records in order to return 1,000 records.
If you put “WHERE ProductID < 1000”, you see DB engine now uses “Clustered Index Seek”. Seek is great because it means DB engine knows where to find records without checking each record.
Now, DB engine only read 328 records since there were only 328 records whose ProductID is less than 1000. No need to read 40,504 records. This is pretty efficient! “Estimated I/O Cost” is only 0.008, comparing to Index Scan’s 0.65.
Therefore, when you query has “Table Scan” or “Index Scan”, there is a good chance you can improve your query by creating an Index and use “Index Seek”.
Now, let’s say we need the new query, and current execution plan shows Cluster Index Scan is used.
Since the where clause filters “ListPrice” and “DaysToManufacture”, we can create a index like this.
CREATE NONCLUSTERED INDEX IX_ListPrice_DaysToManufacture
ON Production.Product (ListPrice, DaysToManufacture)
Indexes are created and we can see what columns are used for each index in Index Properties.
Now, the execution plan shows Index Seek is used and we successfully created the index for our new query to improve the query performance!
This covers the only basic use of Index. It is important to remember that when there are too many indexes, it will make Insert, Update, and Delete operation slow. Creating Indexes is not always good and there is a trade off.
