Mystery Jimbo,

I am also seeing issues that may or may not be related to this type of issue, we are still flushing out other potential issues, but can you...

Wow...I might just have to use this as Evidence Item #1 when convincing Dev to change the code....

Thanks everyone for your contributions, when we have a fix, I will definitely...

Yeah, I agree,

The unionall plan is attached...it looks MUCH better....2 seeks.

Current query with SET STATISTICS IO ON:

Table 'BH_UserWorkHistory'. Scan count 14, logical reads 5733, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead...

Thanks guys, I agree the biggest mystery to me is why it is worse on more (granted slower) CPUs. This code has not changed and I cannot unfortunate see the...

...Sorry if I wasn't clear, if we can change the code, we would not even need to do it as a...

Ninja,

Thanks and I love it. The new plan is attached.

Ok, I changed that and it used the new index, but it is still a scan....

I need to go fight with development on changing the code for a little bit....

Ninja,

On the second table, I have a covering index, but I can't seem to get it to not do an index scan, the definition is below, am I missing...

Ninja,

The business use of the query is to keep two systems in sync. So the query runs (all the time) to check for the existence of rows.

The...

Thanks guys, sorry for the need to obfuscate. This one just has the schema and DBname changed.

the index definitions are also included.

Thanks Ninja.

attached as a txt. (small changes made to DB, schema and tables names for anonymity.) If that doesn't work, I can send directly.

So, forgive my ignorance, but that query returns 750 rows. The overwhelming vast majority(>90%) are the same query that also shows as being the highest CPU offender. It is shown...

Ok, so I have been monitoring this for about 90 min and am still seeing the same issues. Performance has not moved, still about 200% higher than on the previous...

Thanks Ninja,

I'll try to keep and eye on the tables that have been causing the most problems.