3,904 reputation
1716
bio website lobsterpot.com.au
location Adelaide, Australia
age 40
visits member for 3 years
seen 2 days ago

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)


Nov
17
comment When using underscores, should they be reserved to junction tables?
I was simply offering an alternative based on his description, considering he wasn't going for allcaps, and was keen to use an underscore in that way...
Nov
15
comment When using underscores, should they be reserved to junction tables?
Otherwise, use something else instead of an underscore for junction tables.
Nov
15
answered does the mere existence of a primary key increase query speed for large data sets?
Nov
15
answered When using underscores, should they be reserved to junction tables?
Oct
26
awarded  Yearling
Oct
19
answered Updating a big replicated Dimension (SQL Server PDW)
Sep
30
awarded  Explainer
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.