During the performance issues of database servers and database itself, one of my activity to improve performance of the Indexes. I have created some of the clustered indexes on tables which have only nonclustered indexes and also on some heap tables. Also i have created some additional nonclustered 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 indexes statistics and usage report of all the indexes of tables as how they are seek, scanned and update. Let us pick one example and will create one tables 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 are creating one clustered index, Normal nonclustered indexes, We are not creating additional nonclustered index not.

-- 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 above created index on 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 are creating 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 indexes usage for the same.