Which Indexes will be used? - SQL Server

During the performance issues of database servers and database itself, one of my activities to improve performance of the Indexes. I have created some of the clustered indexes on tables which have only non-clustered indexes and also on some heap tables. Also, I have created some additional non-clustered indexes called as Covering indexes and some of having composite indexes.

After creating or having so many indexes on tables, it is real time to check the usage of them as which indexes are used by execution plans or query plans among them. We should review the index statistics and usage report of all the indexes of tables as how they are seeking, scanned and update. Let us pick one example and will create one table and some of the indexes as well.

USE DEMO
GO

-- Creating table which will be used for demo

IF (OBJECT_ID('TblIndexUsed','U') > 0)
DROP TABLE TblIndexUsed

CREATE TABLE TblIndexUsed
(
Id bigint identity(1,1),
ObjectId bigint,
ObjectName varchar(100),
CreateDate datetime,
ObjectType varchar(100)
)

GO

-- Inserting some sample records in tables 

INSERT INTO TblIndexUsed
SELECT
object_id,
name,
create_date,
type_Desc
FROM SYS.OBJECTS

GO 10000

Now We will create one clustered index, Normal non-clustered indexes, We are not creating additional non-clustered index here.

-- Creating clustered index on ObjectId column.
CREATE CLUSTERED INDEX IX_UObjectIdDate ON TblIndexUsed (Id,CreateDate)
GO

-- Creating normal nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_UId on TblIndexUsed (Id)

-- Creating normal nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_UCreateDate on TblIndexUsed (CreateDate)
GO

Now we are running the below query to check the usage of the above created index on the table.

SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE  ID >=30000  and CreateDate >= GETDATE() - 50 


SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE CreateDate >= GETDATE() - 50 and  ID >=30000  


SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE ID >= 30000 


SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE CreateDate >= GETDATE() - 50 

(Click on image to enlarge)

Once again we will create a more indexes, but they are additional covering indexes.

-- Creating covering nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_UId_Covering ON TblIndexUsed (Id) 

INCLUDE (ObjectId,ObjectName,CreateDate,ObjectType)
GO

-- Creating covering nonclustered index on CreateDate column.
CREATE NONCLUSTERED INDEX IX_UCreateDate_Covering ON TblIndexUsed (CreateDate) INCLUDE (Id,ObjectId,ObjectName,ObjectType)
GO

After creating covering indexes we are reviewing execution plans again for the same above queries.

SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE  ID >=30000  and CreateDate >= GETDATE() - 50 


SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE CreateDate >= GETDATE() - 50 and  ID >=30000  


SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE ID >= 30000 


SELECT 
ID,
ObjectId,
ObjectName,
CreateDate,
ObjectType
FROM TblIndexUsed 
WHERE CreateDate >= GETDATE() - 50 

(Click on image to enlarge) 

You can see here the difference between both of the execution plans and impact of indexes on queries and the index used for the same.