'takes 48% of the time' - Is that from the Execution Plan? If so, those percentages are estimates, even on the Actual Plan.

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

What's in those Filter operators? If they're Startup Expression Predicates, then it's only pulling data from one of the Scans each time.

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

I can't help thinking you're misdescribing something. Your Scans are only being asked for a single row (the largest, presumably - I expect the Scan is "BACKWARD" if you look in the properties window). This would make sense if it were on LastOperationTime followed by OwnerId rather than the other way round. It'll scan through the index looking for the first row which matches the Owner (is your Predicate really on the Time column?), knowing it'll be the largest value for that owner.

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

You said "So I run the query and request the actual execution plan and it has two index scans." But now you say they're Seeks?

This Filter - is it a Top operator, or a Filter operator? If it's a Filter, is there a Startup Expression Predicate property? Also, what is the "Number of Executions" on each of the Seeks?

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

What's the "Number of Executions" on each?

When you say it's "filtered", is this a Top operator, or an actual Filter operator? Or are there both?

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

So it's not a Filter. It's simply requesting a single row from the Seek. Do you get the same behaviour if you add "AND LastOperationTime IS NOT NULL" to the ON clause of your query?

Can you upload the plan or at least provide SQL that runs and reproduces the issue? Current SQL is invalid/

Hmm you've tagged it as sql-azure but sql azure tables need clustered indexes. Do you just mean an Azure VM? If so, what version? The execution plan would be really useful.

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

I have never seen the transformation that you are describing. Can you upload a plan?

The Seek Predicates - do they have a Prefix on OwnerId and then a Start and End too?