May 10, 2016 at 3:33 pm
I'm running 2 exact queries. One in production and one in dev. They both connect to the same LINKED server for data and I'm getting vastly different run time. In production, it takes approximately 60 seconds. In DEV, it runs in 2-3 seconds. (Yes Dev!)
I've attached the 2 execution plans. In the longer running query, most of the cost is in the Remote Query and I'm not sure why this is or what is affecting the change in results.
Where do I start to look into this?
May 10, 2016 at 3:39 pm
Steve T (5/10/2016)
I'm running 2 exact queries. One in production and one in dev. They both connect to the same LINKED server for data and I'm getting vastly different run time. In production, it takes approximately 60 seconds. In DEV, it runs in 2-3 seconds. (Yes Dev!)I've attached the 2 execution plans. In the longer running query, most of the cost is in the Remote Query and I'm not sure why this is or what is affecting the change in results.
Where do I start to look into this?
First, it would help to see the actually query and the query plans (not just pictures of them). Query plans may be attached as *.sqlplan files just like the pictures you posted.
May 10, 2016 at 4:10 pm
Added query plans and query text.
May 10, 2016 at 4:37 pm
You might want to try using OPENQUERY and running the query on the remote server and sending back only the data you need.
Hopefully I made all the right changes:
select * from
openquery(LINKSERVER,'
SELECT DISTINCT LEFT(mmatter, 14) matternum,
udf.udtype,
udf.udjoin,
udf.udfindex,
CASE udf.udvalue
WHEN ''N'' THEN NUll
ELSE udf.udvalue
END udvalue
FROM son_db.dbo.matter
LEFT JOIN son_db.dbo.udf
ON udf.udjoin = matter.mmatter
AND udf.udtype = ''MT''
AND udf.udfindex = 221
WHERE ( mclosedt IS NULL AND mmatter NOT IN ( ''00000--0000000'' ) and mmatter = ''000753-0000632'')');
May 10, 2016 at 4:55 pm
Didn't think about using OPENQUERY. That does help a lot. Wish I don't have to do that though. I'm would like to figure why the query runs so badly.
Additionally I did check the SP version. We're on SP1 on the PROD server and SP3 on the DEV. I have to patch it to SP3 and check again also to see if that helps.
May 10, 2016 at 6:02 pm
In the posted .sqlplan files, the reason the slower plan is slower is pretty clear.
If you look at the actual remote query being run, the faster plan is sending over a query that does the joining and filtering on the remote server.
The slower plan is just doing a SELECT * from each of the remote tables, bringing across the entire result sets, and then doing the joining and filtering locally.
The number of rows being pulled across is quite high, so I would expect that to slow down the query substantially.
It used to be that one of the most common causes for this was lack of access to statistics on the remote server for permissions reasons, but those requirements were relaxed considerably in 2012 SP1.
At any rate, the slow query's estimates are accurate, and the cost is correctly estimated to be much higher for doing the filtering/joining locally, so that's likely not the issue here.
I'd check to make sure that there aren't any collation-related differences (the "Use Remote Collation" setting of the linked server object on both servers, especially), as that can prevent the filters from being pushed to the remote server.
Having said all that, the two plans posted as .sqlplan files are not the same as the two plans you posted images of, both of which seemed to be doing some filtering locally.
It could still be the case there that it's just a matter of one of them doing less filtering locally than the other, but from the images we can't really tell.
Cheers!
May 10, 2016 at 6:54 pm
I'd check to make sure that there aren't any collation-related differences (the "Use Remote Collation" setting of the linked server object on both servers, especially), as that can prevent the filters from being pushed to the remote server.
Ah, I thought I double checked this, but I guess not. This was different and has helped the query. It's down to about 8 seconds now. It's still slightly slower as it's still spending most of the query on the remote query still.
Having said all that, the two plans posted as .sqlplan files are not the same as the two plans you posted images of, both of which seemed to be doing some filtering locally.
Sorry I must have messed up the query after trying to sanitize it a bit.
You solution to check the collation made a huge difference. Thanks!
Thanks everybody!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply