Jason-299789 (8/7/2012)
Thank you to both of the replies so far its confirmed a few things, and given me a few things to ponder.The PK is Non-clustered on the #Lookup table, and looking at the table there is no clustered Index at all, unfortunately id assumed the PK (or secondary index) was clustered, which probably explains the table scan. Id like to see what happens if you make the PK clustered.
I did some 'off-record' testing by dropping the SysId from the joins inthe actual query, and over a couple of runs there was an approx 5% performance improvement.
Also in the actual query the #Lookup table is referenced around 8 times. At the moment the query returns around 25% of the data in a little over 13-14 minutes.
Assuming the optimizer thinks there is 25% of the table's rows coming back and that estimate is accurate you will almost certainly get scans in your case since you have no WHERE clause. The only thing that will help this is a) better hardware (especially RAM and IO) and b) not trying to return too much data to SSMS if that is what you are doing with your SELECT.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service