My table structure is as follows

  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

  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?

  • Since AutoId and EventTime both columns have auto increasing values can indexe on EventTime share the data pages of cluster index on AutoId. This will avoid the need of lookup in cluster index – manish Sep 10 '13 at 6:46

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):

    ON [Events](EventTime, AutoId);

And then recreate the primary key:


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.

  • I was also thinking on exactly same lines. But It will increase the size of clustering key, size of non clustered indexes and also Clustered index seek time. – manish Sep 11 '13 at 5:44
  • For me ideal solution will be somehow force SQL server to share the data pages between cluster index created on AutoId column and non clustered index created on eventTime column – manish Sep 11 '13 at 5:48
  • @manish: If you're always doing date-range-based queries, you won't need any nonclustered indexes. The increase in key size will be largely irrelevant compared to the data in the table. If you're concerned about seeking on the primary key (why?), yes, it will be slightly slower after this change, but is that really the most expensive part of the workload? For maximum performance, you could create indexes that INCLUDE every column in the table, but then you pay the storage penalty. It's always a balance. Your "ideal" doesn't exist in SQL Server. – Jon Seigel Sep 11 '13 at 15:21
  • Thanks again for helping. Any idea on why my "ideal" solution of sharing data pages between cluster and non cluster index is not there? Is any other DBMS provide this? – manish Sep 11 '13 at 15:58
  • In my application Events table can have a few million rows, But most of the time I need to display around 20 to 25 Events(due to paginated UI) based on certain filters(event time will be there almost all the time, other possible filters are FileName, SystemName etc). So I am thinking of creating of creating non clustered index on EventTime with FileName, SystemName(possible filter candidates) etc as included columns. This index will narrow down the result set to around 25. Then to get the complete row data we will have 25 cluster index seek. – manish Sep 11 '13 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.