Why is there a table scan on the ActivityLog (81.3% cost)
2016-03-17 21:34:05
I expect SQL Server to use the following index…
CREATE NONCLUSTERED INDEX [idx_Date_CompanyID] ON [dbo].[ActivityLog] ( [Date] ASC, [CompanyID] ASC ) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON );
2016-03-18 17:40:50
The reason for the scan is because that index isn't covering the columns required from that table and SQL decided that a table scan was cheaper than a lookup to get what it needed.
INCLUDE ( IPAddress, Page, ActivityString, activitycode, BranchID, TemplateID ... )
I got most of way through the predicate and realized that there are a lot of columns used from ActivityLog. I don't know how wide the table is but the index might become a copy of table. You will have to try this and see how it works in your situation.
Given that it is named ActivityLog it sounds like something that is INSERTed/UPDATEd on every DML. A wide index might or might not be good for all of those operations.
2016-03-23 11:38:39
Why does Activity Log not have a clustered index? Most tables should.
A clustered index on (CompanyID, Date) seems like a good option. Make it unique if that would be correct. Every table needs a key.
There query has many other problems, but that addresses the question you asked.