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
Or
Based on the above information would it be of any help to add Non-clustered Indexes
to either TimeStamp
or 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.