Choosing optimal indexes for a SQL Server table
I doubt anyone here can make anything but a guess - you need to record the usage of the table and see from that usage what combinations of columns are being queried for.
- Create one "master" index that would hold all these columns
That's definitely not a good idea - if you have an index on (A,B,C,D,E) and you restrict your query by values of B and D, that index is totally useless. It's only useful
- if you query by all five columns frequently
- by combinations like (A,B), (A,B,C), (A,B,C,D) frequently
In any other case, it's a waste - don't use this.
- Identify some of the most used filter combinations e.g. [A,D,E], [A, Start, End] etc. and create indexes for them
Yes, that's really the only way that promises any success. You need to see what kind of queries actually happen, and then tweak for those.
Log tables are rarely indexed, because indexing slows down INSERT, UPDATE, and DELETE statements.
I would recommend either:
- loading the records into a table (temporary or actual, indexed) before filtering
- using an indexed view
Basically - if speed/performance is a big concern, index the records in another form of table so the logging isn't impacted.
One approach is to let SQL Server tell you the optimal usage. Run a trace for a few min while the table is under "typical" usage, and then run the Database Engine Tuning Advisor