The optimizer is choosing to scan the lookup table because it thinks that is the fastest way for it to deliver the data. Consider that for each row in #Partitioned, there are 3 LEFT JOINs, meaning it would have to do thirty million index seek operations against the #Lookup table. I suspect it opted instead for a hash join. The execution plan would confirm this.

The SysID in the primary key of the #lookup table isn't necessarily pointless, but it really doesn't help this query, because the SysIDs are all spread out due to the fact that ColID is the primary column in the key. The index you described would only be truly helpful for queries that requested ColID and SysID only, because those queries could be answered without going to the leaf level of the index.

As for the index hint, try it and see what happens. 😉 Be sure to turn STATISTICS IO ON and get a baseline of the number of logical reads before you use the index hint.

Hope this helps.