Index is one of most widely discussed topic among developers and DBAs. During query optimization Index is one of the important aspects. I came across multiple instances where Developer/ DBA creates Index but they are not sure whether that index is used by the optimizer or not. Also I received emails from developer asking How to design an Index? So Here I’ll try to explain the fundamental of Index Selection.
While designing an Index the basic idea is that the optimizer should use the index so that it will fetch the data using an optimal path with fewer disks I/O operation and less system resource usage.
Below are the important parameters for Optimizer to consider before it selects an Index to use
- Predicate from the WHERE clause
- Join Condition
- Other limiting option used in a query (Ex: Group by, Distinct etc.)
Using Index, optimizer can perform either Seek Operation or Scan Operation.
Seek Operation is used when fetching a single value or a range of value and in Scan operation it traverses through the Index pages either forward or backward.
All predicate used in where clause are not always used for Index selection. Predicate which can be converted into an Index operation are often called SARGgable or Search-ARGument-able and predicate that do not match the selected index are called non-sargable predicates which are used as filter predicate. SQL SERVER usually evaluated non-sargable predicates with in the seek/scan operator in the query tree.
Once you create an Index always checks with your query to determine whether it improves your query performance. If it is not improving your query performance, then remove that index.
To understand SARGgable and Non SARGgable predicate, let me demonstrate it using a simple demo.
For this Demo I’m using AdventureWorks database which can be downloadable from Here
Step 1: create table Product_1 using Product table.
SELECT * INTO Product_1 FROM [Production].[Product]
Step 2: Create an UNIQUE Clustered Index on Product_1 for Column ProductID
CREATE UNIQUE CLUSTERED INDEX CIX_Product ON Product_1 (ProductID)
Step 3: Run the below query and check its actual execution plan. You can enable actual execution plan either by shortcut Ctrl+M or from the Query menu in SSMS.
SELECT * FROM Product_1 WHERE ProductID <100 and Makeflag = 1
We can see below Optimizer uses the Index Seek operation to fetch the data. If you mouse over on Clustered Index Seek operator you will get the operator details. Here we can see that ProductID is used for Seek predicate as we have Index on that and column MakeFlag is used for filter predicate.
