Why is there a table scan on the ActivityLog (81.3% cost)

Frank DeWysockie 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
);
Dan Holmes 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.

SQLkiwi 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.