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?