That's a searchable argument I think... when you look at the execution plan at the bottom of the bubbles that pop up over each icon there is a section that says arguments. It first lists OBJECT indicating what index it used. Then if a SARG is present it may list SEEK: for instance where userID = @userid. Based off of the where clause, the optimizer will guess which index to use.

I ran the following three statements:

insert into tImpressionViewThroughBuilder

select userID, max(time)

from tImpressionWithID1

where userId is not null

group by userID

truncate table tImpressionViewThroughBuilder

insert into tImpressionViewThroughBuilder

select userID, max(time)

from tImpressionWithID1

group by userID

There are no null's in the userId column so the where clause has no effect on the actual data returned however, it had a big effect on the execution plans.

A clustered index seek was performed in the first block. 58% of the query runtime

A clustered index scan was performed in the second block. 83% of the query runtime

The first then used parallelism when performing the stream aggregate (4 processors on this machine) while the second didn't use parallelism.

At the end the runtime percentage was 41% vs 59% of the total time which cut the query from 3 minutes 43 seconds to 2 minutes 7 seconds... (I know the math doesn't work out, but there are some other things going on on the server)

Thanks all, but I'll be posting some more. Hopefully, my learning can help you all out aswell.

-J