thanks tried and it still wants to do the key lookup, driving me nuts, this looks like a layup but the engine wants to do this no matter which strategy...

no the last iteration of 2nd query posting runs fine, almost as exactly fast as one vendor currently uses.  Issue is with trying to get the engine to use my...

Thanks that works my brain freeze too long looking at something.  Problem is did not help, the start of this is underlying issue with sql wanting to do a key...

first query returns 11 rows thousands in the second.  As I stated 3rd party app have to deal with their SQL, they also have schema names in all their sql...

I get the bad code on first attempt it is from vendor part of living with 3rd party applications

Went through Kendra little's index training video again and ran sp_blitzindex against the table (oh yeah no clustered index on their tables that is a whole other discussion). When...

sorry on standard edition. Tried wider index which it used, took 3 of the 7 steps and got rid of RID, not for the other 4. Going to...

thanks that was kind of my first thought, also a little worried about the inserts and updates generating lots of IO if I have 7-8 indexes

just getting started with project plan, trying to get ducks in order

no chance in changing vendor, these are DML triggers and I do expect them to be rather large

I would wonder about a come back to earth for big data. After the election there should be a reality check that with big data comes big responsibility. ...

the joy of 3rd party apps

yes scan was quicker I guess the engine is smarter than the average bear

Appreciate all the input, it did get rid of the scan, however below are the stats, top set is with index scan below is the seek.

(71 row(s) affected)

Table 'Worktable'. Scan...

same plan and results