232 reputation
28
bio website
location
age
visits member for 2 years, 6 months
seen Sep 9 at 6:32

Sep
19
awarded  Notable Question
Aug
29
comment Why does my query end up with two seeks instead of one and how do I fix that?
@AaronBertrand: Okay, I used code from here social.msdn.microsoft.com/Forums/en-US/… with two getutcdate() calls. The initial query takes about 2200 milliseconds while the modified one takes 4300 to 6500 milliseconds.
Aug
29
comment Why does my query end up with two seeks instead of one and how do I fix that?
@RobFarley: I added the picture with the plan and included the details from earlier comments into the question.
Aug
29
comment Why does my query end up with two seeks instead of one and how do I fix that?
@AaronBertrand: I cannot know, but it's reproducible and it's much slower compared to SELECT 1.
Aug
29
revised Why does my query end up with two seeks instead of one and how do I fix that?
added 518 characters in body
Aug
28
comment Why does my query end up with two seeks instead of one and how do I fix that?
@RobFarley: Nope, that's a web interface that contains a fraction of SSMS functionality.
Aug
28
comment Why does my query end up with two seeks instead of one and how do I fix that?
@RobFarley: Yes, something like a million records per Owner is not uncommon for specific owners (only a small fraction of all). I doubt I can export anything better than a tree picture from Management Portal.
Aug
28
comment Why does my query end up with two seeks instead of one and how do I fix that?
@RobFarley: The delay between I press "run" and see the results in the Management Portal is longer compared to the original query.
Aug
28
comment Why does my query end up with two seeks instead of one and how do I fix that?
I tried this - it yields an index scan for TaskItems which is slower.
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
@wBob: There are clustered indexes which I haven't shown.
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
@MartinSmithL I presumably fixed the query so that it's valid now.
Aug
27
revised Why does my query end up with two seeks instead of one and how do I fix that?
deleted 2 characters in body
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
@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.
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
That was my bad - those are seeks, not scans, the tree clearly shows both as "Index Seek". The filter is TOP(1) for both.
Aug
27
revised Why does my query end up with two seeks instead of one and how do I fix that?
added 115 characters in body
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
@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.
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
@RobFarley: The closest to the scans are TOP(1).
Aug
27
revised Why does my query end up with two seeks instead of one and how do I fix that?
added 67 characters in body
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
@MarkSinkinson: Yes, the numbers are from the plan.
Aug
27
asked Why does my query end up with two seeks instead of one and how do I fix that?