I use a MSSQL Database for my web application and now the user audit(
USER_AUDIT) records in the application have crossed beyond 5 million records/rows. Every day the performance of the application is degrading and the queries for the audit table are pretty straight forward from the UI.
The default sorting on the data is on the
TimeStamp i.e. the time when the record was created so I was wondering if adding an
INDEX on the
TimeStamp column would improve the performance of the application or because the the
ID column(Incremental IDs) already has the
INDEX on it, its sufficient and would not improve the performance any further.
Also sometimes the records need to be filtered using the
username which is also one of the columns in the same table but because the user names are not unique I was wondering if it would help at all to add
INDEX to that column as well.
The clustered index is on the
ID column on the table - Would it be helpful if I change the
Clustered INDEX to be on a different column
Based on the above information would it be of any help to add
Non-clustered Indexes to either
Username or both columns
I come from a programming background with very limited knowledge of the Database management so please pardon any missing information and please feel free to let me know if I need to add more information to the Question.
I would really appreciate it even if you could point me in the right direction.