we have an issue with SQL Link server query in our migration Project.
There is a link Server connecting Server A with Server B (both SQL Server DB's).
As part of a migration Project, we have cloned application DBs to a new env .
Correspondingly we have set up Server A1 (in place of Server A) and Server B1 (in place of Server B).
Similar Link Server (as in the old env) is set up on Server A1 (Connecting to Server B1).
Try the same Query on both env
Select username,userid,address from B.UserDB.dbo.User where userID = @userID
Result comes up immediately on old env , while same takes more than 20 sec on new env.
While comparing query plan on both env, we could see that remote link server query on new env pulls out the entire data without applying filter and later on applies where clause locally on the result.
This is resulting in pulling lot of rows across the LInk Server resulting in query performance.
Query plan on old env is different form this .
On old env, Remote link server query pulls out filtered data from the target server.
hence we have lesser data pull across servers resulting in faster query.
Note that data and code is the same on both the env.
Any clue why there is a difference in behaviour ..?
NB: i replaced parameter with hardcode value and query came up immediately as in old env.
i could also notice a change in query plan in this case (now filter was applied remotely)