3,856 reputation
1615
bio website lobsterpot.com.au
location Adelaide, Australia
age 39
visits member for 2 years, 11 months
seen yesterday

Owner / Principal at LobsterPot Solutions

Adelaide (Australia) based SQL MVP

On twitter @rob_farley

Blog at http://sqlblog.com/blogs/rob_farley
(Old blog at http://msmvps.com/blogs/robfarley)


Aug
28
comment Why does my query end up with two seeks instead of one and how do I fix that?
What is this "Management Portal"? If it's SSMS, you can right-click and Save.
Aug
28
comment Why does my query end up with two seeks instead of one and how do I fix that?
It sounds like the statistics on that table are old, if it's choosing to do a scan which is slower. It sounds like there are a decent number of records per Owner. Still, the system figures it worth grabbing the top NOT NULL record and a placeholder NULL record. But I agree that it shouldn't have to care about the NULL record. There's something else that's going on, and if you can post the actual sqlplan file, I'm sure we could help further.
Aug
28
comment Why does my query end up with two seeks instead of one and how do I fix that?
What makes you say it's slower? It's probably scanning once, rather than seeking for every individual owner.
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
The Seek Predicates - do they have a Prefix on OwnerId and then a Start and End too?
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
Using the time field in an expression that will be nulled out by a null (either being returned that way or by the LJ) won't be helping either. I think SQL is trying to differentiate between the two types of null, but can't be sure.
Aug
27
answered Why does my query end up with two seeks instead of one and how do I fix that?
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
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?
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
When you say it's "filtered", is this a Top operator, or an actual Filter operator? Or are there both?
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
What's the "Number of Executions" on each?
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
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?
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
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?
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
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.
Aug
27
comment Why does my query end up with two seeks instead of one and how do I fix that?
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.
Aug
27
answered Using expressions in the SELECT list to thin down a CASE statement
Aug
7
answered Filtered index not chosen, and rejected with hint
Jul
25
comment View with left joins are very slow
Start by getting rid of the word "distinct". And consider looking at the Execution Plan to get a feel for how the query is running. You may like NCIXs.
Jul
14
answered Update row where associated row does not exist
Jul
2
answered Using the “Select Top (100) percent vs. Select * , or selecting any column”
Jul
1
answered Table income and expense is better to have it together or apart
Jun
30
comment Performance tuning a Great Circle distance calculation
...there's no need to do the IN bit at all. It really won't help at all.