Discussion on question by sharptooth: Why does my query end up with two seeks instead of one and how do I fix that?

Feeds

10:06

Q: Why does my query end up with two seeks instead of one and how do I fix that?

I have these two tables: CREATE TABLE [TaskItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL UNIQUE PRIMARY KEY, [LastOperationTime] DATETIME NULL DEFAULT GETUTCDATE(), [OwnerId] UNIQUEIDENTIFIER NOT NULL -- more columns which are irrelevant ) CREATE INDEX [Tas...

Mark Sinkinson

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

sharptooth

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

Rob Farley

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.

sharptooth

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

Rob Farley

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.

sharptooth

10:06

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

Rob Farley

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?

sharptooth

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

Rob Farley

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?

sharptooth

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

Rob Farley

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?

Martin Smith

10:06

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

wBob

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.

sharptooth

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