Creating indexes on AutoId and time column My table structure is as follows

Events(
  AutoId int identity(1,1) //Primay Key, clustered index
  EventTime   datetime          // non unique, non clustered index
  FileName    nvarchar(1000)
  SystemName  nvarchar(1000)
  other columns
)


values in time column is almost increasing

Queries:
  List all events for last 7 days, last 1 month
  List all events where order by EventTime and SystemName or FileName like something


EventTime is present in most of the queries either through order by or filter clause.

Data Variations:
  Events table can have a two or three million rows
  It can have data for past one year


Non clusterd index on EventTime column is not always used because it can't filter enough data.

What all options can I try to improve overall query performance?
For history tables such as this one, it's often appropriate to create the clustered index with a date field as the leading column, and have the primary key index nonclustered.

In this case, since you know EventTime is non-unique, you can add the AutoId column to the key to make it unique (instead of relying on a uniquifier).

This is how the clustered index would be created (after first dropping the existing primary key constraint):

CREATE UNIQUE CLUSTERED INDEX IX_Events_EventTime_AutoId
    ON [Events](EventTime, AutoId);


And then recreate the primary key:

ALTER TABLE [Events] WITH CHECK
    ADD CONSTRAINT PK_Events
        PRIMARY KEY NONCLUSTERED(AutoId);


This setup allows for efficient date-range-based queries like the examples you've provided, and still doesn't create too much fragmentation to the base table as the clustering key is generally ever-increasing.