I have an advanced SQL question for your SQL perf gurus out there :-)

I'm currently trying to understand some behavior in a larger application, but it boils down to a query against these two tables:

* `Users` table - roughly 750 entries, `UserId` (`varchar(50)`) as clustered PK
* `ActionLog` table - millions of entries, includes `UserId` - but no FK relationship

For a grid in my ASP.NET application, I'm trying to get all users plus their last log entry's date.

The SQL statement that is currently being used looks something like this:

```
SELECT
     UserId, (other columns),
     LastLogDate = (SELECT TOP (1) [Timestamp] FROM dbo.ActionLog a WHERE a.UserId = u.UserId ORDER BY [Timestamp] DESC) 
FROM
     dbo.Users u;

```

and it returns the rows to display - but it's fairly slow (approx. 20 seconds).

My first thought was to add an index on the `ActionLog` table on `UserId` and to include the `Timestamp` column in it:

```
CREATE NONCLUSTERED INDEX [IDX_UserId]
ON [dbo].[ActionLog]([UserId] ASC)
INCLUDE ([Timestamp])

```

The rows are now returned very quickly - under 2 seconds, with 350'000 entries in the `ActionLog` table, and my index is being used just fine, as the execution plan shows me. All seems fine.

Now, for approximating the production scenario, we loaded roughly 2 million rows into the `ActionLog` table, 95% or more of which refer to a non-existing user (i.e. these rows have a `UserId` that doesn't exist in the `Users` table).

Now suddenly, the query becomes **extremely slow** (24 minutes!), and the index isn't being used anymore.

I assumed that since the vast majority of the entries in the `ActionLog` table don't line up with an existing user, I would see performance gains if I use a **filtered** index - to "weed out" all those messy entries without a corresponding user - so I created this index (replacing the other one that existed before):

```
CREATE NONCLUSTERED INDEX [IDX_UserId]
ON [dbo].[Log]([UserId] ASC)
INCLUDE ([Timestamp])
WHERE UserId <> 'user'    -- that's the fixed, non-existing "UserId" I wanted to avoid

```

But to my dismay - the query is still about the same - takes over 20 minutes to complete. I updated the statistics - no change - still extremely slow.

Funny thing (to me) is: when I dropped the index and re-created it -> now the query was really fast again (again less than 3 seconds). WOW!

But as soon as I start adding more entries again, the query "tilts" and becomes really really slow.......

I don't fully understand why this is happening - I was thinking that with a filtered index that eliminates all those "rogue" entries, I would be seeing good performance on trying to find the newest `ActionLog` entry for existing users - but that doesn't seem to be the case. 

**WHY NOT?** 

Any ideas? Thoughts? Things to try??