How does the database decide which Index to use

Question

DDL

create table t
(
    id int,
    id1 int
)

create index Example_Index
  On t(id,id1)

create index Example1_Index
  On t(id1,id)

---

DML

insert into t(id, id1)values(1, 100)
insert into t(id, id1)values(1, 101)
insert into t(id, id1)values(2, 103)
insert into t(id, id1)values(1, 104)
insert into t(id, id1)values(3, 105)
insert into t(id, id1)values(1, 106)
insert into t(id, id1)values(2, 107)
insert into t(id, id1)values(3, 108)

---

Sql Query - select * from t Where id = 107 -- uses Example_Index

Sql Query - select * from t Where id1 = 107 -- uses Example1_Index

Sql Query - select * from t -- uses Example1_Index

Confusion - How does the system decides to use Example1_Index

Below are the details...

Answer 1

The index created latest is used or index with higher index id is being used.This is shown in below query but this is just the observation based on below query and might not be consistent behaviour as i couldnt find any documentaion from MS related to this. Run below query and then run the same by swapping the two index creation statements.

But as long as optimizer picks the best index it shouldnt bother you.In this case both indexes are same.

drop table t
go
create table t 
( 
    id int, 
    id1 int 
) 


create index Example_Index 
  On t(id,id1) 

 create index Example1_Index 
  On t(id1,id) 
 go
 insert into t(id, id1)values(1, 100) 
insert into t(id, id1)values(1, 101) 
insert into t(id, id1)values(2, 103) 
insert into t(id, id1)values(1, 104) 
insert into t(id, id1)values(3, 105) 
insert into t(id, id1)values(1, 106) 
insert into t(id, id1)values(2, 107) 
insert into t(id, id1)values(3, 108) 
go
select * from sys.dm_db_index_physical_stats(9,object_id('t'),null,null,'DETAILED')
go
dbcc freeproccache
go
select * from t Where id = 107 -- uses Example_Index

select * from t Where id1 = 107 -- uses Example1_Index

select * from t -- uses Example1_Index
go
dbcc freeproccache
go
select * from t Where id = 107 -- uses Example_Index

select * from t -- uses still Example1_Index even though that index is not used at all.However, this index id is 3 higher than example_index.
go
--now try the same thing but change the order of the index and you will see example_index is used.

Answer 2

Index creation is the crucial thing in DB. In large database it shows a major role during the search of particular record set.

So first of all when you search the record so on the basis of search criteria the DB engine try to search the record through index. If it find the right index the query is said to be optimized and if it doesn't then engine mark the this index as a missing index and finally provide the result set very slow.

For finding the missing index you can run :

Select * from dm_db_missing_index_details

Here you will get equality_Columns, Non_equalityColumns, and included columns. so on the basis of these details you can prepare the right index for your search criteria.

For Ex:

CREATE NONCLUSTERED INDEX IX_MyIndex ON
dbo.Mytable(ListOfEqualityColumns_separatedByCommas,ListOf_NONE_qualityColumns_separatedByCommas)
INCLUDE (ListOf_Included_Columns_separatedByCommas); 
GO

Answer 3

There is no certainty that which index executes.

Answer 4

First two queries are self-explanatory - they're performing a seek on the leading column in the chosen index.

Third one is a coin-flip since, without a clustered index, both indexes have the same cost.

You might observe that the most recently-used index (from query 2) will be used for query 3 - but you shouldn't rely on it.