sharptooth

What country is this in?

@wBob: There are clustered indexes which I haven't shown.

@RobFarley: "number of executions" is several dozen thousand - the same number for each of the two seeks which also equals the number of rows in TaskOwners. The "filter" is TOP(1) both times.

That was my bad - those are seeks, not scans, the tree clearly shows both as "Index Seek". The filter is TOP(1) for both.

@RobFarley: Yes, the index is exactly as in the question. The "predicates" are on LastOperationTime being NULL and NOT NULL and "seek predicates" are "TaskItems.OwnerId=TaskOwners.OwnerId`. Both seeks are marked "backward" in the plan tree.

@RobFarley: The closest to the scans are TOP(1).

@MarkSinkinson: Yes, the numbers are from the plan.

@Benubird Okay, then the real problem is that he doesn't want to take your presumably uncool hand-crafted query and rewrite it with a proper approach (but without ORM).

I have to point out that even though you think there's no user input with that SQL statement you cannot be sure that things don't change and you don't have some SQL injection later. I don't say you need the ORM, I just say you have to craft the query using the proper tools - for example you could use EntityFramework (example of an ORM) or you could use parameterized queries instead of concatenating strings - the latter two would both give you perfect control over emitted SQL query but concatenating strings can be prone to SQL injection. This could make both of you happy.