April 17, 2015 at 3:58 am
We recently upgraded our servers to SQL Server 2014 from 2008 R2.
There are a few processes which use linked servers to pull data across & the performance for most of these processes seems to be on par or slightly better than it was on the previous version.
But there are others where the performance is horrible. On 2008, a specific linked server query took less than a minute to complete. Since moving over to 2014, it's taking longer than an hour.
What we've done so far:
Update stats with fullscan
Recompiled objects
Rebuilt indexes
Recreated link servers
If you execute the query locally on the source server, it returns within seconds, so it's definitely something to do with the linked server setup.
Anyone encountered something similar?
April 17, 2015 at 4:17 am
It may not be relevant here, but ...
Presumably you are aware of all of this[/url] stuff regarding the new cardinality estimator.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 17, 2015 at 4:23 am
Oh yes! 🙂
The recompile of the objects sorted that out.
April 22, 2015 at 5:03 am
For those interested, we've managed to solve our problem. 🙂
To explain the scenario in detail...
Server A pulls data from Server B via linked server.
Server A upgraded to SQL 2014. Compatibility levels of all user databases on 120.
Server B upgraded to SQL 2014. Compatibility levels of user databases a mix between 100 & 120.
All linked server queries to databases with 120 are performing nicely. Queries to 100 databases are poor, even though it's the same query for all databases.
Initially, the default database for the login used in the linked server was set to master, which of is on 120. All our problems immediately went away when we changed the default database to another user database on a 100 compatibility level.
If the default database is compatibility level 100 & I view a query plan, it uses the old CE & query performs exactly the same on a 100 vs 120 database.
If the default database is compatibility level 120 & I view a query plan, it uses the new CE & query performs badly on a 100 database.
If there are any SQL boffins out there who can explain or makes sense of this, please, feel free to comment.
May 5, 2015 at 8:35 am
Now that is VERY bizzare... very.
May 11, 2015 at 6:25 am
Thanks for taking the time to post that. That's going to help me a lot in a couple of days. Really appreciate it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply