I'm with Jeff M, probably a security setup issue, like remote user mapping issue.

If using Win Auth, it could be an SPN issue.

Or possibly not calling it correctly in the...

• This reply was modified 2 years, 2 months ago by  rothj.
• This reply was modified 2 years, 2 months ago by  rothj.
• This reply was modified 2 years, 2 months ago by  rothj.
• This reply was modified 2 years, 2 months ago by  rothj.
• This reply was modified 2 years, 2 months ago by  rothj.

Since nobody has suggested anything...look into the SPN's if it's a domain user.

Double hop scenario for Windows accounts will show up as anonymous (Kerberos not forwarded).

You might check the schema change history report (data from default trace).

On the database, right click, choose Reports > Standard Reports > Schema Changes History from context menu.

I agree with Johan's approach.  Use Data Migration Assistant to check for deprecated code.

Go with a new server with all the latest. You build it with a different name like...

Thanks for everyone's input.  I presented my findings to the SR DEV, he also feels the Bank subquery is redundant.  He also doesn't believe we need DISTINCT and NOLOCK.  He...

No missing index suggestion. It didn't use the your recommended one.

But when Jonathon added both columns the WHERE clause, it might have taken care of it.

c.CustomerNumber = t.CustomerNumber

AND c.Customer = t.Customer

Jonathon, your second query brought the spool back.  But I...

I'm...

What is the difference in using 'EXISTS' instead of 'IN'?

Yes!   Thank You very much!

Agreed, the NOLOCK was already on my radar.

Thanks guys.  I also thought the Bank filter may be unnecessary.  I tried removing it and got one more record than with it, I'll look into that.  It ran extremely...

Edit: Wrong Answer

• This reply was modified 3 years, 1 month ago by  rothj.

Are your Statistics up to date?
I see it estimated 1.5 m rows but the actual was 4.2 m rows.
Then the sort spilled to TempDB.