I have Log and LogItem tables; I'm writing a query to grab some data from both. There are thousands of
Logs and each
Log can have up to 125
The query in question is complicated so I'm skipping it (if someone thinks it's important I can probably post it), but when I ran SSMS Estimated Query plan, it told me a new Non-Clustered index would improve performance up to 100%.
Existing Index: Non-clustered Key Colums (LogItem): ParentLogID, DateModified, Name, DatabaseModified Query Plan Recommendation CREATE NONCLUSTERED INDEX [LogReportIndex] ON [dbo].[LogItem] ([ParentLogID],[DatabaseModified])
Just for fun, I created this new index and ran the query and much to my surprise, it now takes ~1 second for my query to run, when before it was 10+ seconds.
I assumed that my existing index would cover this new query, so my question is why did creating a new index on the only columns used in my new query improve performance? Should I have an index for each unique combination of columns used in my
note: I don't think this is because the SQL Server is caching my results, I ran the query about 25-30 times before I created the index and it consistantly took 10-15 seconds, after the index it is now consistantly ~1 or less.