Rob Farley

Ah, I see from the image you’re partitioning on End. So use the extra predicate for partition elimination, if you can be sure that the rows you’re after are all from the last day (or two days or whatever).

The main thing in my answer is having a separate table of Sources, to leverage the proper index. But if you have Source in there too you could even use your current clustered index. My current question is about the partitioning.

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

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?

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

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

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?

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?

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.

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.

No worries. Good luck. It's getting late here in Aus, so I'm not going to be around much longer. I'll check in later though.

I thought you didn't have a database. So what did you do to get something you could backup?

Then bring it online again. Ignore the errors. Then see if you can do a log backup.

Create a new database with the same name... Make sure it's in the full recovery model. Back it up so it knows it's in really in full. Then take it offline. Now rename the actual files, and swap in a copy of your file.

Ok. So how were you trying to back it up? There may be hope after all.

That is: was your old database definitely using simple?

Ok. How were you backing it up then?

Aha. Okay. Next question for you: Why did you say that your LDF contained all the transactions since the full backup?

Assuming you have a corrupt page, what kind of page is corrupt? If you don't know, use DBCC PAGE to see.

Wait though. Do you still have your database in some (albeit corrupt) form?

If you're in SIMPLE, the LDF is no use to you, because it would have been truncated every few moments.

Ok. In SIMPLE the only thing you can go back to is the last database backup. The log is no use. Let's talk about what you do have... For starters, what makes you say your database is corrupt?

Yes. Not always better. But also, not always worse.

I agree with Thomas on these points. Heaps and GUID PKs are over-villainised, and I certainly don't rate them as bad as many in the SQL community do.

....which was the reasoning behind my criticism of the suggesting to create a clustered index (which was what your initial answer described).

@srutzky When we look at the question around "my query is doing a table scan", adding a clustered index on SnapshotKey wouldn't make a difference. The significant thing is having a non-clustered index which includes the full list of columns required by the query. I agree that the non-clustered index could be a little smaller with a clustered index, but the clustered index itself doesn't improve the query plan.

I'm not sure the clustered index would help much. It's a data warehouse, therefore insert-only (hopefully). This is a scenario where heaps work just fine, and RID Lookups are much quicker than Key Lookups, so I'd be quite wary of creating a clustered index.

@PaulWhite Not about the points at all, I'm sure. Happy Birthday @AaronBertrand

Correct - I'm in Australia. My day will start later.

I'm just waking up, myself...